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).
