Re: Completely insane execution plan with 11.2.0.3 but not with 11.2.0.2

1. export statistics from 11.2.0.2 and import them to the 11.2.0.3 db
2. set init.ora parameters identical
3. validate that the issue still exists
4. it yes, then create a test case builder archive of the with OFE=11.2.0.3 (default) and then again with 11.2.0.2 see [1]
5. upload both archives to Oracle support. If you want, load both optimizer trace files into a visual diff tool and see where they diverge.

[1] https://raw.github.com/grahn/oracle_scripts/master/tcb.sh

On Mon, Jul 23, 2012 at 8:01 AM, Thomas Kellerer <thomas.kellerer_at_mgm-tp.com
> wrote:

> Hello,
>
> (I have also posted this to forums.oracle.com in case this sounds
> familiar to someone).
>
> we have a production server that is running Oracle 11.2.0.2 on Windows and
> a Development/Test server
> that is running 11.2.0.3 on Linux.
>
> We have one statement that selects from a rather large view (that
> statement does a simple select * from viewname).
> The execution plan shows a cost of ~20k and a row estimate of ~800
>
> On our test server which has similar data the optimizer completely
> mis-estimating the cost for one join which
> results in totally insane estimates higher up the chain. All other row
> estimates are pretty close to reality.
>
> After testing several things we discovered that setting
> optimizer_features_enable = '11.2.0.2' things are fine on our test server.
>
> --

Regards,
Greg Rahn | blog <http://bit.ly/u9N0i8> | twitter <http://bit.ly/v733dJ> |  linkedin <http://linkd.in/gregrahn>

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