Process Scheduler resource consumption

If you use PepoleSoft Porcess Scheduler to schedule PS Jobs (a collection of PS Processes) then Process Scheduler regularly issues:

SELECT R.PRCSINSTANCE ,R.PRCSNAME ,R.RUNSTATUS
FROM PSPRCSQUE R 
WHERE R.JOBINSTANCE = :1 
AND R.PRCSINSTANCE <> R.JOBINSTANCE
AND R.PRCSJOBSEQ < :2
AND R.RUNSTATUS NOT IN (:"SYS_B_0",:"SYS_B_1")

which results in a table scan of PSPRCSQUE.

This is pretty bad because:

  • this causes a table scan per job
  • data retrieved via a table scan is placed on the LRU end of the buffer cache, i.e. it will quickly drop out of the buffere cache again - in which case it will have to be read from disk again

Use UNIX 'top' to check you Process Scheduler CPU consumption.

Let's create an index to speed this up:

CREATE INDEX SYSADM.JS_IX_PRCSQUE ON SYSADM.PSPRCSQUE
(JOBINSTANCE, PRCSINSTANCE,PRCSJOBSEQ, RUNSTATUS, PRCSNAME)
TABLESPACE PSINDEX
LOGGING;

Data will now be retrieved via a RANGE SCAN of the index which is:

  • a much faster access path
  • data retrieved this way will be inserted in the buffer cache so that it does not age out quickly. It's very likely that it will be still in the buffer cache when Process Scheduler polls the status again, which it does every 15 seconds by default. So data will not have to be re-read from disk.

Don't forget to collect statistics:

begin
dbms_stats.gather_table_stats
(ownname=>'sysadm',tabname=>'psprcsque',
 method_opt=>'for all columns size auto', cascade=>true);
end;

Use UNIX 'top' to check you Process Scheduler CPU consumption again, it should have dropped to 5 % of what it was before ! Physical reads from disk should also be reduced.

Comments (1)