Share:|

Remedy 9.x is written in Java. One of the features that was introduced was to avoid sending literal values in Remedy SQL statements as was the case in 8.x and prior versions.

 

Instead the statement is prepared in JDBC with "bind" variables and then sent to the database (this relieves the burden on the Oracle database to replace literals with binds).

 

One drawback to the above fix is addressed in Oracle Doc Id 1451804.1.

 

The drawback/problem arises if you are using the database (Oracle Standard/Enterprise 11.1.0.6 or higher) in Case Insensitive mode, with appropriate Linguistic Indexes in place, and Remedy ends up generating SQL statements with LIKE conditions in them.

 

The Oracle document states that the LIKE condition must be <COLUMN LIKE 'CONSTANT%"> AND NOT <COLUMN LIKE :BIND>. This will cause the optimizer to NOT use a Linguistic Index on the column COLUMN.

 

SYMPTOM

A query using LIKE 'CONSTANT%' with nls_comp=LINGUISTIC against a column with a linguistic index defined on it.  This produces an efficient plan that uses a range scan on the index, but if a bind variable with the same value is used instead (LIKE :BIND, where:BIND = 'CONSTANT%') The query plan will not use a range scan, resulting in poor performance.  Hinting, if tried, does not help.

 

CAUSE

This is a known limitation of using LIKE with NLSSORT-based indexes.

 

Using an NLSSORT-based index with LIKE 'CONSTANT%' requires transforming the predicate into a range predicate based upon on the constant matching string. For instance, col LIKE 'CONSTANT%' when using LINGUISTIC and BINARY_CI is transformed intoNLSSORT("COL",'nls_sort=''BINARY_CI''')>=HEXTORAW('636F6E7374616E7400') AND NLSSORT("COL",'nls_sort=''BINARY_CI''')<HEXTORAW('636F6E7374616E7500').  The values passed to HEXTORAW are derived from the string 'CONSTANT'.

 

This transformation is performed during query parse and requires that the matching pattern be a known constant.  NLSSORT-based functional indexes cannot be used with a LIKE predicate when the matching pattern is based on a bind variable or expression.

 

When cursor_sharing=FORCE (or SIMILAR), all constants are replaced by bind variables before parse, preventing any NLSSORT-based functional indexes being used with LIKE.

 

The above issue is not likely to be fixed by Oracle any time soon.

 

As a result of the above problem BMC has introduced the following check in its code:

(a) Is the database Oracle and is it Case Insensitive?

(b) Are there any LIKE conditions in the SQL?

 

THE ABOVE FIX IS AVAILABLE IN AR 9.1 SP4 PATCH 2 HOTFIX.

 

If the above are TRUE then the SQL will be send to the database AS IS (no replacing with binds in JDBC) and the Oracle database, where cursor_sharing = EXACT is necessary, will process the SQL and come up with an optimal execution plan.