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

From: Ross Lafferty <ross.lafferty_at_gmail.com>
Date: Wed, 1 May 2013 17:18:25 -0400
Message-Id: <262BA8A6-8622-43EB-BF62-650078F45528_at_gmail.com>

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.

Thanks again,

-- 
B. Ross Lafferty
==============
M: 412-608-7505
E: ross.lafferty_at_gmail.com
==============



On May 1, 2013, at 4:52 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:



> 

> Use dbms_xplan.display_cursor() with the 'peeked_binds' option to find out the bind values used on the two different compute nodes where the queries were optimised.

> 

> Most obvious guess - there are histograms on some of the predicate columns and the query had been executed with different input values on one node before the bad run took place. Possible solution if this is the case - get rid of the histograms if that gives you good enough, consistent, performance rather than intermittent disasters.

> 

> 

> Regards

> Jonathan Lewis

> 

> 

> ________________________________________

> From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Ross Lafferty [ross.lafferty_at_gmail.com]

> Sent: 01 May 2013 21:15

> To: oracle-l_at_freelists.org

> Subject: Difference in Execution Plan - Same Environment,  Same SQL

> 

> Running into interesting behavior with execution plans in 11.2.0.3 (running on Exadata x3 platform).   The below query was executed at roughly the same time, against the same environment, originating from different clients (different machines all together).  The first one listed below, using hash joins, returns results in ~1 minute.  The second one listed below, using nested loops, doesn’t return, even after hours.  No alter sessions have been performed.  OPTIMIZER_MODE for the environment is ALL_ROWS (which should gravitate towards hash joins).

> 

> My question is, why the varying execution path for the same query, same environment, being run at the same time - and any way to correct it?

> Thanks in advance!

> ----

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

> 

> 



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

Received on Wed May 01 2013 - 23:18:25 CEST