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

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 Received on Thu May 02 2013 - 07:43:01 CEST