Re: Inserts are taking time !
No, just give it a little test. Anjo. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 04, 2002 10:58 AM At 02:03 AM 9/4/2002, Anjo Kolk wrote: Yep and you have given the answer yourself. It is the number of indexes. I think that if the number of records increase the number of levels increase and slowly but surely you need to update more and more blocks. I have done sone tests (an oher people I am sure) that show that there is an expontial increase in the amount of undo and redo generated for every index that gets added into the mix. I assume that you mean exponential increase metaphorically. I can't imagine that the amount of undo redo would grow at anything greater than linearly. You will probably see an increase in CPU time (assuming that you are the only process/session on the system). Anjo. On Wednesday 04 September 2002 08:53, you wrote: Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). Justin Cave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Justin Cave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Anjo Kolk 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: Inserts are taking time !
Marul, "11 are indexed." - That's the first place I would look into. Check it out just dropping them. Make sure you realy needall the indexes. Consider to drop them before and recreate after loading. Alexandre - Original Message - From: Marul Mehta To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 04, 2002 8:53 AM Subject: Inserts are taking time ! Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul.
Re: Inserts are taking time !
Thanks Justin, Now its high time and I have to take some harsh steps to resolve this bottleneck. This might even go to an extent of changing the app code. But I have to do it, there is no way out. Thank you all for the support. I will get back to you with my reading and implementation in a day or two (even if this is successful or not). bfn Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 06, 2002 12:28 PM At 09:08 AM 9/5/2002, you wrote: Thanks a lot for the response, No its not a batch insert; each insert is done with auto-commit=true from the java application. So, after each insert a commit is done at the Db level, which is the root cause of such a delay, I guess. Yes, yes, a thousand times yes. I'm willing to wager that 90% of your time is spent waiting for the commits in this scenario. But if this auto-commit is the issue than why first 10K records are inserted quickly. Well, it's only slower by a factor of 6 when you go from 0 rows to 350,000 rows, which seems reasonable. When you have 0 rows in the database, you probably have the table, indexes, etc. completely cached in memory. When the table grows larger, however, more and more stuff will be coming from the disk. Additionally, the commits are likely to be a bit more complicated the more rows you have, because the number of blocks that need to be touched in the indexes will go up. I cannot disable constraints even for a sinlge second as there will be heavy reads going on even when inserts are taking place. Can you do batch inserts, so that you're not committing 10,000 times? Modify the application to batch 1000 statements at a time, and I'll be you get much happier very quickly. Any clues? TAI Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:53 PM Marul, I think this question needs to be answered, otherwise impossible to make suggestions... Chris -Original Message- Sent: 05 September 2002 08:18 To: Multiple recipients of list ORACLE-L Marul, what i fail to understand is: Are you running a batch job of inserting 350,000 inserts? If that is the case then you should go for dropping and recreating the indexes. Can you partition the table and use local partitioned indexes? Can't you try the option of inserting in parallel? Did you try disabling the constraints and then ENABLE NOVALIDATE them(that will only work if you r sure of the data)? Naveen -Original Message- Sent: Thursday, September 05, 2002 11:48 AM To: Multiple recipients of list ORACLE-L Thanks Chris, So than any clues how to resolve this issue, as earliest, becuase this is causing bottleneck in our application . Rgds, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:29 AM Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the index things start to slow down Chris -Original Message- Sent: 04 September 2002 16:50 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: It sounds to me like the indexes are going into overflow - this will cause What do you mean by 'overflow'? Jared the insert time to increase. I would suggest batching up the inserts, dropping the indexes, running the inserts and re-creating the indexes. Chris -Original Message- Sent: 04 September 2002 07:53 To: Multiple recipients of list ORACLE-L Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to.
RE: Inserts are taking time !
Removing the auto-commit sounds good, on condition, of course, that you can afford to lose up to 999 recs in the event of a system crash. You probably may want to write them to a temp storage table first Chris -Original Message- Sent: 06 September 2002 09:28 To: Multiple recipients of list ORACLE-L Thanks Justin, Now its high time and I have to take some harsh steps to resolve this bottleneck. This might even go to an extent of changing the app code. But I have to do it, there is no way out. Thank you all for the support. I will get back to you with my reading and implementation in a day or two (even if this is successful or not). bfn Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 06, 2002 12:28 PM At 09:08 AM 9/5/2002, you wrote: Thanks a lot for the response, No its not a batch insert; each insert is done with auto-commit=true from the java application. So, after each insert a commit is done at the Db level, which is the root cause of such a delay, I guess. Yes, yes, a thousand times yes. I'm willing to wager that 90% of your time is spent waiting for the commits in this scenario. But if this auto-commit is the issue than why first 10K records are inserted quickly. Well, it's only slower by a factor of 6 when you go from 0 rows to 350,000 rows, which seems reasonable. When you have 0 rows in the database, you probably have the table, indexes, etc. completely cached in memory. When the table grows larger, however, more and more stuff will be coming from the disk. Additionally, the commits are likely to be a bit more complicated the more rows you have, because the number of blocks that need to be touched in the indexes will go up. I cannot disable constraints even for a sinlge second as there will be heavy reads going on even when inserts are taking place. Can you do batch inserts, so that you're not committing 10,000 times? Modify the application to batch 1000 statements at a time, and I'll be you get much happier very quickly. Any clues? TAI Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:53 PM Marul, I think this question needs to be answered, otherwise impossible to make suggestions... Chris -Original Message- Sent: 05 September 2002 08:18 To: Multiple recipients of list ORACLE-L Marul, what i fail to understand is: Are you running a batch job of inserting 350,000 inserts? If that is the case then you should go for dropping and recreating the indexes. Can you partition the table and use local partitioned indexes? Can't you try the option of inserting in parallel? Did you try disabling the constraints and then ENABLE NOVALIDATE them(that will only work if you r sure of the data)? Naveen -Original Message- Sent: Thursday, September 05, 2002 11:48 AM To: Multiple recipients of list ORACLE-L Thanks Chris, So than any clues how to resolve this issue, as earliest, becuase this is causing bottleneck in our application . Rgds, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:29 AM Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the index things start to slow down Chris -Original Message- Sent: 04 September 2002 16:50 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: It sounds to me like the indexes are going into overflow - this will cause What do you mean by 'overflow'? Jared the insert time to increase. I would suggest batching up the inserts, dropping the indexes, running the inserts and re-creating the indexes. Chris -Original Message- Sent: 04 September 2002 07:53 To: Multiple recipients of list ORACLE-L Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records.
RE: Inserts are taking time !
Hi Marul, A few things to check before you alter the application. 1. Have you checked the growth of extents in dba_extents for the concerned table and indexes as you insert rows in the table? I would suggest to create the table and indexes with large initial and next extent sizes. 2. You can also use nologging option when you create the table and indexes. You can experiment with append and/or parallel hint in your insert. 3. I would also monitor the rollback segment that your transaction is using, if the table is being read at the same time if delete/inserts are going on, then it is likely that your RBS may be extending too much. You might want to revisit the sizing of the extents for RBS. Cheers Viral From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Inserts are taking time ! Date: Fri, 06 Sep 2002 04:28:19 -0800 Removing the auto-commit sounds good, on condition, of course, that you can afford to lose up to 999 recs in the event of a system crash. You probably may want to write them to a temp storage table first Chris -Original Message- Sent: 06 September 2002 09:28 To: Multiple recipients of list ORACLE-L Thanks Justin, Now its high time and I have to take some harsh steps to resolve this bottleneck. This might even go to an extent of changing the app code. But I have to do it, there is no way out. Thank you all for the support. I will get back to you with my reading and implementation in a day or two (even if this is successful or not). bfn Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 06, 2002 12:28 PM At 09:08 AM 9/5/2002, you wrote: Thanks a lot for the response, No its not a batch insert; each insert is done with auto-commit=true from the java application. So, after each insert a commit is done at the Db level, which is the root cause of such a delay, I guess. Yes, yes, a thousand times yes. I'm willing to wager that 90% of your time is spent waiting for the commits in this scenario. But if this auto-commit is the issue than why first 10K records are inserted quickly. Well, it's only slower by a factor of 6 when you go from 0 rows to 350,000 rows, which seems reasonable. When you have 0 rows in the database, you probably have the table, indexes, etc. completely cached in memory. When the table grows larger, however, more and more stuff will be coming from the disk. Additionally, the commits are likely to be a bit more complicated the more rows you have, because the number of blocks that need to be touched in the indexes will go up. I cannot disable constraints even for a sinlge second as there will be heavy reads going on even when inserts are taking place. Can you do batch inserts, so that you're not committing 10,000 times? Modify the application to batch 1000 statements at a time, and I'll be you get much happier very quickly. Any clues? TAI Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:53 PM Marul, I think this question needs to be answered, otherwise impossible to make suggestions... Chris -Original Message- Sent: 05 September 2002 08:18 To: Multiple recipients of list ORACLE-L Marul, what i fail to understand is: Are you running a batch job of inserting 350,000 inserts? If that is the case then you should go for dropping and recreating the indexes. Can you partition the table and use local partitioned indexes? Can't you try the option of inserting in parallel? Did you try disabling the constraints and then ENABLE NOVALIDATE them(that will only work if you r sure of the data)? Naveen -Original Message- Sent: Thursday, September 05, 2002 11:48 AM To: Multiple recipients of list ORACLE-L Thanks Chris, So than any clues how to resolve this issue, as earliest, becuase this is causing bottleneck in our application . Rgds, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:29 AM Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels
RE: Inserts are taking time !
This appears to be a classic example of a design that cripples performance and once in production 'nothing' can be done to solve the problem. By nothing, I mean that possible options are not 'possible' due to external (business, application) reasons. Here are some areas to examine: 1. Are all the indexes actually in use? Are there some that are redundant (identical leading columns)? Are there 'performance' indexes that are rarely used? The fewer the indexes, the fewer i/os for each insert, the faster it will be. 2. Is there an off-time where the indexes can be rebuilt for better performance? Perhaps even nightly? 3. Can you use any sort of partitioning? Can the application use partition elimination? 4. How immediate is the need to see the data? If there can be a delay, consider inserting into a transient table and then performing a bulk load during off hours. 5. Are you using the background processes efficiently? With a single dbwr, you may be getting bogged down in writing all these blocks. If logwr is slow, check for i/o contention. 6. How much space management is occurring? Are you constantly throwing extents for the table/indexes? Here are some areas that can be given low priority: (knowing full well this is an invitation to heated disagreements with other listers) 1. RBS - Adding rbs space will not help the situation, unless you are seeing rbs related errors. The real problem is the time that may be required to create a read-consistent view of the data. 2. Commit time - The time for a 'commit' to occur is more a function of the number of changes (i.e. table data + each index entry) than the amount of data currently in the table/index. Good luck. I hope this provides some areas to examine. Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Inserts are taking time !
On Friday 06 September 2002 19:13, you wrote: 2. Commit time - The time for a 'commit' to occur is more a function of the number of changes (i.e. table data + each index entry) than the amount of data currently in the table/index. More data in the index means a greater chance on getting index block splits (especially with small block sizes and large keys), and block splits and adding levels will generate more redo and have an effect on the commit time. Anjo. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Inserts are taking time !
Marul, what i fail to understand is: Are you running a batch job of inserting 350,000 inserts? If that is the case then you should go for dropping and recreating the indexes. Can you partition the table and use local partitioned indexes? Can't you try the option of inserting in parallel? Did you try disabling the constraints and then ENABLE NOVALIDATE them(that will only work if you r sure of the data)? Naveen -Original Message- Sent: Thursday, September 05, 2002 11:48 AM To: Multiple recipients of list ORACLE-L Thanks Chris, So than any clues how to resolve this issue, as earliest, becuase this is causing bottleneck in our application . Rgds, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:29 AM Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the index things start to slow down Chris -Original Message- Sent: 04 September 2002 16:50 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: It sounds to me like the indexes are going into overflow - this will cause What do you mean by 'overflow'? Jared the insert time to increase. I would suggest batching up the inserts, dropping the indexes, running the inserts and re-creating the indexes. Chris -Original Message- Sent: 04 September 2002 07:53 To: Multiple recipients of list ORACLE-L Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Inserts are taking time !
Marul, I think this question needs to be answered, otherwise impossible to make suggestions... Chris -Original Message- Sent: 05 September 2002 08:18 To: Multiple recipients of list ORACLE-L Marul, what i fail to understand is: Are you running a batch job of inserting 350,000 inserts? If that is the case then you should go for dropping and recreating the indexes. Can you partition the table and use local partitioned indexes? Can't you try the option of inserting in parallel? Did you try disabling the constraints and then ENABLE NOVALIDATE them(that will only work if you r sure of the data)? Naveen -Original Message- Sent: Thursday, September 05, 2002 11:48 AM To: Multiple recipients of list ORACLE-L Thanks Chris, So than any clues how to resolve this issue, as earliest, becuase this is causing bottleneck in our application . Rgds, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:29 AM Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the index things start to slow down Chris -Original Message- Sent: 04 September 2002 16:50 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: It sounds to me like the indexes are going into overflow - this will cause What do you mean by 'overflow'? Jared the insert time to increase. I would suggest batching up the inserts, dropping the indexes, running the inserts and re-creating the indexes. Chris -Original Message- Sent: 04 September 2002 07:53 To: Multiple recipients of list ORACLE-L Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
RE: Inserts are taking time !
Jared, This stuff for fun was great!!! http://www.computerworld.com/departments/opinions/letters/0,10817,73890,00.h tml :) - Kirti -Original Message- Sent: Wednesday, September 04, 2002 6:18 PM To: Multiple recipients of list ORACLE-L Some of us have work to do, you know ;) Not me, I'm independently wealthy. I just stay up late to do this stuff for fun. Jared ;) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/04/2002 01:59 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Inserts are taking time ! Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the index things start to slow down Chris -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Inserts are taking time !
Thanks a lot for the response, No its not a batch insert; each insert is done with auto-commit=true from the java application. So, after each insert a commit is done at the Db level, which is the root cause of such a delay, I guess. But if this auto-commit is the issue than why first 10K records are inserted quickly. I cannot disable constraints even for a sinlge second as there will be heavy reads going on even when inserts are taking place. Any clues? TAI Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:53 PM Marul, I think this question needs to be answered, otherwise impossible to make suggestions... Chris -Original Message- Sent: 05 September 2002 08:18 To: Multiple recipients of list ORACLE-L Marul, what i fail to understand is: Are you running a batch job of inserting 350,000 inserts? If that is the case then you should go for dropping and recreating the indexes. Can you partition the table and use local partitioned indexes? Can't you try the option of inserting in parallel? Did you try disabling the constraints and then ENABLE NOVALIDATE them(that will only work if you r sure of the data)? Naveen -Original Message- Sent: Thursday, September 05, 2002 11:48 AM To: Multiple recipients of list ORACLE-L Thanks Chris, So than any clues how to resolve this issue, as earliest, becuase this is causing bottleneck in our application . Rgds, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:29 AM Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the index things start to slow down Chris -Original Message- Sent: 04 September 2002 16:50 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: It sounds to me like the indexes are going into overflow - this will cause What do you mean by 'overflow'? Jared the insert time to increase. I would suggest batching up the inserts, dropping the indexes, running the inserts and re-creating the indexes. Chris -Original Message- Sent: 04 September 2002 07:53 To: Multiple recipients of list ORACLE-L Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
RE: Inserts are taking time !
Mahul, Sounds a bit of a classic problem - optimum performance and availability required with no time given for database admin/reorg work. One idea you might think about is messy but could involve the following: Step 1 Create 2 tables - Prob_table and prob_table_copy Step 2 Create indexes on prob_table Step 2 Load up 10,000 rows into prob_table(while being read at the same time) Step 3 Load next 10,000 rows into prob_table_copy Step 4 Insert into prob_table_copy select * from prob_table Step 5 Build indexes on prob_table_copy step 6 Rename prob_table to prob_table_temp Step 7 Rename prob_table_copy to prob_table Step 8 Insert into prob_table select * from prob_table_temp where timestamp (to capture rows that were inserted after the select was called) Step 9 Load up next 10,000 rows into prob_table(while being read at the same time) Step 10 Rename prob_table_temp to prob_table_copy etc I'm sure you get the idea. As I mentioned messy but not impossible. In addition you will also need to capture timestamps if you don't already. Good luck! Chris -Original Message- Sent: 05 September 2002 16:08 To: Multiple recipients of list ORACLE-L Thanks a lot for the response, No its not a batch insert; each insert is done with auto-commit=true from the java application. So, after each insert a commit is done at the Db level, which is the root cause of such a delay, I guess. But if this auto-commit is the issue than why first 10K records are inserted quickly. I cannot disable constraints even for a sinlge second as there will be heavy reads going on even when inserts are taking place. Any clues? TAI Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:53 PM Marul, I think this question needs to be answered, otherwise impossible to make suggestions... Chris -Original Message- Sent: 05 September 2002 08:18 To: Multiple recipients of list ORACLE-L Marul, what i fail to understand is: Are you running a batch job of inserting 350,000 inserts? If that is the case then you should go for dropping and recreating the indexes. Can you partition the table and use local partitioned indexes? Can't you try the option of inserting in parallel? Did you try disabling the constraints and then ENABLE NOVALIDATE them(that will only work if you r sure of the data)? Naveen -Original Message- Sent: Thursday, September 05, 2002 11:48 AM To: Multiple recipients of list ORACLE-L Thanks Chris, So than any clues how to resolve this issue, as earliest, becuase this is causing bottleneck in our application . Rgds, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:29 AM Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the index things start to slow down Chris -Original Message- Sent: 04 September 2002 16:50 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: It sounds to me like the indexes are going into overflow - this will cause What do you mean by 'overflow'? Jared the insert time to increase. I would suggest batching up the inserts, dropping the indexes, running the inserts and re-creating the indexes. Chris -Original Message- Sent: 04 September 2002 07:53 To: Multiple recipients of list ORACLE-L Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ:
Re: Inserts are taking time !
At 09:08 AM 9/5/2002, you wrote: Thanks a lot for the response, No its not a batch insert; each insert is done with auto-commit=true from the java application. So, after each insert a commit is done at the Db level, which is the root cause of such a delay, I guess. Yes, yes, a thousand times yes. I'm willing to wager that 90% of your time is spent waiting for the commits in this scenario. But if this auto-commit is the issue than why first 10K records are inserted quickly. Well, it's only slower by a factor of 6 when you go from 0 rows to 350,000 rows, which seems reasonable. When you have 0 rows in the database, you probably have the table, indexes, etc. completely cached in memory. When the table grows larger, however, more and more stuff will be coming from the disk. Additionally, the commits are likely to be a bit more complicated the more rows you have, because the number of blocks that need to be touched in the indexes will go up. I cannot disable constraints even for a sinlge second as there will be heavy reads going on even when inserts are taking place. Can you do batch inserts, so that you're not committing 10,000 times? Modify the application to batch 1000 statements at a time, and I'll be you get much happier very quickly. Any clues? TAI Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:53 PM Marul, I think this question needs to be answered, otherwise impossible to make suggestions... Chris -Original Message- Sent: 05 September 2002 08:18 To: Multiple recipients of list ORACLE-L Marul, what i fail to understand is: Are you running a batch job of inserting 350,000 inserts? If that is the case then you should go for dropping and recreating the indexes. Can you partition the table and use local partitioned indexes? Can't you try the option of inserting in parallel? Did you try disabling the constraints and then ENABLE NOVALIDATE them(that will only work if you r sure of the data)? Naveen -Original Message- Sent: Thursday, September 05, 2002 11:48 AM To: Multiple recipients of list ORACLE-L Thanks Chris, So than any clues how to resolve this issue, as earliest, becuase this is causing bottleneck in our application . Rgds, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:29 AM Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the index things start to slow down Chris -Original Message- Sent: 04 September 2002 16:50 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: It sounds to me like the indexes are going into overflow - this will cause What do you mean by 'overflow'? Jared the insert time to increase. I would suggest batching up the inserts, dropping the indexes, running the inserts and re-creating the indexes. Chris -Original Message- Sent: 04 September 2002 07:53 To: Multiple recipients of list ORACLE-L Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
Re: Inserts are taking time !
Yep and you have given the answer yourself. It is the number of indexes. I think that if the number of records increase the number of levels increase and slowly but surely you need to update more and more blocks. I have done sone tests (an oher people I am sure) that show that there is an expontial increase in the amount of undo and redo generated for every index that gets added into the mix. You will probably see an increase in CPU time (assuming that you are the only process/session on the system). Anjo. On Wednesday 04 September 2002 08:53, you wrote: Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Inserts are taking time !
At 02:03 AM 9/4/2002, Anjo Kolk wrote: Yep and you have given the answer yourself. It is the number of indexes. I think that if the number of records increase the number of levels increase and slowly but surely you need to update more and more blocks. I have done sone tests (an oher people I am sure) that show that there is an expontial increase in the amount of undo and redo generated for every index that gets added into the mix. I assume that you mean exponential increase metaphorically. I can't imagine that the amount of undo redo would grow at anything greater than linearly. You will probably see an increase in CPU time (assuming that you are the only process/session on the system). Anjo. On Wednesday 04 September 2002 08:53, you wrote: Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). Justin Cave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Justin Cave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Inserts are taking time !
Thanks for the immediate reply But my requirement is such that I cannot reduce the indexes. There are lots of selects happeneing on this table based on these indexed columns. Our entire application is about to move in the production environment and we cant change our DB design at this time. Please suggest TIA, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 04, 2002 1:33 PM Yep and you have given the answer yourself. It is the number of indexes. I think that if the number of records increase the number of levels increase and slowly but surely you need to update more and more blocks. I have done sone tests (an oher people I am sure) that show that there is an expontial increase in the amount of undo and redo generated for every index that gets added into the mix. You will probably see an increase in CPU time (assuming that you are the only process/session on the system). Anjo. On Wednesday 04 September 2002 08:53, you wrote: Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Inserts are taking time !
Marul, 10k records in 1 hour(3600 seconds) 1 record in 3600/1 = approx 0.36 seconds If your application is OLTP you'll be inserting records 1 by 1 rather than in bulk. Which means the effect will hardly be noticed. If you are going to insert record in bulk you can DROP and then recreate the indexes after load. Check what takes more time. See if there is any scope of partitioning the table, to use local partitioned indexes. For bulk load, disabling the constraints is also an option. Naveen -Original Message- Sent: Wednesday, September 04, 2002 3:13 PM To: Multiple recipients of list ORACLE-L Thanks for the immediate reply But my requirement is such that I cannot reduce the indexes. There are lots of selects happeneing on this table based on these indexed columns. Our entire application is about to move in the production environment and we cant change our DB design at this time. Please suggest TIA, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 04, 2002 1:33 PM Yep and you have given the answer yourself. It is the number of indexes. I think that if the number of records increase the number of levels increase and slowly but surely you need to update more and more blocks. I have done sone tests (an oher people I am sure) that show that there is an expontial increase in the amount of undo and redo generated for every index that gets added into the mix. You will probably see an increase in CPU time (assuming that you are the only process/session on the system). Anjo. On Wednesday 04 September 2002 08:53, you wrote: Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Inserts are taking time !
Marul, It sounds to me like the indexes are going into overflow - this will cause the insert time to increase. I would suggest batching up the inserts, dropping the indexes, running the inserts and re-creating the indexes. Chris -Original Message-From: Marul Mehta [mailto:[EMAIL PROTECTED]]Sent: 04 September 2002 07:53To: Multiple recipients of list ORACLE-LSubject: Inserts are taking time ! Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul.
RE: Inserts are taking time !
Marul, Are there any bitmapped indexes on the table Iain Nicoll -Original Message- Sent: Wednesday, September 04, 2002 11:28 AM To: Multiple recipients of list ORACLE-L Marul, 10k records in 1 hour(3600 seconds) 1 record in 3600/1 = approx 0.36 seconds If your application is OLTP you'll be inserting records 1 by 1 rather than in bulk. Which means the effect will hardly be noticed. If you are going to insert record in bulk you can DROP and then recreate the indexes after load. Check what takes more time. See if there is any scope of partitioning the table, to use local partitioned indexes. For bulk load, disabling the constraints is also an option. Naveen -Original Message- Sent: Wednesday, September 04, 2002 3:13 PM To: Multiple recipients of list ORACLE-L Thanks for the immediate reply But my requirement is such that I cannot reduce the indexes. There are lots of selects happeneing on this table based on these indexed columns. Our entire application is about to move in the production environment and we cant change our DB design at this time. Please suggest TIA, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 04, 2002 1:33 PM Yep and you have given the answer yourself. It is the number of indexes. I think that if the number of records increase the number of levels increase and slowly but surely you need to update more and more blocks. I have done sone tests (an oher people I am sure) that show that there is an expontial increase in the amount of undo and redo generated for every index that gets added into the mix. You will probably see an increase in CPU time (assuming that you are the only process/session on the system). Anjo. On Wednesday 04 September 2002 08:53, you wrote: Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Nicoll, Iain \(Calanais\) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
Re: Inserts are taking time !
On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: It sounds to me like the indexes are going into overflow - this will cause What do you mean by 'overflow'? Jared the insert time to increase. I would suggest batching up the inserts, dropping the indexes, running the inserts and re-creating the indexes. Chris -Original Message- Sent: 04 September 2002 07:53 To: Multiple recipients of list ORACLE-L Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Inserts are taking time !
Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the index things start to slow down Chris -Original Message- Sent: 04 September 2002 16:50 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: It sounds to me like the indexes are going into overflow - this will cause What do you mean by 'overflow'? Jared the insert time to increase. I would suggest batching up the inserts, dropping the indexes, running the inserts and re-creating the indexes. Chris -Original Message- Sent: 04 September 2002 07:53 To: Multiple recipients of list ORACLE-L Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Inserts are taking time !
Some of us have work to do, you know ;) Not me, I'm independently wealthy. I just stay up late to do this stuff for fun. Jared ;) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/04/2002 01:59 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Inserts are taking time ! Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the index things start to slow down Chris -Original Message- Sent: 04 September 2002 16:50 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: It sounds to me like the indexes are going into overflow - this will cause What do you mean by 'overflow'? Jared the insert time to increase. I would suggest batching up the inserts, dropping the indexes, running the inserts and re-creating the indexes. Chris -Original Message- Sent: 04 September 2002 07:53 To: Multiple recipients of list ORACLE-L Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Inserts are taking time !
No there is not a single bitmap indexes. We had previously but than removed all and converted to normal b-tree indexes. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 04, 2002 4:58 PM Marul, Are there any bitmapped indexes on the table Iain Nicoll -Original Message- Sent: Wednesday, September 04, 2002 11:28 AM To: Multiple recipients of list ORACLE-L Marul, 10k records in 1 hour(3600 seconds) 1 record in 3600/1 = approx 0.36 seconds If your application is OLTP you'll be inserting records 1 by 1 rather than in bulk. Which means the effect will hardly be noticed. If you are going to insert record in bulk you can DROP and then recreate the indexes after load. Check what takes more time. See if there is any scope of partitioning the table, to use local partitioned indexes. For bulk load, disabling the constraints is also an option. Naveen -Original Message- Sent: Wednesday, September 04, 2002 3:13 PM To: Multiple recipients of list ORACLE-L Thanks for the immediate reply But my requirement is such that I cannot reduce the indexes. There are lots of selects happeneing on this table based on these indexed columns. Our entire application is about to move in the production environment and we cant change our DB design at this time. Please suggest TIA, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 04, 2002 1:33 PM Yep and you have given the answer yourself. It is the number of indexes. I think that if the number of records increase the number of levels increase and slowly but surely you need to update more and more blocks. I have done sone tests (an oher people I am sure) that show that there is an expontial increase in the amount of undo and redo generated for every index that gets added into the mix. You will probably see an increase in CPU time (assuming that you are the only process/session on the system). Anjo. On Wednesday 04 September 2002 08:53, you wrote: Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Nicoll, Iain \(Calanais\) INET: [EMAIL PROTECTED]
Re: Inserts are taking time !
Thanks Chris, So than any clues how to resolve this issue, as earliest, becuase this is causing bottleneck in our application . Rgds, Marul. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 05, 2002 2:29 AM Good question, Jared! Perhaps 'overflow' is technically not the correct term to use to decribe this scenario but it seemed to fit the bill sufficiently to mail off a quick one-liner solution without going into great depth. Some of us have work to do, you know ;) To redeem myself I probably should have mentioned that this table sounds pretty volatile. Consequently the index(es) are likely to end up fairly disorganized, especially if the 350k records are being inserted in ascending order. Once you start adding levels to the index things start to slow down Chris -Original Message- Sent: 04 September 2002 16:50 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: It sounds to me like the indexes are going into overflow - this will cause What do you mean by 'overflow'? Jared the insert time to increase. I would suggest batching up the inserts, dropping the indexes, running the inserts and re-creating the indexes. Chris -Original Message- Sent: 04 September 2002 07:53 To: Multiple recipients of list ORACLE-L Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul. Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
