Support Article
RequestorLockException & similar-characters issue in Oracle SQL
SA-2315
Summary
User has been receiving RequestorLockExceptions frequently, higher response time and has seen some issues in the logs related to DB Bytes Read errors.
Error Messages
Long Requestor Time alerts were observed:
Database operation took more than the threshold of 200 ms: 2,800 ms SQL: select pzRuleName as "pzRuleName", pzRuleType as "pzRuleType", pzPrimaryPageClassName as "pzPrimaryPageClassName", pzAspect as "pzAspect", pzAppHash as "pzAppHash", pzImplementationKey as "pzImplementationKey", pzCreationElapseTime as "pzCreationElapseTime", pxCreateDateTime as "pxCreateDateTime", pzEntrySource as "pzEntrySource", pzInsKey as "pzInsKey", pzAppName as "pzAppName", pzAppVersion as "pzAppVersion", pzExpirationTime as "pzExpirationTime" from pr_sys_appcache_shortcut where (pzEntryKey = ? or pzEntryKey = ?) and pzCacheConfigID = ?
Steps to Reproduce
Restart PRPC and permit users to login/work. System gradually slows to a halt.
Root Cause
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.
Resolution
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',-
tabname=>'pr_index_reference',-
pname=>’METHOD_OPT’,-
pvalue => 'FOR ALL COLUMNS SIZE 1' );
For Oracle 10g you can achieve similar results by using the SQL statement below:
SQL> exec dbms_stats.gather_table_stats(ownname=>'PRPC',-
tabname=>'pr_index_reference',-
estimate_percent => 100,-
cascade=>true,-
method_opt => 'FOR ALL COLUMNS SIZE 1' );
tabname=>'pr_index_reference',-
pname=>’METHOD_OPT’,-
pvalue => 'FOR ALL COLUMNS SIZE 1' );
For Oracle 10g you can achieve similar results by using the SQL statement below:
SQL> exec dbms_stats.gather_table_stats(ownname=>'PRPC',-
tabname=>'pr_index_reference',-
estimate_percent => 100,-
cascade=>true,-
method_opt => 'FOR ALL COLUMNS SIZE 1' );
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.
For more details, refer PDN article - Troubleshooting: How to solve a similar-characters issue in a SQL query to an Oracle database
Also, provided HFix-9723 to address the issue with rule resolution not picking the latest rule without clearing the app cache.
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.