Re: Difference in Execution Plan - Same Environment, Same SQL
- From: Carlos Sierra <carlos.sierra.usa@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Thu, 2 May 2013 08:08:55 -0400
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@xxxxxxxxxxxxxxxxxx>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@xxxxxxxxx>
> To: <jonathan@xxxxxxxxxxxxxxxxxx>
> Cc: <oracle-l@xxxxxxxxxxxxx>
> 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.
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Cheers -- Carlos Sierra
http://carlos-sierra.net/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
