Re: Difference in Execution Plan - Same Environment, Same SQL

From: Carlos Sierra <carlos.sierra.usa_at_gmail.com>
Date: Thu, 2 May 2013 08:08:55 -0400
Message-ID: <CAGzKQQf0fpWEDimrkyH+8SFxeMyPedwWrgEXU--NF7712bQ0NQ_at_mail.gmail.com>

Ross,
If you use SQLT XTRACT on it, please send me the output and I will review it.
Cheers -- Carlos

On Thu, May 2, 2013 at 1:43 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>wrote:

>
> Ross,
>
> Histograms can introduce significant problems of stability, particularly
> frequency histograms created on "status" columns that track small volumes
> through a series of status changes until they reach a few common "end"
> statuses. The critical issue relates mainly to status values that might not
> be present when the histogram is collected, or may be present in such small
> volumes that the sample set doesn't catch them.
>
> Different plans on different nodes could result as a consequence of shifts
> in the histogram content - but the act of collecting the histogram should
> invalidate any existing cursor over the next few hours, so plans should
> change from good to bad (or vice versa) over all nodes over a few hours,
> and stay in the new state until the histogram is next collected. This
> pattern doesn't seem to be a very good match for the way you've described
> the problem.
>
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com/all-postings
>
> Author: Oracle Core (Apress 2011)
> http://www.apress.com/9781430239543
>
> ----- Original Message -----
> From: "Ross Lafferty" <ross.lafferty_at_gmail.com>
> To: <jonathan_at_jlcomp.demon.co.uk>
> Cc: <oracle-l_at_freelists.org>
> Sent: Wednesday, May 01, 2013 10:18 PM
> Subject: Re: Difference in Execution Plan - Same Environment, Same SQL
>
>
> Jonathan -
>
> Removing the histogram on the ODS_CHANGE_FLAG seems to make it more stable.
> I say more, because I'll continue to run various tests to see if it
> provides the same consistent performance.
>
> For this specific query, its generated from an ETL tool and the parameters
> are always static. Would it be possible that if the distribution of data
> within the histogram buckets shifts enough it would drive the nested loops
> vs. hash joins? I ask because prior to dropping the histogram, it was hit
> or miss, some sessions had the good plan, some bad - and the sessions were
> distributed between each of the 2 DB nodes.
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Cheers -- Carlos Sierra
http://carlos-sierra.net/


--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 02 2013 - 14:08:55 CEST