SA-30210

Summary

A systems administrator has received complaints of poor performance from the business.  

Examining the Pega ALERT log, poor performance is observed on standard table PR_INDEX_REFERENCE.  

As a result, software fix HFIX-26978 was issued to relieve the performance problems observed by the developers against this table.

After taking this software fix, this table performance improved significantly, from several dozen seconds to update, to ten seconds to update.

The sysadmin inquires how to further improve the performance of interactions against this table, and multiple other PRPC standard tables.

Error Messages

No error messages are displayed on screen; no specific error messages are observed in the Pega logfile.  However, the Pega ALERT log is rife with this, and other PEGA0005 alerts:

Database batch execution took more than the threshold of 500 ms: 72,646 ms

SQL:

MERGE INTO
  pega_rules.pr_index_reference t

USING
(
  VALUES
  (? , ? , ? , ? , ? , ? , ? , ? ,
   ? , ? , ? , ? , ? , ? , ? , ? ,
   ? , ? , ? , ? , ? , ? , ? , ? ,
   ? , ? , ? , ?)
) AS inputs
(
...

Steps to Reproduce

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

Root Cause

Additional analysis reveals multiple standard tables that are missing needed schema INDEXes.  

​Specifically, additional INDEXes are needed against the following PegaRULES schema table objects:

- PR_INDEX_REFERENCE
- PR4_BASE
- PR4_LOG_RULE_USAGE_DETAILS
- PR4_RULE_VW
- PR4_RULE_SHORTCUT
- PR_DATA_RECENTS
- PR_SYS_STATUSDETAILS

Resolution

Advised systems administrator to contact their Database Administrator, and request that the following additional IDNEXes be defined:

CREATE INDEX rules.indexref_idx01
ON rules.pr_index_reference
(
  PXRULEFAMILYNAME  ASC,
  PXRULECLASSNAME   ASC,
  PINSINDEXEDKEY    ASC,
  PXINDEXPURPOSE    ASC,
  PXOBJCLASS        ASC
)

CREATE OR REPLACE INDEX RULES.PR4_BASE_IDX2
ON RULES.PR4_BASE
(
  PYRULESET              ASC,
  PZRULESETVERSIONMAJOR  ASC,
  PZRUELSETVERSIONMINOR  ASC,
  PZRULESETVERSIONPATCH  ASC,
  PYCLASSNAME            ASC
)

CREATE INDEX PR4_LOG_DETAIL_IDX2
ON RULES.PR4_LOG_RULE_USAGE_DETAILS
(
  PXOBJCLASS     ASC,
  PYLABEL        ASC
)

CREATE INDEX RULES.PR4_RULE_VW_IDX7
ON RULES.PR4_RULE_VW
(
  PYCIRCUMSTANCETYPE ASC,
  PYRULENAME  ASC,
  PYRULESTARTS  ASC,
  PYRULEENDS  ASC
)

CREATE OR REPLACE INDEX RULE_SHORTCRES_1
ON PR4_RULE_SHORTCUT
(
  PXINSID  ASC,
  PXOBJCLASS  ASC,
  PYCLASSNAME  ASC,
  PYRULEINSNAME  ASC,
  PYRULEAVAILABLE  ASC
)

CREATE INDEX RULES.DATARECENTS_IDX01
ON RULES.PR_DATA_RECENTS
(
  PXUPDATEOPNAME    ASC,
  PZRECORDNUMBER    ASC,
  PYRECORDKEY       ASC,
  PXUPDATEDATETIME  ASC
)

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