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

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 2 May 2013 06:43:01 +0100


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.


Jonathan Lewis

Author: Oracle Core (Apress 2011)

----- 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.


Other related posts: