RE: cache buffers chain latch contention
- From: "Koivu, Lisa" <Lisa.Koivu@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 25 Mar 2004 15:02:37 -0500
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@xxxxxxxxxxxxxxxxxx]
Sent: Thursday, March 25, 2004 1:56 PM
To: oracle-l@xxxxxxxxxxxxx
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
----- Original Message -----
From: "Koivu, Lisa" <Lisa.Koivu@xxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, March 25, 2004 6:23 PM
Subject: RE: cache buffers chain latch contention
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
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
