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.
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.
Firstly, accurate object statistics are required on the transaction history table and its indexes.
An additional index is required on PSPMTRANSHIST:
Finally, I have changed the view.
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:
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