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

From: Randolf Geist <mahrah_at_web.de>
Date: Wed, 4 Nov 2009 08:11:21 -0800 (PST)
Message-ID: <3c6ffeed-2c12-42dc-b4eb-2d6155aa0cc2_at_s31g2000yqs.googlegroups.com>

On Nov 3, 4:29 pm, lsllcm <lsl..._at_gmail.com> wrote:
> I have one performance issue after upgrade to oracle 11.2.0.1 linux 32
> bit.
>
> The optimizer always choose index of "order by column", not use index
> of "where clause columns". Any comments are appreciated.

This might be caused by some code change / bug introduced in 11.2.0.1, but since Charles already asked you to run the same with the optimizer features reverted to 11.1.0.6 and you confirmed that the problem still persists with this setting, there are some other possibilities:

1. Looking at the 10053 optimizer trace file, we can see that the optimizer actually estimates that this statement is going to generate millions of rows, so this is already way off from the 0 rows that it actually produces. So the starting point of the whole calculation is already totally wrong, since otherwise the optimizer would recognize that using the FIRST_ROWS_n mode won't change the outcome of the query. Looking at the 3 consistent gets with the good plan, it looks like the whole execution already stops after the first index / table access to SD, otherwise we probably would see more consistent gets. So the runtime engine can take a shortcut here with the NESTED LOOP joins if one the driving row sources generates 0 rows.
2. You're using the FIRST_ROWS_100 optimizer mode together with very aggressive OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING settings which will lower cost of index access paths dramatically, in particular when performing NESTED LOOP joins. This might lead to multiple optimizer choices with similar costs, due to the lowering of the cost. Using the FIRST_ROWS_n modes will introduce additional recosting steps by the optimizer for ORDER BY access. This is very likely the reason why you don't see the effect when omitting the ORDER BY.
3. You say that you've migrated to 11.2.0.1, but we don't know if the remaining environment in terms of optimizer settings and object statistics is the same as in the 10.2 and 11.1 databases you've used to show the "correct" plan chosen. May be the 11.2 database has regathered table/index statistics and with those modified statistics the 11.1 and 10.2 database might come to the same "bad" execution plan
4. As pointed out by Charles already, you need to be very careful with testing when bind variables are involved, in order to make sure that you don't share an execution plan from previous executions available in the Shared Pool. This is obviously a general issue, and only partially addressed by the new Adaptive Cursor Sharing introduced with 11.1

What you could try:

1. What cost / plan do you get if you request to use the index SETDETAILS_SETID_IX?

SELECT /*+ INDEX(SD, SETDETAILS_SETID_IX) */ ... 2. What plan do you get in 11.2 if you're setting OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING to default values:

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 100; ALTER SESSION SET OPTIMIZER_INDEX_CACHING = 0; 3. What plan do you get in your 10.2 and 11.1 databases if you use the same environment as in 11.2? In particular the optimizer settings and object statistics should be the same. You can either try to export / import the object statistics manually or (I'm not sure if it requires an additional license cost, but if your license allows for it) you can very simply create a test case using the SQL Test Case Builder in 11.2 and try to import this into the 11.1 and 10.2 databases to create a similar environment, however I'm not sure if this will work due to potential compatibility issues in the lower version environments.

Use the DBMS_SQLDIAG.EXPORT_SQL_TESTCASE / IMPORT_SQL_TESTCASE to do so.

See the manuals or e.g. http://optimizermagic.blogspot.com/2008/03/oracle-support-keeps-closing-my-tar.html for more information.

4. A general question: Is your application actually using the FIRST_ROWS_100 optimizer mode properly? Which means, do you always fetch only a few rows from a larger result set? Does your application show only the top N ordered results of a search result in "pagination" style like Google search? Only then the FIRST_ROWS_100 mode is reasonable, otherwise you should actually use the ALL_ROWS mode. Given your aggressive OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING the optimizer would still favor index access / nested loop operations quite a lot even in ALL_ROWS mode.

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 Wed Nov 04 2009 - 10:11:21 CST