Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
Date: Sun, 8 Nov 2009 12:29:54 -0800 (PST)
Message-ID: <a68d01c8-648c-428b-a9b8-d15c7c851844_at_a32g2000yqm.googlegroups.com>
On Nov 8, 5:20 pm, lsllcm <lsl..._at_gmail.com> wrote:
> Below sql still runs slow regardless optimzer mode is FIRST_ROWS_100
> and ALL_ROWS
First, could you please always include the "Predicate Information" section below the plan, it often contains very important information, and without it it's sometimes really hard to understand what is going. The 11.2 plan seems to come from the end of the 10053 trace file and not from the DBMS_XPLAN output, by the way.
Second, since 11.2 again attempts to avoid the SORT operation, and comes up with a plan having a cost of 10K, you might again have the issue that 11.2 overestimates the cardinality and therefore thinks the sort is much more costly than it effectively is.
You can again search for that "First K Rows: K = 100.00" in the 10053 trace file to get an impression of the estimated ALL_ROWS cardinality, or run the statement with ALL_ROWS mode and without the outer "ROWNUM < 101" query to see the estimated ALL_ROWS cardinality.
The mode ALL_ROWS or FIRST_ROWS_100 OPTIMIZER_MODE doesn't matter here
because the "ROWNUM < 101" attribute will enable the FIRST_ROWS_n mode
anyway regardless of the OPTIMIZER_MODE used in the session
(controlled by the underscore parameter
"_optimizer_rownum_pred_based_fkr" which defaults to true).
I think the bottom line is you need to understand why 11.2 overestimates the cardinality - and you should get try to confirm that this is different from your 10.2 and 11.1 systems, which means that I expect those to come up with a cardinality estimate closer to reality.
This may not be an issue about the general optimizer settings, but more about the differing object statistics or different calculations performed by 11.2 using these object statistics.
Once the cardinality estimates are closer to the actual ones, 11.2 very likely will come up with better performing plans.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
Received on Sun Nov 08 2009 - 14:29:54 CST