PeopleSoft PS/Query: Identify Long Running Queries (on Process Schedulers)

This is the first of a series in which I will share some of my PeopleSoft scripts, and explain how they work.

Many PeopleSoft users like its ad hoc query tool because they can write their own queries directly on the system, without having to learn to write structured query language (SQL), or getting a developer to write it for them.  

What is the Problem?

This tool is disliked and even feared by database administrators (DBAs) and system administrators, because it is easy for users to create poor queries, that either don't work as intended or can run for long periods, sometimes indefinitely, without even producing results.  This can consume significant amounts of CPU.

Managing Queries Scheduled on the Process Scheduler

The PSQUERY application engine program runs queries on the process scheduler. Users should be encouraged to use this rather than running them online.  

Queries run online via the PeopleSoft Internet Architecture (PIA) cannot be managed.  

• There is no limit to the number of queries that users can initiate concurrently.  
• The number that can actually execute concurrently is limited by the number of PSQRYSRV processes in each application server domain.  Any additional requests will simply queue up in Tuxedo.
• It is possible to set maximum execution times in the PeopleSoft configuration, on the ICQuery service on the PSQRYSRV server in the application server.  

It is easier to manage and monitor the queries run in PSQUERY processes on the process scheduler.  They don't put any load on the PIA, but they put load on the database.

• A system-wide maximum number of concurrently executing instances of the application engine program can be set on the process definition.
• A maximum number of concurrently executing instances of the application engine program per process scheduler can be set (by using a process class).
• The application engine, or its process class, can be given a lower priority so that other queued processes are run in preference.

PS/Queries run either in the PIA or on the process scheduler can be mapped to low-priority consumer groups in an Oracle database resource manager plan so that they do not starve the rest of the system of CPU (see PeopleSoft DBA Blog: PSFT_PLAN: A Sample Oracle Database Resource Manager Plan for PeopleSoft).
A maximum run time, or maximum estimated run time, can be defined for a consumer group.  If the limit is breached an Oracle error is raised: ORA-00040: active time limit exceeded - call aborted. In the PIA, the error message is simply presented to the user.  The scheduled PSQUERY application engine process will terminate and the error will be logged.  In both cases, the user has to recognise the error message and understand what it means.  Otherwise, they will raise the issue with support.
The various methods of setting maximum execution time limits are quite blunt instruments.  They are essentially one-size-fits-all approaches.  Typically, some queries are expected to run for a long time, and then the limits must be set to accommodate them. 

Queries Scheduled on the Process Scheduler

I can query who has run which queries, and how long they ran for.  Simply outer join the run control record for the PSQUERY application engine (PS_QUERY_RUN_CNTL) to the process scheduler request table (PSPRCSRQST).

In this case, I am interested in 

• the top 50 PS/Queries by cumulative execution

• with a cumulative execution time of over 5 minutes (300s)

• that were scheduled yesterday between 8am and 7pm

REM qry_missingjoins.sql
WITH x as (
SELECT r.prcsinstance, r.oprid, r.runcntlid
,      DECODE(c.private_query_flag,'Y','Private','N','Public') private_query_flag, c.qryname
,      CAST(begindttm AS DATE) begindttm
,      CAST(enddttm AS DATE) enddttm
,      runstatus
,      (CAST(NVL(enddttm,SYSDATE) AS DATE)-CAST(begindttm AS DATE))*86400 exec_Secs
FROM   psprcsrqst r
  LEFT OUTER JOIN ps_query_run_cntrl c ON c.oprid = r.oprid AND c.run_cntl_id = r.runcntlid
WHERE  prcsname = 'PSQUERY'
AND    r.begindttm >= TRUNC(SYSDATE)-0+8/24 /*from 8am*/
AND    r.begindttm <= TRUNC(SYSDATE)-0+19/24 /*to 7pm*/
)
SELECT x.* FROM x
WHERE  exec_Secs >= 300 /*Over 5 minutes*/
ORDER BY exec_secs desc /*descending order of elapsed time*/
FETCH FIRST 50 ROWS ONLY /*top 50 ROWS ONLY*/
/

I now have a profile of top queries that I can use to direct further investigation.

  Process                                           Private                                                                    Run     Exec
 Instance OPRID      RUNCNTLID                      Query   QRYNAME                        BEGINDTTM         ENDDTTM           Stat    Secs
--------- ---------- ------------------------------ ------- ------------------------------ ----------------- ----------------- ---- -------
 12344471 F******    ***AM_FIN_GL_AP                Public  ***AM_FIN_GL_AP                10:06:21 19.**.** 19:08:52 19.**.** 8      32551
 12344342 N******    ownxxxxxxxxxxxx                Public  ***_TRIAL_BALANCE_BY_BU_***_V2 09:41:58 19.**.** 18:20:09 19.**.** 10     31091
 12344336 N******    ojnxxxxxxxxxx                  Public  ***_TRIAL_BALANCE_BY_BU_***    09:40:27 19.**.** 16:51:11 19.**.** 10     25844
 12345209 N******    eowxxxxxxxxxxxxx               Public  ***_TRIAL_BALANCE_BY_BU_***    12:41:17 19.**.** 19:08:30 19.**.** 8      23233
 12345213 N******    iwoxxxxxxxxxxxxx               Public  ***_TRIAL_BALANCE_BY_BU_***_V2 12:41:53 19.**.** 19:08:56 19.**.** 8      23223
 12345574 B******    gl                             Private ***_GL_BJU                     14:27:32 19.**.** 19:08:59 19.**.** 8      16887
 12345681 B******    gl                             Private ***_GL_BJU                     14:51:06 19.**.** 19:09:02 19.**.** 8      15476
 12345852 W******    insolvents                     Public  ***INSOLVENTS_JRNL_DETAIL      15:24:41 19.**.** 19:09:04 19.**.** 8      13463
…
                                                                                                                                    -------
sum                                                                                                                                  268112

Notes: 

• Some details have been redacted from this real-world example.
• The result is not guaranteed to be completely accurate.  A user might have reused a run control record and can only get the current value.
• This and other scripts can be downloaded from GitHub davidkurtz/psscripts.