Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

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' );
 
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.
Suggest Edit

Published January 31, 2016 - Updated October 8, 2020

Did you find this content helpful? Yes No

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us