Re: performance issue after upgrade to oracle 184.108.40.206 linux 32 bit.
Date: Tue, 3 Nov 2009 10:11:41 -0800 (PST)
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
> 220.127.116.11 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.
The OP is experiencing problems in 18.104.22.168, 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 22.214.171.124 in Oracle 126.96.36.199 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 188.8.131.52, add a couple extra spaces in the SQL statement, and try your test again. Is the performance the same as it was on 184.108.40.206 and 10.2.0.4?
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Nov 03 2009 - 12:11:41 CST