RE: Query tuning help
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 6 Feb 2013 09:04:06 +0000
Apart from Niall's suggestion - which will probably show us that the estimate
of one row in WFCTOTAL per WTKEMPLOYEE is far lower than it ought to be, the OP
ought to deal with the "Old Plan Table" note so that we can see the predicate
section of the execution plan. After that, an examination of the stats for
notpaidsw would be appropriate, not to mention checking the available indexes
on WFCTOTAL, and most specifically giving us the definition of the index that
Oracle used.
Just of the hell of it, I'll guess that there's a histogram on NOTPAIDSW, but
the value 0 doesn't appear in the histogram because the sample used was small
and the number of occurrences is low (or maybe it appears only as a very small
entry). Then I'll guess that the index used started with notpaidsw and had one
(or possibly both) of the date columns in it. If that's a good guess then it
wouldn't be surprising to see the plan show.
Or maybe the OP has set optimizer_mode = first_rows_1
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Ric Van Dyke [ric.van.dyke@xxxxxxxxxx]
Sent: 06 February 2013 03:19
To: lambu999@xxxxxxxxx; oracle-l
Subject: RE: Query tuning help
Stats off even one percent (or less) can make a difference, but might
not.
It likely would be easier to figure out where to start with stat lines
from a 10046 trace or a query on the V$SQL_PLAN_STATISTICS_ALL view
after a run so you can see at which line most of the work and time is
going. The optimizer certainly thinks it's going to deal with few rows
for much of the plan which I'm thinking is why it's choosing all those
nested loops, it could be that there are more rows then it thinks and
Hash or Sort Merge would be better. It would likely be very revealing
to see the actual rows for each line in the plan.--
//www.freelists.org/webpage/oracle-l
Other related posts:
