Re: Difference in Execution Plan - Same Environment, Same SQL
- From: Ross Lafferty <ross.lafferty@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Wed, 1 May 2013 17:18:25 -0400
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@xxxxxxxxx
==============
On May 1, 2013, at 4:52 PM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
> of Ross Lafferty [ross.lafferty@xxxxxxxxx]
> Sent: 01 May 2013 21:15
> To: oracle-l@xxxxxxxxxxxxx
> 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, doesnt 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!
> ----
> //www.freelists.org/webpage/oracle-l
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
