RE: Cary/Others RE: should you seperate indexes from tables in
Following on from Cary's response, both internal and external testing has shown the performance improvements of storing objects in tablespaces with different block sizes to be so miniscule as to be not worth the effort (or putting it another way, there are lots of better ways to spend your performance tuning time than worrying about this). Multiple block sizes in a single database have one purpose in life and one purpose alone - to allow transporting tablespaces between your OLTP system and a staging database before summarization of the data as it's moved into a data warehouse. Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long term Oracle DBA. -Original Message- Tracy Rahmlow Sent: Wednesday, July 16, 2003 8:14 AM 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
Re: Cary/Others RE: should you seperate indexes from tables in
why would an index necessarily need to use a smaller tablespace? is this article on hotsos? Which one is it? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 6:14 PM 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
RE: Cary/Others RE: should you seperate indexes from tables in
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
Re: Cary/Others RE: should you seperate indexes from tables in
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
