Support Article
sppr_sys_reservequeueitem_b stored procedure taking more time
SA-24036
Summary
A systems administrator observes gradually declining system performance during the work day. An analysis of the Pega ALERT log reveals large numbers of alerts indicating poor database performance.
Error Messages
No error messages are displayed on screen; no applicable error messages were found in the Pega logfile.
However, the ALERT log is rife with the PEGA0005 ALERT message:
Database operation took more than the threshold of 500 ms: 650,074 ms SQL: {call PRPC71_xxx_db.sppr_sys_reservequeueitem_b ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }
Steps to Reproduce
Unknown. No specific steps were found to reproduce this issue.
Root Cause
The root cause is a known defect in the Pega standard stored procedure sppr_sys_reservequeueitem_b. Specifically, the ORDER BY clause of the standard SQL contains a known string that causes the database engine performance to grind to a halt.
Resolution
This is an ORACLE environment. To resolve this issue, alter the ORDER BY clause of the standard stored procedure sppr_sys_reservequeueitem_b:
FROM:
orderby := ' order by pzInsKey ASC';
TO:
orderby := ' order by pyMinimumDateTimeForProcessing ASC';
Published July 13, 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.