Re: Query tuning help
Date: Wed, 6 Feb 2013 07:40:00 -0500
Message-ID: <CAGzKQQfez_xuo_RSi7SZVtWXyfiyZgG-11_kS+6UEcre0jo+2g_at_mail.gmail.com>
You may want to use SQLT (215187.1) so we get to see all the details like stats, indexes, parameters, binds, etc. If you have no license for Tuning nor Diagnostics, just answer N to the last installation parameter, then SQLT will give you every detail associated to this SQL excluding licensed views, still is a lot!
On Wed, Feb 6, 2013 at 4:04 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>wrote:
>
> 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
>
>
>
-- Cheers -- Carlos Sierra http://carlos-sierra.net/ -- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 06 2013 - 13:40:00 CET