Gaja,
You're correct. I should have quantified what I meant by significant. As
well as given more detail on what I was doing. That said, here is what I
remember of what I was doing....
Specifically, At the request of management, I was testing the performance
and extent allocation of locally managed tablespaces v.s. dictionary managed
tablespaces. I was to give a summary of my results and a recommendation as
to how new tablespaces were to be created.
That said, I create 2 tablespaces. One dictionary managed and one locally
managed (uniform extent size) on the same instance, same logical volume on
the disk array and same extent sizes (1mb)
The same table was created in both tablespaces, using the default storage
clauses.
I used SQL Loader to load the same data into both tables tablespaces
multiple times. The source file was about 1mb.
I was mostly testing non-direct path insert performance (via sqlldr) and
select performance via several scripts (using sqlplus).
What I found was that the performance of sqlldr stayed remarkably steady for
the dictionary-managed tablespace well past 2000 extents. The sqlldr
run-times increased by about 5-10% for the locally-managed after about 1024
extents had been reached.
The performance of the select statements degraded in a linear fashion, based
on the number of rows. The exception was that the LMT table saw a 5-10%
degradation in performance after about 1024 extents were reached.
No updates or deletes were performed on the tables. Also, there were no
indexes or constraints on the tables. Nor did I generate statistics.
And this was repeatable as I dropped and recreated the tablespaces several
times.
The methodology was as follows, recording the timing at each step
1) Load the file one time into each of the tables
2) note the number of extents
3) perform the selects
-- count(*)
-- select * from xxx where id = 1;
-- a select with a group by.
Kevin
-----Original Message-----
Krishna Vaidyanatha
Sent: Friday, March 21, 2003 1:15 PM
To: Multiple recipients of list ORACLE-L
Hi Kevin,
Long time no talk or see. Hope things are well with
you. Going forward it will nice for us to "quantify"
any performance differences that we observe,
preferably with data supporting the claim. Don't get
me wrong, I am not trying to beat you up on this, but
trying to bring some clarity to the situation. Your
original posting very strongly suggested that 1024 was
some kind of magical number beyond which SQL
performance took a "significant" dive. We need to find
out (at this stage hypothesise), what caused the
performance decrease.
If we were to look at this more objectively, it is
very unlikely that full-table scans would perform
worse with more extents, assuming that everything else
remains constant. This is because, Oracle would have
issued the same number of "read system calls", to
process the data below the high-water mark,regardless
of the number of extents. This ofcourse assumes that
the value of db_file_multiblock_read_count did not
change over time.
Index scans are also rarely affected by the number of
extents, because an index-scan will still require
reading of the same root, branch and leaf nodes to
determine the ROWIDs for the search (regardless of the
number of extents in the table).
One very plausible and probable cause for the
performance decrease, could be the onset of
"block-level fragmentation" that happens over time.
This can be measured by calculating the "data density"
(defined as rows/block below the high-water mark) of
the blocks in the segment over time. Data density
issues are usually caused by un-optimal settings of
PCTUSED and PCTFREE. Again, if PCTUSED is causing the
data density issue, then even that can be overcome by
using Automatic Segment Space Management in 9i.
So, if the high-water mark of the table has overtime
inflated to 100000 blocks (due to frequent INSERT &
DELETE operations), and the current data density is 10
rows per block, and if we assume that each block in
reality can actually hold 100 rows, then when the
table is re-organized, the number of blocks below the
high-water mark will drop to 10000 blocks.
This most certainly will cause SELECTs that are
performing full-table scans to perform faster, as
Oracle will issue "fewer" read system calls to read
the data. In all cases here, I am assuming here that
the data is NOT in the database buffer cache. The same
issue of "data density" can be suggested for indexes,
as fewer and more dense leaf blocks, will result in
less logical/physical I/O on the index blocks itself.
So, the increase in the number of extents in a segment
by itself does not cause the performance problem. It
"may be" a symptom or a signal of something else
occuring on segment, obviously caused by your
application. I am not in anyway suggesting that
everyone should start having 10000+ extents for all of
their objects, but on the flip side I also don't want
to see people inflicting pain on themselves, but
re-organizing their objects whenever it grows beyond
"a small number of extents". I will leave the
definition of "small" to the reader.
Best regards,
Gaja
--- Kevin Toepke <[EMAIL PROTECTED]> wrote:
> Performance of selects and non-direct path inserts.
> I no longer have the
> exact data as it was left at my former employer when
> I left. Significantly
> is relative -- it wasn't like performance went to
> the pots, I'm talking
> between about 5% and 10%. So a 1 minute query would
> run a couple seconds
> longer than expected.
>
> And why? I don't know. That was one of the things I
> was going to look into.
> If only I had metalink access I could do some
> searching. Maybe I should try
> to recreate it in my current environment (Win2k,
> Oracle 9iR2 patchset 3)
>
> Kevin
>
> -----Original Message-----
> Sent: Thursday, March 20, 2003 6:44 PM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Importance: High
>
>
> Kevin,
>
> Performance of what? SELECT, INSERT, DELETE,
> UPDATE?
>
> And why?
>
> Jared
>
> "Kevin Toepke" <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 03/19/2003 03:43 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc:
> Subject: RE: Storage guidelines in
> 9iR1 ??
>
>
> Funny this came up. I had just finished doing
> research on this for a
> potential 9iRAC implemenation.
>
> What I came up with from reading the docs and from
> experimentation is to
> keep the number of extents of each table and each
> indexes to less than
> 1024.
> 1024 seemed to be a majic number. Performance
> dropped significanty once
> you
> got past that.
>
> Keep the uniform size an exact multiple of the
> tablespace's (or
> database's)
> block size.
>
> Finaly, when you create a LMT, figure out the size
> you want and add 64k
> for
> the bitmap.
>
> My envionment was Sun Solaris 8, 9iR2; patchset 1.
>
> HTH
> Kevin
>
> -----Original Message-----
> Sent: Wednesday, March 19, 2003 5:34 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Thanks Mogens.
> but any "thumb of rule" to fix the UNIFORM SIZE in
> LMTs.
> how do u go about fixing the size ?!
>
> Regards,
> Jp.
>
> 2003/03/19 18:15:52, Mogens N?rgaard
> <[EMAIL PROTECTED]> wrote:
>
> >Go to LMT asap. Whether you want to use uniform
> sized extents in LMTs or
> >system managed (or whatever it's called) is a
> matter of dispute. My
> >personal opinion is for uniformed sizing.
> >
> >Mogens
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Prem Khanna J
> INET: [EMAIL PROTECTED]
>
=====
__________________________________________________
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gaja Krishna Vaidyanatha
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kevin Toepke
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
