RE: Re: IOT Tuning Question
Zhu Chao, You are right to say that with a heap organized table you also have the index to encumber the SGA and indeed you are right to say that, as I put it, what I said is not totally correct. I should have been more specific. The reference to _partitioned_ IOTs implicitly associated them to full partition scans in my mind, because the case I was referring to was some massive swoop among a lot of data, with many scans. In such a case, then indexes in general, and IOTs in particular, tend to stay much longer than required in memory, which may become a problem over time with long running processes (while table blocks are prime candidates for replacement after full scans). Quite obviously, if you are doing mostly indexed accesses, the picture may be different. I don't think that with 3 columns, unless they are well-filled VARCHAR2(4000) columns (you never know, with 3rd party software ...) overflow will be much of a problem. I'd rather fear contention, but of course it depends on the level of concurrency. SF - --- Original Message --- - From: zhu chao [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 20 Nov 2003 17:50:11 Hi, Jay: Since your table is just a table with 1M records and you have only three columns(all of them are pk), so using IOT is really an good candicate. The space save is not important because 1M records with three columns typically consumes several megabytes, which is not important at all these days. If your table is heavily DMLed, then using IOT reduced the DML to the base table, so less IO generated and less redo. I do not think SF's words are correct. IOT is indices, right. But Regular tables with indexes also consumes memory in SGA, and the index on the regular do the same thing as IOT table does. And the base table itself also consumes SGA memory. Overflow in IOT(oracle 8i) is just heap organized, in 9i it is also index organized(from my test), so if your table has overflow segment, and you insert more and more data into the table, IOT *WILL* be less efficient and you need to move the overflow segment to make the table efficient. Regards. zhu chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 21, 2003 12:30 AM Jay, On the paper, your table is indeed a good candidate for an IOT - it will save you the space used by the table (you will only have the primary key index). However, there may be gotchas. I have noticed in the past that IOTs, being primarily indices, have a tendency to be a bit 'sticky' in the SGA. I have seen massive processes wading through enormous amounts of data significantly slowing down over time with an IOT, and my interpretation was that the IOT was slowly filling up the SGA, letting fewer and fewer space to the rest. Also, think carefully about partitioning; it depends on how you query your table, mostly. It will be beneficial during inserts if you insert your rows in a random fashion in all partitions. Using a reverse key is also something you may want to consider if you have no range scan, it will help with contention. I don't think that there is an obviously good solution; it needs testing. HTH, SF - --- Original Message --- - From: Jay Wade [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 20 Nov 2003 07:44:59 Hello: I'm looking at trying to tune a 3rd party app and was wondering if anyone could tell me if my assumptions are on base. The table contains three columns, each is part of the primary key, with about 1 million + rows. I figured that it would be an ideal candidate for using a partitioned IOT, but since records are frequently inserted am I correct in assuming that it would be better to use regular partitioned table using a primary key? Since this is a 3rd party application I can't change much of the layout, if anyone has any ideas it would be greatly appreciated. Regards, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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).
Re: Re: IOT Tuning Question
Zhu/SF: Thanks for your insight. I was under the impression that Oracle did not recommend IOT for tables that where not fairly static. Would the reasoning for this not being an issue in this case be due to oracle now having to only maintain the IOT table blocks instead of the table blocks and the associates index blocks? The three table columns all being number datatypes which I think will help avoid the overflow issue. In monitoring the tables it appears that approximately 150,000 rows are added each week through a batch process. It also seems as though they are not using any type of buld loading functionality. If I rebuild to IOT on locally managed tablespaces will fragmentation be an issue? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 21, 2003 4:54 AM Zhu Chao, You are right to say that with a heap organized table you also have the index to encumber the SGA and indeed you are right to say that, as I put it, what I said is not totally correct. I should have been more specific. The reference to _partitioned_ IOTs implicitly associated them to full partition scans in my mind, because the case I was referring to was some massive swoop among a lot of data, with many scans. In such a case, then indexes in general, and IOTs in particular, tend to stay much longer than required in memory, which may become a problem over time with long running processes (while table blocks are prime candidates for replacement after full scans). Quite obviously, if you are doing mostly indexed accesses, the picture may be different. I don't think that with 3 columns, unless they are well-filled VARCHAR2(4000) columns (you never know, with 3rd party software ...) overflow will be much of a problem. I'd rather fear contention, but of course it depends on the level of concurrency. SF - --- Original Message --- - From: zhu chao [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 20 Nov 2003 17:50:11 Hi, Jay: Since your table is just a table with 1M records and you have only three columns(all of them are pk), so using IOT is really an good candicate. The space save is not important because 1M records with three columns typically consumes several megabytes, which is not important at all these days. If your table is heavily DMLed, then using IOT reduced the DML to the base table, so less IO generated and less redo. I do not think SF's words are correct. IOT is indices, right. But Regular tables with indexes also consumes memory in SGA, and the index on the regular do the same thing as IOT table does. And the base table itself also consumes SGA memory. Overflow in IOT(oracle 8i) is just heap organized, in 9i it is also index organized(from my test), so if your table has overflow segment, and you insert more and more data into the table, IOT *WILL* be less efficient and you need to move the overflow segment to make the table efficient. Regards. zhu chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 21, 2003 12:30 AM Jay, On the paper, your table is indeed a good candidate for an IOT - it will save you the space used by the table (you will only have the primary key index). However, there may be gotchas. I have noticed in the past that IOTs, being primarily indices, have a tendency to be a bit 'sticky' in the SGA. I have seen massive processes wading through enormous amounts of data significantly slowing down over time with an IOT, and my interpretation was that the IOT was slowly filling up the SGA, letting fewer and fewer space to the rest. Also, think carefully about partitioning; it depends on how you query your table, mostly. It will be beneficial during inserts if you insert your rows in a random fashion in all partitions. Using a reverse key is also something you may want to consider if you have no range scan, it will help with contention. I don't think that there is an obviously good solution; it needs testing. HTH, SF - --- Original Message --- - From: Jay Wade [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 20 Nov 2003 07:44:59 Hello: I'm looking at trying to tune a 3rd party app and was wondering if anyone could tell me if my assumptions are on base. The table contains three columns, each is part of the primary key, with about 1 million + rows. I figured that it would be an ideal candidate for using a partitioned IOT, but since records are frequently inserted am I correct in assuming that it would be better to use regular partitioned table using a primary key? Since this is a 3rd party application I can't change much of the layout, if anyone has any ideas it would be greatly appreciated. Regards, Jay
RE: IOT Tuning Question
Jay, On the paper, your table is indeed a good candidate for an IOT - it will save you the space used by the table (you will only have the primary key index). However, there may be gotchas. I have noticed in the past that IOTs, being primarily indices, have a tendency to be a bit 'sticky' in the SGA. I have seen massive processes wading through enormous amounts of data significantly slowing down over time with an IOT, and my interpretation was that the IOT was slowly filling up the SGA, letting fewer and fewer space to the rest. Also, think carefully about partitioning; it depends on how you query your table, mostly. It will be beneficial during inserts if you insert your rows in a random fashion in all partitions. Using a reverse key is also something you may want to consider if you have no range scan, it will help with contention. I don't think that there is an obviously good solution; it needs testing. HTH, SF - --- Original Message --- - From: Jay Wade [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 20 Nov 2003 07:44:59 Hello: I'm looking at trying to tune a 3rd party app and was wondering if anyone could tell me if my assumptions are on base. The table contains three columns, each is part of the primary key, with about 1 million + rows. I figured that it would be an ideal candidate for using a partitioned IOT, but since records are frequently inserted am I correct in assuming that it would be better to use regular partitioned table using a primary key? Since this is a 3rd party application I can't change much of the layout, if anyone has any ideas it would be greatly appreciated. Regards, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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).
Re: IOT Tuning Question
Hi, Jay: Since your table is just a table with 1M records and you have only three columns(all of them are pk), so using IOT is really an good candicate. The space save is not important because 1M records with three columns typically consumes several megabytes, which is not important at all these days. If your table is heavily DMLed, then using IOT reduced the DML to the base table, so less IO generated and less redo. I do not think SF's words are correct. IOT is indices, right. But Regular tables with indexes also consumes memory in SGA, and the index on the regular do the same thing as IOT table does. And the base table itself also consumes SGA memory. Overflow in IOT(oracle 8i) is just heap organized, in 9i it is also index organized(from my test), so if your table has overflow segment, and you insert more and more data into the table, IOT *WILL* be less efficient and you need to move the overflow segment to make the table efficient. Regards. zhu chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 21, 2003 12:30 AM Jay, On the paper, your table is indeed a good candidate for an IOT - it will save you the space used by the table (you will only have the primary key index). However, there may be gotchas. I have noticed in the past that IOTs, being primarily indices, have a tendency to be a bit 'sticky' in the SGA. I have seen massive processes wading through enormous amounts of data significantly slowing down over time with an IOT, and my interpretation was that the IOT was slowly filling up the SGA, letting fewer and fewer space to the rest. Also, think carefully about partitioning; it depends on how you query your table, mostly. It will be beneficial during inserts if you insert your rows in a random fashion in all partitions. Using a reverse key is also something you may want to consider if you have no range scan, it will help with contention. I don't think that there is an obviously good solution; it needs testing. HTH, SF - --- Original Message --- - From: Jay Wade [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 20 Nov 2003 07:44:59 Hello: I'm looking at trying to tune a 3rd party app and was wondering if anyone could tell me if my assumptions are on base. The table contains three columns, each is part of the primary key, with about 1 million + rows. I figured that it would be an ideal candidate for using a partitioned IOT, but since records are frequently inserted am I correct in assuming that it would be better to use regular partitioned table using a primary key? Since this is a 3rd party application I can't change much of the layout, if anyone has any ideas it would be greatly appreciated. Regards, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: zhu chao 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).
