Re: Index Maintenance Vs Insert Response Time
Date: Tue, 7 Feb 2012 11:25:34 -0800 (PST)
Message-ID: <1328642734.99823.YahooMailNeo_at_web36807.mail.mud.yahoo.com>
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_at_newsamerica.com> To: Antony Raj <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 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_at_yahoo.com]
Sent: Tuesday, February 07, 2012 1:16 PM
To: Iotzov, Iordan; huanshengchen_at_gmail.com
Cc: oracle-l_at_freelists.org
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_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?
Iordan Iotzov
http://iiotzov.wordpress.com/
-----Original Message-----
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
Cc: oracle-l_at_freelists.org
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_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
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-lReceived on Tue Feb 07 2012 - 13:25:34 CST