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