Re: Index Maintenance Vs Insert Response Time
- From: Antony Raj <ca_raj@xxxxxxxxx>
- To: "Iotzov, Iordan" <IIotzov@xxxxxxxxxxxxxxx>, "huanshengchen@xxxxxxxxx" <huanshengchen@xxxxxxxxx>
- Date: Tue, 7 Feb 2012 11:25:34 -0800 (PST)
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
--
//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
________________________________
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:
