DBMS_APPLICATION_INFO package

I often see requests to investigate why a particular job is taking longer than expected, or to kill a session running a particular job. The problem is identifying the session, and they trying to identify what the session is doing, or what part of the batch process is running.

If developers used the DBMS_APPLICATION_INFO package to instrument their code it would make mine that their life much easy. The package allows you to specify a Module and Action for the current position in the code. This can be monitored externally using V$SESSION and also appears in V$SQLAREA to allow you to match SQL to a module.

You can also use the package to put your own progress information in V$SESSION_LONGOPS. If you haven't come across this view before Oracle itself populates it when doing "long operations", so you can monitor the progress of an index rebuild, or how far a FTS has got. With the DBMS_APPLICATION_INFO package you can show the progress of you own batch processing, eg. You have processes 300 contracts out of 2000 etc.

- at start
DBMS_APPLICATION_INFO.SET_MODULE( 'TEST MODULE','AT START' );
- when complete
DBMS_APPLICATION_INFO.SET_MODULE( NULL,NULL );

The first monitors what the progress in v$SESSION ...
SELECT sid , module ,action
FROM v$session
WHERE module IS NOT NULL

The second monitors V$SESSION_LONGSOPS through the long ops section

SELECT sid ,opname,sofar,totalwork,units,elapsed_seconds ,time_remaining FROM v$session_longops WHERE sofar != totalwork;