Support Article
Oracle query is not using an index
SA-7105
Summary
For user's production application, Oracle decided to do a full table scan and not a lookup via an index, which is impacting application performance. They have 2 instances of this occurring.
They are:
Table: CPMHC_WORK column INQUIRYCASEKEY;
Table: HMK$CSD_INDEX_INTERACTIONS column pxInsIndexedKey;
At times SQL statements choose to do a table scan instead of using an available index. It makes this choice because it uses something called a histogram to determine the uniqueness of the values in the index. For example, if most on an index was from Pennsylvania and that was the index value, it does no good to use it because it doesn't make it unique enough. The problem is that this histogram only looks at the first 32 bytes of the column to determine uniqueness. User is using Oracle 11.2.0.3. User would like to know if there is anything that they can do to fix this issue.
Database operation took more than the threshold of 500 ms: 5,525 ms SQL: SELECT INQUIRYCASEKEY AS "InquiryCaseKey" , PYSTATUSWORK AS "pyStatusWork" , PYID AS "pyID", PZINSKEY as "pxInsHandle" from cpmhc_work WHERE pxObjClass LIKE ? AND ( ( INQUIRYCASEKEY = ? ) AND ( PYSTATUSWORK NOT LIKE ? ) )
Error Messages
None
Steps to Reproduce
n/a
Root Cause
The root cause of this problem is in a third-party product (Oracle database server) integrated with PRPC.
If your PRPC application uses an Oracle database, you may experience poor behavior with the above SQL query. The root cause of the issue is related to a character-length limitation in the Oracle database: (On Oracle 11g), queries which use a key where the first 32 characters are identical will not use a database index defined on that key.
PRPC provides a highly flexible data model for building objects. Classes follow an inheritance model which allows many levels in a hierarchy. PRPC class names can be 96 characters in length. Due to inheritance, it is possible that a large number of classes in an application start with an identical set of characters. Class names are also often used as the leading key in the definition of a work item. This can lead to the first 32 characters of a key column (pzEntryKey) to be identical for many database entries.
Resolution
This issue is resolved by making the following change to Oracle database server –
Oracle uses histograms (statistics about distinct values in columns) to determine how unique, and therefore how "selective", the data in a given column is. The histograms are calculated on the first 32 characters of a column. If a column has all its values identical in the first 32 characters, the histogram distribution for this column is set to 1, which means "non-selective".
When determining the query plan for a SQL statement that uses this column in a WHERE clause, the database optimizer will choose not to select a perfectly valid index due to the histogram reporting it as non-selective.
The solution for this issue on Oracle 11g is to set the statistics gathering process to not gather histogram statistics on tables that show this behavior. You can disable histogram stats on a table by table with the following command:
SQL> exec dbms_stats.set_table_prefs(ownname=>'PRPC_DB_Schema_Name',-
tabname=>'cpmhc_work',-
pname=>'METHOD_OPT',-
pvalue => 'FOR ALL COLUMNS SIZE 1' );
Note: In this case however, be sure to include this statement in a job that is scheduled to run after regularly scheduled statistics have been run, as scheduled statistics will override the settings in the SQL statement.
A DBA may choose to use the SQL Plan management feature of Oracle Enterprise Manager to set the SQL plan for a statement that is affected by this issue.
Oracle provides extensive documentation in their Metalink site. DBA's working with an Oracle database has access to the site. In particular, these Metalink articles are relevant:
Best Practices for automatic statistics collection (ID 377152.1)
Recommendations for Gathering Optimizer Statistics on 10g (ID 605439.1)
Oracle bug number 6934892 discusses the issue covered in this article
Published January 31, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.