PeopleSoft Wiki

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.

Note this particular SQL is written for an Oracle database, but it wouldn't be hard to modify for other platforms.

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;