Skip navigation
Share This:

BMC Support or R&D might have asked for a "slow" SQL's Execution Plan and it is possible that the SQL might have aged out of Oracle's Library Cache or MSSQL's Procedure cache and hence no Execution Plan is available.


That would then necessitate generating an Estimated Execution Plan ("Estimated Plan" in MSSQL's SQL Management Studio or "Explain Plan" in Oracle) on the SQL extracted from AR log file.


The SQL does need to be complete and not truncated in order for an Estimated Plan to be generated.


To get the complete SQL as it was sent to the database put the following AR parameter in your ar.cfg/ar.conf file(s) and restart the AR Server(s).


Enable-Unlimited-Log-Line-Length: T


Remedy documentation -


Some of you already know this so it may be old hat/news for you! :-)

Share This:

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 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.



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.



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 into NLSSORT("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?




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.

Filter Blog

By date: