Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
Date: Tue, 3 Nov 2009 10:11:41 -0800 (PST)
Message-ID: <c0adb157-2a16-4645-92ff-64be67452af9_at_b15g2000yqd.googlegroups.com>
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.
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?
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Tue Nov 03 2009 - 12:11:41 CST