Cary, On the same line, I want to propose a different thought - smaller block sizes for index tablespaces to reduce the chance that a single block is contended for by two different sessions, which indices the wait event "buffer busy waits". Making them smaller, a typical index block will hold less number of leafs and therefore will have less chance of experiencing this wait.
Will appreciate your thoughts on this. Arup ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, July 15, 2003 6:39 PM > Tracy, > > I would have expected you to say the opposite: big blocks for index segments > (to reduce B*-tree height), and small blocks for table data (to improve > block selectivity). > > It's a pretty expensive thing to implement though (assuming you're already > "up," the downtime to rebuild a tablespace could be costly). And most sites > have a lot of much less costly things they should be doing to create vastly > bigger impact (like getting rid of unnecessary LIO calls and parse calls). > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Hotsos Clinic 101 in Washington, Denver, Sydney > - Hotsos Symposium 2004, March 7-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -----Original Message----- > Tracy Rahmlow > Sent: Tuesday, July 15, 2003 5:14 PM > To: Multiple recipients of list ORACLE-L > > > Based on Cary's paper regarding when to use an index, would there not be > value > in having index tablespaces with a smaller block size vs tables using a > larger > block size? > > > > > > > > AM PST > > Please respond to [EMAIL PROTECTED] > > Sent by: [EMAIL PROTECTED] > > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > cc: > > > The thing that occurred to me a few years ago (as a result of a test > designed by Craig Shallahamer) is that "what disks do" gets very, very > complicated when you add users. On any system busy enough to have a > performance problem, the odds are usually slim that a disk is just "sitting > there" waiting for your next I/O call. On a busy system, someone else's I/O > call is almost always going to intercede between two of *your* I/O calls. > > As has been said many times, many ways... > > - DO separate tables and indexes into different tablespaces. There are lots > of reasons you should do this. > > - DON'T necessarily feel that you have to put the index and data tablespaces > on different devices. One decision criterion is performance: don't ever put > two files on the same device if the sum of their I/O-per-second rates > exceeds the I/O-per-second capacity of the device. Another decision > criterion is availability: don't ever put more data on a device than you can > recover in your acceptable downtime window. The list goes on... > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Hotsos Clinic 101 in Washington, Denver, Sydney > - Hotsos Symposium 2004, March 7-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -----Original Message----- > Daniel Fink > Sent: Tuesday, July 15, 2003 11:24 AM > To: Multiple recipients of list ORACLE-L > > I may be way off base here, so any gurus please correct me with a gentle > slap to the back of the head... > > Index and table access is not as simple as index entry..table row..index > entry..table row..etc. I just ran a quick test (which may not be > represntative and is using the primary key which can be understood as the > row number in physical order of the data blocks) and I found (using the > sequence of wait events) that there was substantial access to the index > datafiles initially, followed by substantial access to the data datafiles. > Then another single access to index, multiple access to data, single > access to index, multiple access to data. It seems to me that this pattern > is read several index blocks, then access several data blocks, read several > index blocks, access several data blocks. This may be due to the sequential > nature of the pk in the data blocks. It seems that the most efficient > algorithm is to read enough index blocks to set up a list of data blocks to > read, then go get them. Since you have the index block pinned, don't waste > any resource in releasing the pin to pin the data > blocks, then repin the index block. > > The other issue is that indexes can be accessed using multiblock reads > (index fast full scan) and tables can be indexed using single block reads > (table access by rowid). > > > Garry Gillies wrote: > > > > It's hot here. I wish I was at the beach and I feel like a rant. > > > > "oracle actually accesses indexes and tables serially" > > > > Is it just me or is this blindingly obvious? > > You cannot access the table data until you have completed accessing the > > index data > > because the index data contains the location of the table data. > > > > During an indexed query on a single table the index will be accessed, then > > the table, > > then the index,then the table, then the index,then the table then the > > index,then the table. > > If the index and the table are on the same disk then a lot of time will be > > taken up by > > head seek movement. > > If they are on the different disks then the "index" heads can locate their > > data and stay > > there - and the "data" heads can locate their data and stay there. > > Less head movement, less wasted time. > > > > That is the argument for what it is worth. Real life is of course vastly > > more complex than > > this and we are swimming in very muddy waters, which is why there is so > > much > > argument on the subject (raid salesmen - spit). > > > > Thanks for the vent > > > > Garry Gillies > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Cary Millsap > 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). > > > > > > > American Express made the following > annotations on 07/15/2003 02:15:07 PM > -------------------------------------------------------------------------- -- > -- > **************************************************************************** > ** > > "This message and any attachments are solely for the intended recipient > and may contain confidential or privileged information. If you are not the > intended recipient, any disclosure, copying, use, or distribution of the > information included in this message and any attachments is prohibited. If > you have received this communication in error, please notify us by reply > e-mail and immediately and permanently delete this message and any > attachments. Thank you." > > **************************************************************************** > ** > > > ============================================================================ > == > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Tracy Rahmlow > 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: Cary Millsap > 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: Arup Nanda 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).
