IBM Support

Set CURSOR_SHARING=FORCE or SIMILAR for best performance

Flash (Alert)


Abstract

Maximo does not use bind variables for user specified where clauses, which are used in most queries. You should set the Oracle database parameter CURSOR_SHARING to FORCE or SIMILAR to improve 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.

Cross reference information
Segment Product Component Platform Version Edition
Systems and Asset Management Tivoli Asset Management for IT All
Systems and Asset Management IBM Maximo Asset Management Essentials All

Historical Number

M05002

Product Alias/Synonym

MAXIMO

Document information

More support for: Maximo Asset Management

Software version: 6.2.1, 6.2.2, 6.2.3, 6.2.4, 6.2.5, 6.2.6, 6.2.7, 6.2.8, 7.1, 7.1.1, 7.5, 7.6, Version Independent

Operating system(s): Platform Independent

Software edition: All Editions

Reference #: 1262959

Modified date: 13 July 2009


Translate this page: