Re: Index Maintenance Vs Insert Response Time

No.I had increased the sga size from 3GB to 6GB.
I analyzed the indexes.PCT_USED from index_stats shows 64% and there are 
deleted leaf rows 224838 & 116373 respectively on two non-unique indexes.
If i rebuild them,i could push pct_used up to 90% and one of the indexes height 
may reduce to 3 from 4.
 

________________________________
From: "Iotzov, Iordan" <IIotzov@xxxxxxxxxxxxxxx>
To: Antony Raj <ca_raj@xxxxxxxxx>; "huanshengchen@xxxxxxxxx" 
<huanshengchen@xxxxxxxxx> 
Cc: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx> 
Sent: Tuesday, February 7, 2012 1:52 PM
Subject: RE: Index Maintenance Vs Insert Response Time


Is it fair to say that the size of your buffer cache has stayed the same while 
the size of your database, not only the table in question, has grown 
significantly? 
 
Iordan Iotzov
http://iiotzov.wordpress.com/
 
 
From:Antony Raj [mailto:ca_raj@xxxxxxxxx] 
Sent: Tuesday, February 07, 2012 1:16 PM
To: Iotzov, Iordan; huanshengchen@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Index Maintenance Vs Insert Response Time
 
93% of execution time spent on the wait event "db file sequential read" on the 
Unique Index of the table.


 
From:"Iotzov, Iordan" <IIotzov@xxxxxxxxxxxxxxx>
To: "ca_raj@xxxxxxxxx" <ca_raj@xxxxxxxxx>; "huanshengchen@xxxxxxxxx" 
<huanshengchen@xxxxxxxxx> 
Cc: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx> 
Sent: Tuesday, February 7, 2012 12:08 PM
Subject: RE: Index Maintenance Vs Insert Response Time

Are there any triggers or constraints defined on the table?

Even though the indexes of the large table can be significantly bigger compared 
to when it was smaller, their height would only increase by one ( from 2 to 3 
most likely) . The increased index height would slow most SQL statements, 
including INSERTs, but that (my opinion!)  could not account for the reported 5 
times increase in execution time.

What wait event was most prevalent during the execution?

Iordan Iotzov
http://iiotzov.wordpress.com/

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

Hi

It's an Insert .... select.
The select part is spending exactly the same amount of time(13 secs) from the 
extended trace.


Rows  Operation

0  LOAD TABLE CONVENTIONAL (crC1112 pr4448 pw=0 time11385671 us)
43,564    FILTER (crB7284 prD8 pw=0 time572425 us)

Thanks

________________________________
From: Sidney Chen <huanshengchen@xxxxxxxxx>
To: ca_raj@xxxxxxxxx
Cc: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, February 7, 2012 11:05 AM
Subject: Re: Index Maintenance Vs Insert Response Time

Hi Antony,
Have you enabled entended sql trace to check the what's the major component
of the current 6-7 minutes. Unless identifying what's responsible for the
response time, we can only guess.

what's the method to insert the rows, are inserted in a single
insert...select... statment, in by inserting 45,000 times, in such a
case,  you may want to try the array insert method, the forall statment.



--
Regards
Sidney Chen


--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l




This message and its attachments may contain legally privileged or confidential 
information. It is intended solely for the named addressee. If you are not the 
addressee indicated in this message (or responsible for delivery of the message 
to the addressee), you may not copy or deliver this message or its attachments 
to anyone. Rather, you should permanently delete this message and its 
attachments and kindly notify the sender by reply e-mail. Any content of this 
message and its attachments that does not relate to the official business of 
News America Incorporated or its subsidiaries must be taken not to have been 
sent or endorsed by any of them. No warranty is made that the e-mail or 
attachment(s) are free from computer virus or other defect.
--
http://www.freelists.org/webpage/oracle-l





________________________________
This message and its attachments may contain legally privileged or confidential 
information. It is intended solely for the named addressee. If you are not the 
addressee indicated in this message (or responsible for delivery of the message 
to the addressee), you may not copy or deliver this message or its attachments 
to anyone. Rather, you should permanently delete this message and its 
attachments and kindly notify the sender by reply e-mail. Any content of this 
message and its attachments that does not relate to the official business of 
News America Incorporated or its subsidiaries must be taken not to have been 
sent or endorsed by any of them. No warranty is made that the e-mail or 
attachment(s) are free from computer virus or other defect.
--
http://www.freelists.org/webpage/oracle-l


Other related posts: