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';