Re: Index Maintenance Vs Insert Response Time
Date: Tue, 7 Feb 2012 10:15:50 -0800 (PST)
93% of execution time spent on the wait event "db file sequential read" on the Unique Index of the table.
From: "Iotzov, Iordan" <IIotzov_at_newsamerica.com> To: "ca_raj_at_yahoo.com" <ca_raj_at_yahoo.com>; "huanshengchen_at_gmail.com" <huanshengchen_at_gmail.com> Cc: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> 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?
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Antony Raj Sent: Tuesday, February 07, 2012 11:17 AM To: huanshengchen_at_gmail.com
Subject: Re: Index Maintenance Vs Insert Response Time
It's an Insert .... select.
The select part is spending exactly the same amount of time(13 secs) from the extended trace.
0 LOAD TABLE CONVENTIONAL (crC1112 pr4448 pw=0 time11385671 us) 43,564 FILTER (crB7284 prD8 pw=0 time572425 us)
From: Sidney Chen <huanshengchen_at_gmail.com> To: ca_raj_at_yahoo.com
Cc: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Tuesday, February 7, 2012 11:05 AM Subject: Re: Index Maintenance Vs Insert Response Time
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.
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 Received on Tue Feb 07 2012 - 12:15:50 CST