RE: Index Maintenance Vs Insert Response Time
- From: "Iotzov, Iordan" <IIotzov@xxxxxxxxxxxxxxx>
- To: "ca_raj@xxxxxxxxx" <ca_raj@xxxxxxxxx>, "huanshengchen@xxxxxxxxx" <huanshengchen@xxxxxxxxx>
- Date: Tue, 7 Feb 2012 12:08:49 -0500
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
--
//www.freelists.org/webpage/oracle-l
--
//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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
