RE: Difference in Execution Plan - Same Environment, Same SQL
Date: Wed, 1 May 2013 20:52:46 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90D03BE_at_exmbx06.thus.corp>
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 Received on Wed May 01 2013 - 22:52:46 CEST