Long Running Processes
The following query can be used to identify any long running processes from the process request table PSPRCSRQST.
It identifies any processes that took more than 300 seconds (5 minutes) to run using the difference of the process begin date/time and process end date/time that were run over the past 7 days. You can easily change these values to whatever parameters are most suitable for your environment.
Having this information can help identify processes that are taking a long time to run and may be holding up other processes. If you have more than one process scheduler available, it may be worth moving such long running processes (if they can't be optimised) to another process scheduler to improve performance for other users. If you only have the one process scheduler in production, then the information from this query may be grounds for justifying for another process scheduler to be put in place.
select
PQ.SERVERNAMERUN,
PQ.PRCSINSTANCE,
PQ.PRCSTYPE,
PQ.PRCSJOBNAME,
PQ.PRCSNAME,
PD.DESCR,
PQ.OPRID,
PQ.RUNCNTLID,
(
select XLATSHORTNAME
from PSXLATITEM
where FIELDNAME = 'RUNSTATUS'
and FIELDVALUE = PQ.RUNSTATUS
) as RUNSTATUS,
PQ.RUNDTTM,
PQ.RQSTDTTM,
round((PQ.ENDDTTM - PQ.BEGINDTTM) * 24 * 60 * 60) || ' seconds' as PROCESSING_TIME
from
PSPRCSRQST PQ inner join PS_PRCSDEFN PD
on PQ.PRCSNAME = PD.PRCSNAME
where
round((ENDDTTM - BEGINDTTM) * 24 * 60 * 60) >= 300
and trunc(PQ.RUNDTTM) >= trunc(sysdate - 7)
order by PRCSINSTANCE desc;
No Comments