Sunday, December 21, 2008

Poor performance of PSPMSESSIONS_VW view affects Performance Monitor System Monitor Component

The System Performance Monitor component (PSPMSYSHEALTH; navigation: PeopleTools -> Performance Monitor -> System Performance) gives an overview of each system monitored by the PeopleSoft Performance Monitor. However, the poor performance of the view PSPMSESSIONS_VW can severely affect this component, to the extent that as transaction history builds up the component will not respond within the timeout.

Below is an extract from a PeopleTools trace. What happened is that the query ran until the Tuxedo service timed out. Tuxedo terminated application server process 31944, and spawned a new process (ID 3598). The user session received an error.
PSAPPSRV.31944 (70)   1-783    11.38.38    0.000072
Cur#1.31944.PMONITOR RC=0 Dur=0.000040 COM
Stmt=select count(*) from pspmsessions_vw where pm_agentid = :1
PSAPPSRV.31944 (70) 1-784 11.38.38 0.000008
Cur#1.31944.PMONITOR RC=0 Dur=0.000001
Bind-1 type=19 length=3 value=689
PSAPPSRV.3598 (112) 1-132 11.42.39 314.495972
Cur#1.3598.PMONITOR RC=0 Dur=0.000095 COM
Stmt=SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS'

Even when each query finishes within a reasonable amount of time, the query can be run several times by the component.

This is the definition of PSPMSESSIONS_VW delivered by PeopleSoft.
SELECT T3.PM_CONTEXT_VALUE1
, T3.PM_AGENTID
, T3.PM_AGENT_STRT_DTTM
FROM PSPMTRANSHIST T3
WHERE T3.PM_TRANS_DEFN_ID = 116
AND PM_TRANS_STATUS = '1'
AND PM_TRANS_DURATION <> 0
AND T3.PM_CONTEXT_VALUE1 IN (
SELECT T.PM_CONTEXT_VALUE1
FROM PSPMTRANSHIST T
WHERE T.PM_TRANS_DEFN_SET = 1
AND T.PM_TRANS_DEFN_ID = 109
AND T.PM_MON_STRT_DTTM > %TimeAdd(%CurrentDateTimeIn, -720)
AND T.PM_PARENT_INST_ID <> PM_TOP_INST_ID
AND T.PM_CONTEXT_VALUE1 NOT IN (
SELECT T2.PM_CONTEXT_VALUE1
FROM PSPMTRANSHIST T2
WHERE T2.PM_TRANS_DEFN_SET = 1
AND T2.PM_TRANS_DEFN_ID = 108
AND T2.PM_CONTEXT_VALUE1 = T.PM_CONTEXT_VALUE1
AND T2.PM_MON_STRT_DTTM > %TimeAdd(%CurrentDateTimeIn, -720)))

Firstly, accurate object statistics are required on the transaction history table and its indexes.

An additional index is required on PSPMTRANSHIST:
CREATE INDEX PSPPSPMTRANSHIST ON PSPMTRANSHIST
(PM_TRANS_DEFN_SET
,PM_TRANS_DEFN_ID
,PM_CONTEXT_VALUE1
,PM_MON_STRT_DTTM)
TABLESPACE PSINDEX PCTFREE 0
PARALLEL NOLOGGING COMPRESS 3
/
ALTER INDEX PSPPSPMTRANSHIST NOPARALLEL LOGGING
/

Finally, I have changed the view.
  • The IN() operators have been changed to WHERE EXISTS(). The new index supports the efficient execution of these sub-queries.
  • The sub-queries are now both correlated back to the main query on T3.
  • The ROWNUM criteria have been added to restrict the number of rows the sub-queries can return.

SELECT T3.PM_CONTEXT_VALUE1 /*Session ID*/
, T3.PM_AGENTID
, T3.PM_AGENT_STRT_DTTM
FROM PSPMTRANSHIST T3
WHERE T3.PM_TRANS_DEFN_ID = 116 /*Redirect after login*/
AND T3.PM_TRANS_DEFN_SET = 1 /*added*/
AND T3.PM_TRANS_STATUS = '1'
AND T3.PM_TRANS_DURATION <> 0
AND EXISTS(
SELECT 'x'
FROM PSPMTRANSHIST T
WHERE T.PM_TRANS_DEFN_SET = 1
AND T.PM_TRANS_DEFN_ID = 109 /*User Session Began*/
AND T.PM_MON_STRT_DTTM > %TimeAdd(%CurrentDateTimeIn, -720)
AND T.PM_PARENT_INST_ID <> T.PM_TOP_INST_ID
AND T.PM_CONTEXT_VALUE1 = T3.PM_CONTEXT_VALUE1
AND NOT EXISTS(
SELECT 'x'
FROM PSPMTRANSHIST T2
WHERE T2.PM_TRANS_DEFN_SET = 1
AND T2.PM_TRANS_DEFN_ID = 108 /*User Session Ended*/
AND T2.PM_MON_STRT_DTTM > %TimeAdd(%CurrentDateTimeIn, -720)
AND T2.PM_CONTEXT_VALUE1 = T3.PM_CONTEXT_VALUE1
AND ROWNUM <= 1)
AND ROWNUM <= 1)

The effect of these changes is a significant improvement in the performance of the query. I ran a test query for a single agent on a system with over 3 million rows on PSPMTRANSHIST:
  • Original: 24895 consistent gets
  • New Index: 22547 consistent gets
  • and View Changes: 85 consistent gets
Your mileage may vary, but for me this made the difference between the component being usable and not.