Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Inserts are taking time !

RE: Inserts are taking time !

From: viral desai <viral303_at_hotmail.com>
Date: Fri, 06 Sep 2002 06:58:29 -0800
Message-ID: <F001.004C9BC7.20020906065829@fatcity.com> 





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: chris.w.johnson_at_bt.com

>Reply-To: ORACLE-L_at_fatcity.com

>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

>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" <ORACLE-L_at_fatcity.com>

>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" <ORACLE-L_at_fatcity.com>

> > >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" <ORACLE-L_at_fatcity.com>

> > > > 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: ORACLE-L_at_fatcity.com; chris.w.johnson_at_bt.com

> > > > >

> > > > >

> > > > > On Wednesday 04 September 2002 04:23, chris.w.johnson_at_bt.com 

>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: chris.w.johnson_at_bt.com

> > > > >

> > > > > 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: ListGuru_at_fatcity.com (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: marul_at_zycus.com

> > > >

> > > > 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: ListGuru_at_fatcity.com (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: naveen_nahata_at_mindtree.com

> > > >

> > > > 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: ListGuru_at_fatcity.com (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: chris.w.johnson_at_bt.com

> > > >

> > > > 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: ListGuru_at_fatcity.com (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: marul_at_zycus.com

> > >

> > >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: ListGuru_at_fatcity.com (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: jcave_at_cableone.net

> >

> > 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: ListGuru_at_fatcity.com (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: marul_at_zycus.com

>

>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: ListGuru_at_fatcity.com (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: chris.w.johnson_at_bt.com

>

>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: ListGuru_at_fatcity.com (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).









Send and receive Hotmail on your mobile device: http://mobile.msn.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: viral desai
  INET: viral303_at_hotmail.com

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: ListGuru_at_fatcity.com (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).


Received on Fri Sep 06 2002 - 09:58:29 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US