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

Pega table performance issue - tuning review



A database administrator has identified two possible points of tuning their PegaRULES database. The SA wants to formally present these ideas to Pega Engineering for a sanity check before implementation. The ideas are:

Rebuild PRPC standard PR_SYS_LOCKS_PK primary key constraint with 90% PCTFREE so that INDEX entries per block are reduced. This would possibly reduce the chances of multiple transactions trying to update the same INDEX block.

If this appears effective, the next step would be to implement this change to PRPC standard schema objects: PR_SUS_QUEUE_SLA_INDEX1 and PR_SYS_QUEUEU_FTSINDEXER_PK

There are high number of DELETEs running against table ( 9000 deletes per hr ) – PR_SYS_LOCKS during the same time period as the INSERTS/Updates are running, if there is a configuration setting available , we should shift these DELETEs to an Off-peak period to reduce the number of commits generated by the application during peak periods.

Error Messages

No error messages are displayed on screen; no specific error messages are found in the Pega Logfile. However, multiple PEGA0005 ALERTs are observed in the Pega ALERT log detailing poor database performance.

Steps to Reproduce

Unknown. No specific steps were identified that are causing the poor database performance. The poor performance is sporadic and appears at random.

Root Cause

Performing additional testing, the SA has identified a possible hardware fault.


Pega Engineering provided the following answers to the two database administrator questions:

This suggestion might help the problem, but most likely will not be enough.

Engineering has instead provided these two suggestions:

a) Recreate the primary key on pr_sys_locks using reverse index. This will help the index insert contention and the ‘global buffer busy’ in RAC system. As we don’t use range scan on primary key, this change should be without much downside impact.

b) Add freelist to this table if it’s not using Oracle ASSM. This should help the ‘log file sync’ issue.

This suggestion is invalid: Process Commander SYSTEM-LOCK is itself a function, and cannot be delayed.

Published August 26, 2017 - 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