Support Article

Log file Error

SA-25778

Summary



A systems administrator is monitoring their Pega logfiles, and observes long running SQL interaction ALERTS in their Pega ALERT log (PEGA0005 ALERTs). The client is complaining of very slow performance, and some users are losing their browser sessions to PRPC because of the long delay times.

Error Messages



No error messages are displayed on screen.

The Pega ALERT Logfile contains messages similar to the following:

    Database operation took more than the threshold of 500 ms: 224,531,421 ms

Steps to Reproduce



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

Root Cause



The root cause is a continuously running, very slow performing SQL statement from Process Commander against the standard table PC_ASSIGN_WORKLIST.  The SQL is:

SELECT
  "PC0".PYASSIGNMENTSTATUS AS "pyAssignmentStatus",
  "PC0".PXASSIGNEDOPERATORID AS "pxAssignedOperatorID",
  "PC0".PXFLOWNAME AS "pxFlowName",
  "PC0".PZINSKEY AS "pzInsKey",
  "PC0".PXASSIGNEDUSERNAME AS "pxAssignedUserName",
  "PC0".PYLABEL AS "pyLabel",
  "PC0".PYINSTRUCTIONS AS "pyInstructions",
  "PC0".PXCREATEDATETIME AS "pxCreateDateTime",
  "PC0".PXDEADLINETIME AS "pxDeadlineTime",
  "PC0".PXGOALTIME AS "pxGoalTime",
  "PC0".PXURGENCYASSIGN AS "pxUrgencyAssign",
  "PC0".PXFLOWINSKEY AS "pxFlowInsKey",
  "FlowPage".PYLABEL AS "pyLabel",
  "PC0".PXREFOBJECTKEY AS "pxRefObjectKey",
  "PC0".PXREFOBJECTCLASS AS "pxRefObjectClass",
  "PC0".PXASSIGNEDORGUNIT AS "pxAssignedOrgUnit",
  "PC0".PXASSIGNEDORGDIV AS "pxAssignedOrgDiv",
  "PC0".PXASSIGNEDORG AS "pxAssignedOrg"
FROM
  PRPC71_APP.pc_assign_worklist "PC0"
LEFT OUTER JOIN
  PRPC71_APP.pr4_rule_flow "FlowPage" ON
(
  (
    "FlowPage".PZINSKEY = "PC0".PXFLOWINSKEY
  )  AND
  "FlowPage".PXOBJCLASS = ?  AND
  "PC0".PXOBJCLASS = ?
)
WHERE
(
  "PC0".PXREFOBJECTKEY = ?  AND
  "PC0".PXOBJCLASS = ?
)
ORDER BY
  8 ASC

 
Examining the Pegasystems 7.1.8 schema definition file for an Oracle implementation, and comparing the defined INDEX definitions for this SQL, multiple table.property values are found to be missing.  Also, multiple table.property definitions have been identified as missing from this implementation.

Resolution



The solution is to implement suggested INDEXes for the following table.property values:

PR4_LOG_RULE_USAGE_DETAILS.PXOBJCLASS
PR4_LOG_RULE_USAGE_DETAILS.PXSYSTEMNODE
PR4_LOG_RULE_USAGE_DETAILS.PYLABEL

PC_ASSIGN_WORKLIST.PXREFOBJECTKEY
PC_ASSIGN_WORKLIST.PXOBJCLASS
PC_ASSIGN_WORKLIST.PXAPPLICATION
PC_ASSIGN_WORKLIST.PXASSIGNEDOPERATORID
PC_ASSIGN_WORKLIST.PXFLOWINSKEY

PR_SYS_STATUSDETAILS.PXTYPE
PR_SYS_STATUSDETAILS.PYSYSNODEID
PR_SYS_STATUSDETAILS.PYNODENAME
PR_SYS_STATUSDETAILS.PYSYSTEMNAME

PR_SYS_APP_RULESET_INDEX.PZAPPHASH

PR4_RULE_LIBRARY.PYMETHODSTATUS
PR4_BASE.PZRULESETVERSIONMAJOR
PR4_BASE.PZRULESETVERSIONMINOR
PR4_BASE.PZRULESETVERSIONPATCH
PR4_BASE.PYRULESET
PR4_BASE.PYCLASSNAME

Published July 19, 2016 - Updated August 5, 2016

Have a question? Get answers now.

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