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

PEGA0005 alerts for sppr_sys_reservequeueitem_b

SA-10578

Summary



You see a large number (several hundred) of PEGA0005 alerts in the PegaRULES-ALERT logs with the below message:

Database operation took more than the threshold of 500 ms: 45,107 ms SQL: {call sppr_sys_reservequeueitem_b ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }


Error Messages



Database operation took more than the threshold of 500 ms: 45,107 ms SQL: {call sppr_sys_reservequeueitem_b ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }

Steps to Reproduce



Not Applicable - issue occurs during normal operation.

Root Cause



Due to the way that Oracle determines whether or not to use an index, the index was not being used to run this query and instead a full table scan was being performed.  This occured because pzInsKey is constructed with the class structure as the initial part of the index string, and when all items in a table have similar class inheritance, the first 32 characters can potentially be identical and oracle will incorrectly determine that the column is non-selective and will not opt to use an index.

https://pdn.pega.com/pegarules-database/troubleshooting-how-to-solve-a-similar-characters-issue-in-a-sql-query-to-an-oracle-database

Resolution



To resolve the issue where oracle is incorrectly performing a full table scan instead of using a valid index, take the following steps:

1) Delete the Oracle Histogram Data on table.property reference.

The example SQL would be:

BEGIN
dbms_stats.delete_column_stats
(
ownname=>'YOUR_SCHEMA_NAME',
tabname=>'PR_SYS_WORKINDEXER_QUEUE',
colname=>'PZINSKEY',
col_stat_type=>'HISTOGRAM'
);
END;

2) Configure the database instance to NOT compute Histogram data for this column during future COMPUTE/ESTIMATE table statistics.

The example SQL would be:

BEGIN
dbms_stats.set_table_prefs
(
ownname=>'YOUR_SCHEMA_NAME',
tabname=>' PR_SYS_WORKINDEXER_QUEUE ',
pname=>'METHOD_OPT',
pvalue => 'FOR COLUMNS SIZE 1 PZINSKEY'
);
END;

3) Trigger a regeneration of INDEX data on this table.

The example SQL would be:

BEGIN
dbms_stats.gather_table_stats
(
ownname=>'YOUR_SCHEMA_NAME',
tabname=>' PR_SYS_WORKINDEXER_QUEUE ',
);
END;

4) Tune the Oracle Optimizer such that it will provide a greater emphasis to existing INDEXes and less emphasis to FULL TABLE SCANS.

Please complete this change to the INIT*.ora file for the database instance:

optimizer_index_cost_adj = 20

Published June 12, 2015 - Updated October 8, 2020

Was this useful?

0% found this useful

Have a question? Get answers now.

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

Did you find this content helpful?

Want to help us improve this content?

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