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>