RE: Query tuning help
Date: Wed, 6 Feb 2013 09:04:06 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90C5743_at_exmbx06.thus.corp>
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_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Ric Van Dyke [ric.van.dyke_at_hotsos.com] Sent: 06 February 2013 03:19
To: lambu999_at_gmail.com; 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.-- http://www.freelists.org/webpage/oracle-l Received on Wed Feb 06 2013 - 10:04:06 CET