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
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.