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

From: Carlos Sierra <carlos.sierra.usa_at_gmail.com>
Date: Tue, 24 Jul 2012 07:12:11 -0400
Message-ID: <CAGzKQQdZUnM+RDXV3_ZFMPtsqFYK4pOdPNjpXJd1X1GGNGB=Tw_at_mail.gmail.com>

You may want to install SQLT (215187.1) in both environments and use XTRACT method. Then send me output for diagnostics. I would use SQLT XPLORE in your SQLT, which basically helps to narrow the optimizer change between 11.2.0.2 and 11.2.0.3 which affects your plan so badly. On Tue, Jul 24, 2012 at 2:05 AM, Greg Rahn <greg_at_structureddata.org> wrote:

> 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
>
>
>

-- 
Cheers -- Carlos Sierra
http://carlos-sierra.net/


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

Received on Tue Jul 24 2012 - 06:12:11 CDT