RE: Index Maintenance Vs Insert Response Time

  • From: "CRISLER, JON A" <JC1706@xxxxxxx>
  • To: "ca_raj@xxxxxxxxx" <ca_raj@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 7 Feb 2012 15:59:51 +0000

Check your b level from dba_indexes and see if it jumped from 2 to 3, or 3 to 4 
etc, once you crossed the 10 million row threshold.  Also try to run a alter 
index rebuild online, including valid storage parameters.  Also look into 
dbms_stats on those indexes.  

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Antony Raj
Sent: Tuesday, February 07, 2012 10:24 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Index Maintenance Vs Insert Response Time

Hi All,
Our application have a staging table which has 3 indexes on it.There are two 
INSERTs running everyday by selecting different sets of data from other 
application tables.Both inserts were inserting 
~ 45,000 rows everyday.The current row count of this table is 32 million(Not 
Both INSERTs took ~ 1-2 minutes until the table's row count has reached 10 
million and then the elapsed time has been increased to 6-7 minutes
Since we insert the same amount of rows into this table,the expectation from 
the developers is that the inserts should run within 2 minutes all days.
Any explanations?


Other related posts: