put your own progress information in V$SESSION_LONGOPS

If only more developers used the DBMS_APPLICATION_INFO package.  As a DBA 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.

Ok so an example, the following anonymous PL/SQL block incorporates all off the above ...

DECLARE
  rindex    BINARY_INTEGER;
  slno      BINARY_INTEGER;
  totalwork NUMBER;
  sofar     NUMBER;
  obj       BINARY_INTEGER;
BEGIN
  --
  -- set initial module name and action
  --
  DBMS_APPLICATION_INFO.SET_MODULE( 'TEST MODULE','AT START' );

  DBMS_LOCK.SLEEP(10); -- Simulate Some Work

  --
  -- Update progress by setting the action
  --
  DBMS_APPLICATION_INFO.SET_ACTION( 'IN THE MIDDLE' );

  DBMS_LOCK.SLEEP(10); -- Simulate Some Work

  --
  -- Use SET ACTION to show progress
  --
  FOR i IN 1..10
  LOOP
    DBMS_APPLICATION_INFO.SET_ACTION( 'IN LOOP ' || TO_CHAR( i ) );
    DBMS_LOCK.SLEEP(1); -- Simulate Some Work
  END LOOP;

  --
  -- Demo of using SESSION_LONGOPS to show progress
  --
  DBMS_APPLICATION_INFO.SET_ACTION( 'LONG OPS DEMO' );

  rindex    := DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT;
  sofar     := 0;
  totalwork := 10;

  WHILE sofar < totalwork
  LOOP
    DBMS_LOCK.SLEEP(1); -- Simulate Some Work

   sofar := sofar + 1;
   DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(rindex, slno,
            'Test Operation', obj, 0, sofar, totalwork, 'Contract', 'Contracts');
  END LOOP;

  -- Clean up and reset MODULE and ACTION as we exit.
  DBMS_APPLICATION_INFO.SET_MODULE( NULL,NULL );
END;
/

So when I run this PL/SQL I can monitor the progress using two SQL statements in an another session.  The first monitors what the progress in v$SESSION ...

SELECT  sid
       ,module
       ,action
  FROM v$session
 WHERE module IS NOT NULL
/

       SID MODULE          ACTION
---------- --------------- ---------------
        10 TEST MODULE     AT START

ops$oracle@ORA9I> /

       SID MODULE          ACTION
---------- --------------- ---------------
        10 TEST MODULE     IN THE MIDDLE

ops$oracle@ORA9I> /

       SID MODULE          ACTION
---------- --------------- ---------------
        10 TEST MODULE     IN LOOP 4

ops$oracle@ORA9I> /

       SID MODULE          ACTION
---------- --------------- ---------------
        10 TEST MODULE     IN LOOP 6

ops$oracle@ORA9I> /

       SID MODULE          ACTION
---------- --------------- ---------------
        10 TEST MODULE     IN LOOP 9

ops$oracle@ORA9I> /

       SID MODULE          ACTION
---------- --------------- ---------------
        10 TEST MODULE     LONG OPS DEMO


The second monitors V$SESSION_LONGSOPS through the long ops  section of the test script ...

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

ops$oracle@ORA9I> /
                                      TOTAL                 ELAPSED       TIME
       SID OPNAME          SOFAR       WORK UNITS           SECONDS  REMAINING
---------- --------------- ----- ---------- ------------ ---------- ----------
        10 Test Operation      1         10 Contracts             0          0

ops$oracle@ORA9I> /
                                      TOTAL                 ELAPSED       TIME
       SID OPNAME          SOFAR       WORK UNITS           SECONDS  REMAINING
---------- --------------- ----- ---------- ------------ ---------- ----------
        10 Test Operation      2         10 Contracts             0          0

ops$oracle@ORA9I> /
                                      TOTAL                 ELAPSED       TIME
       SID OPNAME          SOFAR       WORK UNITS           SECONDS  REMAINING
---------- --------------- ----- ---------- ------------ ---------- ----------
        10 Test Operation      4         10 Contracts             3          5

ops$oracle@ORA9I> /
                                      TOTAL                 ELAPSED       TIME
       SID OPNAME          SOFAR       WORK UNITS           SECONDS  REMAINING
---------- --------------- ----- ---------- ------------ ---------- ----------
        10 Test Operation      6         10 Contracts             6          4

ops$oracle@ORA9I> /
                                      TOTAL                 ELAPSED       TIME
       SID OPNAME          SOFAR       WORK UNITS           SECONDS  REMAINING
---------- --------------- ----- ---------- ------------ ---------- ----------
        10 Test Operation      9         10 Contracts             9          1

ops$oracle@ORA9I>

As you can see it also tries to estimate the time remaining for the task to complete, so you can see how much longer the job is going to take based on current progress,  This is great tool for developers debugging their code and production DBAs trying to work out whats going on.