Long Running Processes
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
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;