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's internal operations causing poor database performance

SA-31141

Summary



A systems administrator has been contacted by the business regarding performance problems.  

The systems administrator closely examines their database performance, and observes large numbers of updates occurring on the standard table PR_SYS_STATUSNODES.

Performance problems are effecting logins, and user operations.

Error Messages



No error messages are displayed on screen; no specific error messages were isolated in the Pega logfile.  

However, significant numbers of ALERTs were identified in the Pega ALERT log, indicating database performance problems.

Database performance problems were identified against multiple custom database tables.

Steps to Reproduce



Unknown.  No specific steps were identified to reproduce this issue.

Root Cause



Regarding the large number of interactions on table PR_SYS_STATUSNODES, analysis indicated that there may be an instance of this table that contains incorrect data.  From internal research, a corrupted instance could cause this issue.

As for overall database performance: have isolated multiple SQL statements from the ALERT log, and provided analysis or recommended tuning steps to alleviate the performance problems.

Resolution



To resolve this issue, perform the local-change steps:
 
  1. Perform a TRUNCATE of table PR_SYS_STATUSNODES.
  2. Implement the following database INDEX updates:

 

CREATE OR REPLACE INDEX
<SCHEMA_NAME>.PR_SYS_STATUSDETAILS_IDX1
ON
<SCHEMA_NAME>.PR_SYS_STATUSDETAILS
(
  PYSYSNODEID     ASC,
  PXTYPE          ASC,
  PYNODENAME      ASC,
  PYSYSTEMNAME    ASC
)

CREATE INDEX
<SCHEMA_NAME>.DATARECENTS_IDX01
ON
<SCHEMA_NAME>.PR_DATA_RECENTS
(
  PXUPDATEOPNAME    ASC,
  PZRECORDNUMBER    ASC,
  PYRECORDKEY       ASC,
  PXUPDATEDATETIME  ASC
)

3. Contact the Database Administrators: analyze and tune the SQL applied to the following custom database tables:

<SCHEMA_NAME>.<CUSTOM_TABLE_NAME01>

Suggest Edit

Published December 28, 2016 - Updated October 8, 2020

Did you find this content helpful? Yes No

Have a question? Get answers now.

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

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