Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

High number of PEGA005 alerts for sppr_sys_reservequeueitem_b

SA-8299

Summary



Poor system performance observed. Examination of PRPC ALERT log shows high occurrence of PEGA005 alerts, with the majority of these alerts pointing at Process Commander Standard Stored Procedure sppr_sys_reservequeueitem_b.
Database type is Microsoft SQL Server 64bit 10.50.4000 SP2.


Error Messages



Not Applicable

Steps to Reproduce



Start PRPC and permit users to log in. Observe that performance degrades appreciably during the business day.

Root Cause



The stored procedure sppr_sys_reservequeueitem_b is known to have a performance problem in all PRPC6 series implementations. This performance problem impacts all database implementations; DB2, MS SQL Server, and Oracle.

Resolution



The fix for Oracle and MS SQL Server is to change the ORDER BY clause in the generated SQL:
 
From:
 
orderby := ' order by pzInsKey ASC';
 
To:
 
orderby := ' order by pyMinimumDateTimeForProcessing ASC';

However, the fix for DB2 is more invasive, usually requiring changes to both:
 
- sppr_sys_reservequeueitem
- sppr_sys_reservequeueitem_b

Note that for Oracle 11.2.0.3 and 11.2.0.4, you must implement Oracle software fixes for the following Oracle Bugs:
 
Oracle BUG ID:
  • BUG 18384537
  • BUG 16090440
 
The reason for this is that the stored procedure’s PL SQL is written in such a way that these bugs impose themselves, and crash the database.  Apply the following HFix to resolve the issue in Oracle(in addition to applying the above the mentioned bug fixes):
  • HFix-8989
  • HFix-7530
  • HFix-8285
  • HFix-7492
2. Update the below DBMS INDEXes:

CREATE INDEX pr_sys_queues_index1
ON dbo.pr_sys_queues
(
    pyItemStatus asc,
    pyAgentName asc,
    pyMinimumDateTimeForProcessing asc,
    pzInsKey asc
)
CREATE INDEX pr_sys_queues_index2
ON dbo.pr_sys_queues
(
    pyItemId asc,
    pyItemStatus asc,
    pxProcessingNodeId asc
)

CREATE INDEX pr_sys_queue_sla_index1
ON dbo.pr_sys_queue_sla
(
    pyItemStatus asc,
    pyAgentName asc,
    pyMinimumDateTimeForProcessing asc,
    pzInsKey asc
)
CREATE INDEX pr_sys_queue_sla_index2
ON dbo.pr_sys_queue_sla
(
    pyItemId asc,
    pyItemStatus asc,
    pxProcessingNodeId asc
)

CREATE INDEX pr_sys_queue_svc_idx1
ON dbo.pr_sys_queue_execreq_svc
(
    pyItemStatus asc,
    pyAgentName asc,
    pyMinimumDateTimeForProcessing asc,
    pzInsKey asc
)
CREATE INDEX pr_sys_queue_svc_idx2
ON dbo.pr_sys_queue_execreq_svc
(
    pyItemId asc,
    pyItemStatus asc,
    pxProcessingNodeId asc
)

CREATE INDEX pr_sys_queue_startflows_idx1
ON dbo.pr_sys_queue_startflows
(
    pyItemStatus asc,
    pyAgentName asc,
    pyMinimumDateTimeForProcessing asc,
    pzInsKey asc
)
CREATE INDEX pr_sys_queue_startflows_idx2
ON dbo.pr_sys_queue_startflows
(
    pyItemId asc,
    pyItemStatus asc,
    pxProcessingNodeId asc
)
CREATE INDEX pr_sys_queue_conn_idx1
ON dbo.pr_sys_queue_execreq_conn
(
    pyItemStatus asc,
    pyAgentName asc,
    pyMinimumDateTimeForProcessing asc,
    pzInsKey asc
)
CREATE INDEX pr_sys_queue_conn_idx2
ON dbo.pr_sys_queue_execreq_conn
(
    pyItemId asc,
    pyItemStatus asc,
    pxProcessingNodeId asc
)
CREATE INDEX pr_sys_workindexer_index1
ON dbo.pr_sys_workindexer_queue
(
    pyItemStatus asc,
    pyAgentName asc,
    pyMinimumDateTimeForProcessing asc,
    pzInsKey asc
)
CREATE INDEX pr_sys_workindexer_index2
ON dbo.pr_sys_workindexer_queue
(
    pyItemId asc,
    pyItemStatus asc,
    pxProcessingNodeId asc
)

Suggest Edit

Published January 31, 2016 - Updated October 8, 2020

Did you find this content helpful? Yes No

Have a question? Get answers now.

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

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us