Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
From: lsllcm <lsllcm_at_gmail.com>
Date: Tue, 3 Nov 2009 17:46:52 -0800 (PST)
Message-ID: <6370c694-bc82-4775-9d12-e2aedd26b782_at_m33g2000pri.googlegroups.com>
On Nov 4, 2: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.
>
> 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.- Hide quoted text -
>
> - Show quoted text -
Date: Tue, 3 Nov 2009 17:46:52 -0800 (PST)
Message-ID: <6370c694-bc82-4775-9d12-e2aedd26b782_at_m33g2000pri.googlegroups.com>
On Nov 4, 2: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.
>
> 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.- Hide quoted text -
>
> - Show quoted text -
Thank you All at first.
I have done one quick test after set OPTIMIZER_FEATURES_ENABLE to 11.1.0.6 again, the performance is same as the value to 11.2.0.1.
I will double test it again and get 10053 trace file.
Thanks Received on Tue Nov 03 2009 - 19:46:52 CST