Shawn_Moore

CA Tuesday Tip: Lesser Known Oracle Settings to Improve Performance

Discussion created by Shawn_Moore Employee on Apr 4, 2011
Latest reply on Apr 5, 2011 by Chris_Hackett
CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 4/04/2011

The 12.10 Installation guide notes two Oracle parameters that can improve performance. I have included the text from the guide, plus some additional elaboration.

The following parameters have been shown to have a positive performance gain when running CA Clarity PPM on Oracle 10g and 11g. These parameters are meant to be optional tuning parameters and should be used as needed to achieve adequate database performance.

b_tree_bitmap_plans

Performance problems can occur, when the Oracle's Cost Based Optimizer choses a bitmap index path when there was no bitmap index.

When monitoring execution plans, you may see plans similar to:

0 SELECT STATEMENT
1 0 BITMAP CONVERSION (TO ROWIDS)
2 1 BITMAP AND
3 2 BITMAP CONVERSION (FROM ROWIDS)
4 3 INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE)
5 2 BITMAP CONVERSION (FROM ROWIDS)
6 5 INDEX (RANGE SCAN) OF 'I2' (NON-UNIQUE)

Changing this setting to FALSE generates faster and more efficient execution plan.
i.e.

0 SELECT STATEMENT
1 0 AND-EQUAL
2 1 INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE)
3 1 INDEX (RANGE SCAN) OF 'I2' (NON-UNIQUE)

To disable the use of bitmap index paths in plans, you can set the following Oracle init setting.

btree_bitmap_plans=FALSE

This can be done by running the following statement, followed by a database restart.

ALTER SYSTEM SET "_b_tree_bitmap_plans"=false SCOPE=SPFILE;

For more information please refer to Metalink Bug Id: 8318459.


Cursor Sharing=force

Eventhough Clarity uses bind variables extensively, there are still some queries that contain literal values.

When CURSOR_SHARING is used set to FORCE, Oracle first checks the shared pool to see if there is an identical statement in the shared pool. If an identical statement is not found, then Oracle searches for a similar statement in the shared pool. If the similar statement is there, then the parse checks continue to verify the executable form of the cursor can be used. If the statement is not there, then a hard parse is necessary to generate the executable form of the statement.

By setting this to FORCE, Oracle is essentially rewriting the executed queries to and replacing any literals with bind values. If the queries executed are the same (though values are different), only one shared query is created and is shareable and used for all sessions. This turns a hard parse into a soft parse. A soft parse will lock (or latch) the shared pool less than a hard parse and, thus, provide a performance gain. Watch the Oracle AWR for excessive query parsing to determine if this is a setting you should attempt to use.

To set Cursor sharing, you can set the following Oracle init setting.

CURSOR_SHARING=FORCE


-Shawn Moore

ps: no unintended smiley's today! :grin:

Outcomes