Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.

From: joel garry <joel-garry_at_home.com>
Date: Tue, 3 Nov 2009 16:38:47 -0800 (PST)
Message-ID: <bfcfacff-5aaf-471f-a62c-50fbd8d4b9a3_at_f1g2000prf.googlegroups.com>

On Nov 3, 10:11 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Nov 3, 12:44 pm, joel garry <joel-ga..._at_home.com> wrote:
>
>
>
> > On Nov 3, 8:22 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>
> > > On Nov 3, 10:29 am, lsllcm <lsl..._at_gmail.com> wrote:
>
> > > Note also that it is possible that the object statistics and optimizer
> > > parameters (FIRST_ROWS_n, for example) differ between the Oracle
> > > versions.
>
> > > David's suggestion of a 10053 trace is a good one - just keep in mind
> > > that such traces only appear during a hard parse.
>
> > A bit of a stretch, but consider this:  rownum is assigned after the
> > predicate is processed, but before sorting is done.  "Bug 6438892 :
> > Suboptimal plan with ROWNUM predicate and NESTED LOOPS." is in the
> > 11.1.0.6 patch set, bug not published.  So maybe there is some
> > mysterious optimizer bug here, set off by the order by even though
> > rownum is not originally a predicate.  Try patching beyond the base
> > release and see if the problem is still there.
>
> > jg
> > --
>
> The OP is experiencing problems in 11.2.0.1, which does not exhibit
> the ROWNUM bug that you mentioned, based on my testing.  You might be
> suggesting - what if the fix of that bug caused another bug?
> Interesting, quite possible.

Uh yeah, that's the ticket. (OK, I admit, somewhere along the line I bugeyed the 11.2.0.1 to 11.1...)

>
> I noticed the large number of NESTED LOOPS in his posted plan also,
> which is one of the reasons why I mentioned FIRST_ROWS_n
> (OPTIMIZER_MODE).  It might even be the case that the OPTIMIZER_MODE
> is set to FIRST_ROWS (or maybe even CHOOSE), rather than FIRST_ROWS_1,
> FIRST_ROWS_10, FIRST_ROWS_100, ALL_ROWS, etc.
>
> lsllcm, please try setting OPTIMIZER_FEATURES_ENABLE to 11.1.0.6 in
> Oracle 11.2.0.1 and re-execute the SQL statement - note that this will
> force a hard parse.  How does the performance compare?  If the
> performance is better, set OPTIMIZER_FEATURES_ENABLE to 11.2.0.1, add
> a couple extra spaces in the SQL statement, and try your test again.
> Is the performance the same as it was on 11.1.0.6 and 10.2.0.4?

That may be informative.

jg

--
_at_home.com is bogus.
http://users.rcn.com/eslowry/inexcus.htm

Received on Tue Nov 03 2009 - 18:38:47 CST