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