Support Article
PEGA application slowness
SA-20401
Summary
A systems administrator has been advised by the business of exceptionally poor application performance: login times are slow, and screen-to-screen transition times are very poor. The systems administrator suspects a JDBC Connection Pool Manager configuration problem as they see the PEGA0026 alert appearing in the Pega ALERT log. After a system restart, the system performance is excellent, but progressively degrades over time.
Error Messages
No error messages are displayed on screen; no applicable error messages are found in the Pega Logfile. The Pega ALERT log indicates that the database is performing very poorly.
Steps to Reproduce
Unknown. No specific steps were found to reproduce this problem.
Root Cause
The Pega ALERT log reveals large numbers of PEGA0005 alerts around the standard stored procedure SPPR_SYS_RESERVEQUEUEITEM_B. An examination of the environment reveals that the database is still using the standard version of this stored proc. Customer environment is ORACLE.
Resolution
Advised administrator to update the standard stored procedure SPPR_SYS_RESERVEQUEUEITEM_B. Specifically, update the code of the stored proc:
FROM:
orderby := ' order by pzInsKey ASC';
TO:
orderby := ' order by pyMinimumDateTimeForProcessing ASC';
Have advised administrator to implement the additional following INDEXes:
CREATE INDEX
pr_sys_queue_sla_index1
ON
pr_sys_queue_sla (
pyMinimumDateTimeForProcessing ASC,
pzInsKey ASC,
pyItemStatus ASC,
pyAgentName ASC,
pyItemId ASC
);
CREATE INDEX
pr_sys_workindexer_index1
ON
pr_sys_workindexer_queue (
pyMinimumDateTimeForProcessing ASC,
pzInsKey ASC,
pyItemStatus ASC,
pxObjClass ASC,
pyAgentName ASC
);
CREATE INDEX
queuesla_update
ON
pr_sys_queue_sla (
pyItemId ASC,
pyItemStatus ASC
);
CREATE INDEX
queue_reserve
ON
pr_sys_queues (
pyMinimumDateTimeForProcessing ASC,
pyItemStatus ASC
);
CREATE INDEX
queuesla_reserve
ON
pr_sys_queue_sla (
pyMinimumDateTimeForProcessing ASC,
pyItemStatus ASC
);
CREATE INDEX
queuesvc_reserve
ON
pr_sys_queue_execreq_svc (
pyMinimumDateTimeForProcessing ASC, pyItemStatus ASC
);
CREATE INDEX
queueconn_reserve
ON
pr_sys_queue_execreq_conn (
pyMinimumDateTimeForProcessing ASC,
pyItemStatus ASC
);
Published March 23, 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.