Query – PeopleSoft Jobs & processes that took more than 60 minutes to run

The following query can be used to identify any long running processes from the process request table.

It identifies any processes that took more than 60 minutes to run.

WITH GOT_RUN_MINUTES AS
(SELECT SERVERNAMERUN,
PRCSINSTANCE,
ORIGPRCSINSTANCE,
PRCSTYPE,
PRCSJOBNAME,
PRCSNAME,
RUNDTTM,
RQSTDTTM,
OPRID,
(SELECT XLATSHORTNAME
FROM PSXLATITEM
WHERE FIELDNAME = 'RUNSTATUS'
AND FIELDVALUE = RUNSTATUS
) AS RUNSTATUS,
BEGINDTTM,
ENDDTTM,
(TO_DATE (TO_CHAR (ENDDTTM, 'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' )-TO_DATE (TO_CHAR (BEGINDTTM, 'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ))*(24*60) AS MINUTES
FROM PSPRCSRQST
WHERE RQSTDTTM > SYSDATE-1
ORDER BY RQSTDTTM DESC
)
SELECT SERVERNAMERUN,
PRCSINSTANCE,
ORIGPRCSINSTANCE,
PRCSTYPE,
PRCSJOBNAME,
PRCSNAME,
RUNDTTM,
RQSTDTTM,
OPRID,
RUNSTATUS,
BEGINDTTM,
ENDDTTM,
MINUTES
FROM GOT_RUN_MINUTES
WHERE MINUTES > '60';