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

Hi Ross,

Quick answer is create a baseline on the one you like. Why it flipped takes a little more work. CURSOR_SHARING is probably not your friend. And having a TO_NUMBER interjected on one side of a join is also a bad sign.

Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com
twitter: https://twitter.com/KerryOracleGuy

On May 1, 2013, at 3:15 PM, Ross Lafferty wrote:

> 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!
> --
> B. Ross Lafferty
> ==============
> E: ross.lafferty_at_gmail.com
> ==============
>
>
> --
> B. Ross Lafferty
> ==============
> M: 412-608-7505
> E: ross.lafferty_at_gmail.com
> ==============
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

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