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

Unable to tune low performing SQL

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
)
Suggest Edit

Published November 18, 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