Support Article
High number of PEGA005 alerts for sppr_sys_reservequeueitem_b
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
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
)
Published January 31, 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.