RE: Difference in Execution Plan - Same Environment, Same SQL
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 May 2013 20:52:46 +0000
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, 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!
----
//www.freelists.org/webpage/oracle-l
Other related posts:
