Using Oracle Enterprise Manager (Grid Control) with PeopleSoft

If you use Oracle Grid Control to monitor your PeopleSoft system, here is a simple tip that will help you identify batch processes.

Oracle provides two columns on the session information (v$session) to hold context information. They provide a PL/SQL package DBMS_APPLICATION_INFO, which has procedures to read and update these values. The idea is that application developers will instrument their programs and update these values. Oracle’s Applications (that it has developed itself), such as E-Business Suite does this. PeopleSoft was rather slow to make use of this. They do set the module and action, but not to very useful values.

However, you can create a trigger on the Process Scheduler request table that will update these values when a process starts.

(Updated 19.4.2009) I have created a PL/SQL package psftapi that contains a number of procedures that I have used from triggers and other PL/SQL programs. It contains a function that sets the ACTION for the session with the process instance and the description of the status.

...
PROCEDURE set_action
(p_prcsinstance INTEGER
,p_runstatus VARCHAR2
) IS
l_runstatus VARCHAR2(10 CHAR);
BEGIN
BEGIN
 SELECT x.xlatshortname
 INTO   l_runstatus
 FROM   psxlatitem x
 WHERE  x.fieldname = 'RUNSTATUS'
 AND    x.fieldvalue = p_runstatus
 AND    x.eff_status = 'A'
 AND    x.effdt = (
  SELECT MAX(x1.effdt)
  FROM   psxlatitem x1
  WHERE  x1.fieldname = x.fieldname
  AND    x1.fieldvalue = x.fieldvalue
  AND    x1.effdt <= SYSDATE);  EXCEPTION    WHEN no_data_found THEN l_runstatus := 'Status:'||p_runstatus;         END;      sys.dbms_application_info.set_action(   action_name => SUBSTR('PI='||p_prcsinstance||':'||l_runstatus,1,32) );
END set_action;
...

This procedure can be called from a trigger:

CREATE OR REPLACE TRIGGER sysadm.psftapi_store_prcsinstance
BEFORE UPDATE OF runstatus ON sysadm.psprcsrqst
FOR EACH ROW
WHEN ((new.runstatus IN('3','7','8','9','10') OR
    old.runstatus IN('7','8')) AND new.prcstype != 'PSJob')
BEGIN
IF :new.runstatus = '7' THEN
 psftapi.set_prcsinstance(p_prcsinstance => :new.prcsinstance);
 psftapi.set_action(p_prcsinstance=>:new.prcsinstance
                   ,p_runstatus=>:new.runstatus
                   ,p_prcsname=>:new.prcsname);
ELSIF psftapi.get_prcsinstance() = :new.prcsinstance THEN
 psftapi.set_action(p_prcsinstance=>:new.prcsinstance
                   ,p_runstatus=>:new.runstatus);
END IF;
EXCEPTION WHEN OTHERS THEN NULL; --exception deliberately coded to suppress all exceptions
END;
/

What is the benefit? The MODULE and ACTION show up in Grid Control. So now you can immediately identify the name and Process Instance of those expensive processes.

Unfortunately, it is not possible to do anything similar for sessions created by the Application Server. So all you know is what session belongs to what kind of server process. The Client Information is set at the top of each service, so you know the PeopleSoft Operator ID, but that is all.

It would be nice if perhaps the Component name and PeopleCode context was written to MODULE and ACTION. But it isn’t.

Updated 9.9.11: PeopleTools 8.50 does exactly this, there is another posting on this subject.