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

All connection resource are used and bringing system down

SA-21083

Summary



Seeing an increase in database connections and full table scans that brings the Pega application down. The below query is causing excessive physical reads and is executing 18,000-20,000 times per hour. 

Select pzInsKey from pr_sys_queues where pzInsKey IN (select pzInsKey from ( select pzInsKey from pr_sys_queues where pyItemStatus = :1 and pyAgentName = :2 and pyMinimumDateTimeForProcessing <= :3 order by pzInsKey ASC) where rownum = 1 ) FOR UPDATE SKIP LOCKED

The current execution plan is doing a full table scan of the PR_SYS_QUEUES table for every execution.

Error Messages



Not Applicable

Steps to Reproduce



Not Applicable

Root Cause



A defect in Pegasystems’ code or rules. The 6.1sp1 version of PRPC product media does not ship with some of the required indices.

Resolution



Perform the following local-change: Add the following indexes to this Oracle database:

CREATE INDEX pr_sys_queue_tidx2 ON pr_sys_queues (pyItemStatus ASC, pyAgentName ASC, pyMinimumDateTimeForProcessing ASC, pzInsKey ASC)
;

CREATE INDEX pr_sys_queue_sla_index1 ON pr_sys_queue_sla (pyMinimumDateTimeForProcessing ASC, pzInsKey ASC, pyItemStatus ASC, pyAgentName ASC)
;

CREATE INDEX pr_sys_queue_svc_idx1 ON pr_sys_queue_execreq_svc (pyMinimumDateTimeForProcessing ASC, pzInsKey ASC, pyItemStatus ASC, pyAgentName ASC)
;

CREATE INDEX pr_sys_queue_conn_idx1 ON pr_sys_queue_execreq_conn (pyMinimumDateTimeForProcessing ASC, pzInsKey ASC, pyItemStatus ASC, pyAgentName ASC)
;

CREATE INDEX pr_sys_queue_startflows_idx1 ON pr_sys_queue_startflows (pyMinimumDateTimeForProcessing ASC, pzInsKey ASC, pyItemStatus ASC, pyAgentName ASC)
;

CREATE INDEX pr_sys_queue_col_pop_idx1 ON pr_sys_queue_col_pop (pyMinimumDateTimeForProcessing ASC, pzInsKey ASC, pyItemStatus ASC, pyAgentName ASC)
;

CREATE INDEX pr_cep_queue_idx1 ON pr_cep_events_queue (pyMinimumDateTimeForProcessing ASC, pzInsKey ASC, pyItemStatus ASC, pyAgentName ASC)
;

Published March 18, 2016 - Updated October 8, 2020

Was this useful?

0% found this useful

Have a question? Get answers now.

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

Did you find this content helpful?

Want to help us improve this content?

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