Support Article
SLA update query causing system slowdown
SA-3158
Summary
The server where PRPC is hosted is showing 100% CPU usage. The PR_SYS_QUEUE_SLA table is showing a large number of entries, and the following query is showing a very long running time: UPDATE pr_sys_queue_sla
SET pxProcessingNodeId = :1,
pxLastExecutionDateTime = :2,
pyItemStatus = 'Now-Processing'
WHERE piItemId = :3 AND pyItemStatus != 'Now-Processing'
- This slowness began yesterday morning.
- It is _not_ happening this morning
- You upgraded to 6.1sp2 a month ago
- The slowness occurred once before.
- Oracle 11.2
Resolution
To resolve this problem, obtain/implement the following two software fixes:
HFIX-3262 : Using Bind Variables in a WHERE Clause Cause Oracle Full Table Scans
HFIX-4127: Standard Agents Run Slow on Multiple Nodes. NOTE: Special Installation Instructions.
1) Implement Software Fix HFIX-3262 using the Update Manager
2) Install HFIX-4127. This hotfix is specific to Oracle database.
Please find below instructions to be included in readme.txt.
a) Drop sppr_sys_reservequeueitem_b store procedure using any database tools.
b) Execute the script in sppr_sys_reservequeueitem_b.sql file attached in this hotfix to create the new stored procedure.
c) Restart the server.
2) Install additional needed INDEXes that were not included in the original PRPC Database Schema Installation script.
- Add the 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
);
I would recommend adding the following ADDITIONAL index, as this will help the various queries that are taking a long time:
The index below will address the specific performance issue you are seeing. When assignment is worked, the queue record is updated. As the number of open assignments with SLA's grows, you will see alerts for the
stored procedure call every 30 seconds (from SLA agent) plus you will see alerts when users work assignments:
update pr_sys_queue_sla set
pxProcessingNodeId = ?, pxLastExecuti nDateTime = ?, pyItemStatus =
'Now-Processing' where pyItemId = ? and pyItemStatus != 'Now-Processing'
1. CREATE INDEX pr_sys_queue_sla_index2 ON pr_sys_queue_sla (pyItemId ASC, pyItemStatus ASC);
- create index pr_sys_queue_tidx2 ON pr_sys_queues (pyItemId, pyItemStatus, pxProcessingNodeId)
o Aim is to reduce SQL Execution time, Page Locks and Full Table Scans.
o This should reduce the frequency of deadlock considerably, but not eliminate them completely. These tables are transient, therefore should not have high data volumes in them.
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.