Tags

,

Ever asked yourselves

  • What progress has my batch process done so far? 
  • Which iteration is currently processed?
  • How much longer will the job run?
  • What step is the batch process currently processing?

These and other questions could be easily answered if more developers made use of the DBMS_APPLICATION_INFO package. 

DBMS_APPLICATION_INFO may add information to V$SESSION and V$SESSION_LONGOPS views. The package is writing its data to memory and therefore the overhead produced by using this package is insignificant.

 

SET_MODULE

When a batch is started it should subscribe itself using the SET_MODULE procedure of DBMS_APPLICATION_INFO. Along with the name of the module the initial action is registered.

BEGIN
   SYS.DBMS_APPLICATION_INFO.Set_Module(module_name => 'MyBatch'
                                       ,action_name => 'Initialize');
   ...
END;
/

The name of the module is limited to 48 bytes (longer values will be truncated), the name of the action to 32 bytes.

SET_ACTION

This procedure is used to provide the current state of the module that has been registered using the Set_Module procedure. 

BEGIN
   SYS.DBMS_APPLICATION_INFO.Set_Action(action_name => 'Closing Orders');
   ...
END;
/

SET_CLIENT_INFO

The “Client Information” may be used to provide additional information.

BEGIN
   SYS.DBMS_APPLICATION_INFO.Set_Action(action_name => 'Closing Orders');
   SYS.DBMS_APPLICATION_INFO.Set_Client_Info(Client_Info => 'Iteration 3');
 ...
END;
/

The client_info is limited to 64 bytes.

Querying V$SESSION View

V$SESSION may be queried from a different session to see what the batch process is doing.

select sid, serial#, username, module, action, client_info
 from v$session
 where username = 'TEST'
/

  SID SERIAL# USERNAME MODULE         ACTION          CLIENT_INFO 
----- ------- -------- -------------- --------------- --------------
   38    6914 TEST     SQL Developer 
  269   51268 TEST     SQL Developer  Closing Orders  Iteration 3

SET_SESSION_LONGOPS

This procedure call is reflected in the V$SESSION_LONGOPS view and may be used to provide information on the progress of a task.

DECLARE
   l_rindex     BINARY_INTEGER;
   l_slno       BINARY_INTEGER;
   l_iterations PLS_INTEGER := 10;
BEGIN
   l_rindex := SYS.DBMS_APPLICATION_INFO.set_session_longops_nohint;

   FOR i in 1..l_iterations
   LOOP
      SYS.DBMS_APPLICATION_INFO.set_session_longops(rindex      => l_rindex
                                                   ,slno        => l_slno
                                                   ,op_name     => 'TEST'
                                                   ,sofar       => i
                                                   ,totalwork   => l_iterations
                                                   ,units       => 'Iterations'
                                                   );
      SYS.DBMS_LOCK.Sleep(3);
   END LOOP;
END;
/

 

Querying the longops view:

SELECT opname
      ,sofar
      ,totalwork
      ,units
  FROM v$session_longops
/

OPNAME       SOFAR TOTALWORK  UNITS 
------- ---------- ---------- --------------------------------
TEST             2         10 Iterations

Conclusion

If you have not yet used DBMS_APPLICATION_INFO in any of your processes it is worth looking at this package. The information provided when using this package for long running processes might keep you away from killing a process just because you *think* it should have ended long time ago but you have no idea how far the process is….