| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: cache buffers chain latch contention
Thanks again Jonathan. "Dan Tow method" - are you referring to his
book? Would anyone here recommend it? If it is going to give me a new
perspective on this mess and SQL in general, I will take the time to dig
through it.
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
Sent: Thursday, March 25, 2004 1:56 PM
To: oracle-l_at_freelists.org
Subject: Re: cache buffers chain latch contention
Lisa,
As usual, the plan is pretty unreadable after passing through outlook, and it seems to be missing the bit which is the parallel execution bit.
However, I don't think you can try to fix this on development, and then hope that it will work on production less the data and statistics are identical.
I think your contention problem is at line 2. You have a nested loop that runs converges from parallel to serial.
That line, and all the lines from 2 to 12 are run as a join in a single slave level (parallel combined with parent).
The last action of that set of lines is a nested loop join INTO the index IE1_CNTL_BUSINESS_DAY_IDX. So you have eight slaves screaming through all the data from the 10 previous join steps hitting the small number of blocks that make up that index.
At that point, Oracle seems to think you have only 91 rows to join - (Line 3 rows = 91, bytes = 765341) but I'll bet you have a lot more than that.
The MAIN problem, I suspect, is that Oracle hasn't worked out the cardinalities correctly, so has produced a bad join order. If you can work out the best way to drive the plan (and perhaps Oracle needs to be told that there will be only one row - my guess - coming out of CNTL_BUSINESS_DAY) then you probably won't have to fiddle around with rebuilding data to avoid contention.
BTW - the if my guess about cntl_business_day table is correct, then one simple step is to use the /*+ cardinality(t 1) */ hint to tell Oracle that only one row will come out based on the supplied single-table predicates.
Bottom line, though, you need to tune the query - and looking at it, I suspect that it will crack under the Dan Tow method.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
April 2004 Iceland http://www.index.is/oracleday.php June 2004 UK - Optimising Oracle Seminar
Thank you Jonathan for your response.
My bad, I should have said... 9204 on Windows 2003, 2cpu box, degree 8. Below is the explain plan for the select statement, and the statement is below.
The only "small" table is cntl_business_day. The rest of them are at least medium sized, and some are large. This is of course out of development. I can't run the statement in production for fear of chewing up all resources. I will take your suggestion for the event and try it in dev.
>From your description of the small table scenario, if all the parallel
slaves are running with the same execution plan, then I could see how
this contention would arise in the index blocks.
So Jonathan are you confirming that chasing down the exact problem and rebuilding is the only real option?
Again thank you
Lisa
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- "The sender believes that this E-Mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking proactive and remedial action about viruses and other defects. The sender's business entity is not liable for any loss or damage arising in any way from this message or its attachments." ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Mar 25 2004 - 14:27:37 CST
 |
 |