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

From: Charles Hooper <>
Date: Tue, 3 Nov 2009 10:11:41 -0800 (PST)
Message-ID: <>

On Nov 3, 12:44 pm, joel garry <> wrote:
> On Nov 3, 8:22 am, Charles Hooper <> wrote:
> > On Nov 3, 10:29 am, lsllcm <> 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
> 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, 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 in Oracle 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, add a couple extra spaces in the SQL statement, and try your test again. Is the performance the same as it was on and

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Nov 03 2009 - 12:11:41 CST

Original text of this message