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


100% found this useful

Have a question? Get answers now.

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