SA-22994

Summary

A systems administrator observes very poor system performance.  An analysis of PegaAES logs reveals that that database performance is very poor, and database is an Oracle 11 implementation.

Error Messages

From the PegaAES logs, the customer reports that the database performance problems are the result of a poorly performing stored procedure - sppr_sys_reservequeueitem_b.

Steps to Reproduce

Unknown.  No specific steps were found to trigger this problem. The problem is continuously occurring.

Root Cause

This is a previously identified performance problem in this stored procedure. The root cause is the ORDERBY clause in the SQL string process.

Resolution

To resolve this issue, the standard stored procedure sppr_sys_reservequeueitem_b is modified.  The modification is to alter the ORDERBY clause:

    FROM:
    orderby := ' order by pzInsKey ASC';

    TO:
    orderby := ' order by pyMinimumDateTimeForProcessing ASC';

Also, the environment employs the Oracle 11.2.0.4 environment.  This requires the implementation of Oracle standard fixes:

- BUG 18384537
- BUG 16090440