Set CURSOR_SHARING=FORCE or SIMILAR for best performance

Content

Most select statements are dynamically generated from user input at run time, so they contain literal values in the WHERE clause conditions. This results in large numbers of nearly identical statements with separate parse trees in Oracle's library cache, which can slow performance and cause latch problems.

The kernel parameter cursor_sharing defaults to EXACT, but can be set to FORCE or SIMILAR in order to have the database convert literals to bind variables before parsing the statement.

FORCE - All conditions are converted to bind variables (WONUM='1234' and WONUM LIKE '123%' are both converted).

SIMILAR - Only equivalence conditions are converted to bind variables (WONUM='1234' is converted but WONUM LIKE '123%' is not).

You should at least set cursor_sharing to SIMILAR, but you will usually see much greater performance improvement by using FORCE. (On most Maximo systems, a large portion of user-generated SQL contains LIKE conditions which will benefit from FORCE.)

To make this change:

1. Connect to the database as SYSTEM (or other userid with DBA privilege) with SQLPLUS.
2. Execute the following command:

ALTER SYSTEM SET CURSOR_SHARING=FORCE SCOPE=BOTH;

This will set the value immediately and also make it permanent across shutdown/startup. You must also shutdown and restart the application server or cluster for this to fully take effect. The ALTER command will only affect database connections made after it is executed. You can make this change ahead of a scheduled shutdown for some immediate benefit, and subsequently realize the full benefit after the shutdown.