Re: Completely insane execution plan with 11.2.0.3 but not with 11.2.0.2
- From: Thomas Kellerer <thomas.kellerer@xxxxxxxxxx>
- Date: Tue, 24 Jul 2012 13:26:18 +0200
Hi Greg,
thanks. Will do that but it will take a while because we don't have direct
access to production.
(And the tuning/diagnostic packs are not licensed on either machine, so I
cannot use dbms_sqltune)
> If you want, load both optimizer trace files into a visual diff tool and see
> where they diverge.
It's bascially the same part where the regular execution plans diverge.
The bad plan is using a nested loop whereas the good plan is using a hash join.
As large parts of the plan are moved around due to the different order of
execution, it's hard to
spot the differences in a diff output (even a visual one).
Regards
Thomas
Greg Rahn, 24.07.2012 08:05:
> 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@xxxxxxxxxx
> <mailto:thomas.kellerer@xxxxxxxxxx>> wrote:
>
> Hello,
>
> (I have also posted this to forums.oracle.com <http://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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
