Friday, March 13, 2009

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);
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
WHEN ((new.runstatus IN('3','7','8','9','10') OR
old.runstatus IN('7','8')) AND new.prcstype != 'PSJob')
IF :new.runstatus = '7' THEN
psftapi.set_prcsinstance(p_prcsinstance => :new.prcsinstance);
ELSIF psftapi.get_prcsinstance() = :new.prcsinstance THEN
EXCEPTION WHEN OTHERS THEN NULL; --exception deliberately coded to suppress all exceptions
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.
Screenshot from Oracle Enterprise Manager
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.

1 comment :

Nicolas Gasparotto said...

If I well remember a Peoplesoft presentation during the last Oracle Open World, it was told about an improvement within the last Peopletools regarding the v$session informations you can take from.
Maybe in the coming PT8.50 with a backport in the latest patches of PT8.49 ?