Real Time Monitoring / Tracing

I have to say , DBMS_APPLICATION_INFO is one of the under utilized package from Oracle . This is very valuable in tracing , identifying the long running operations , offending sessions ,  if you will .

This can  be valuable

a)  in multi-tier applications , where you need to  trace for a particualr module using DBMS_MONITOR
b) long running operations

SQL> show user



USER is "DBADMIN"


SQL> exec DBMS_APPLICATION_INFO.set_module(module_name => 'Demoing App Info' ,action_name => 'Demonstration');
 
SQL> col username format a14
SQL> col module format a25
SQL> col action format a35
 
SQL> Select username , module , action from GV$SESSion where username ='DBADMIN';
USERNAME MODULE ACTION
-------------- ------------------------- -----------------------------------
DBADMIN Demoing App Info Demonstration

As you can see , it is immediately visible in v$session . You need NOT commit to see the changes.

Let us say , you are going long running operations ( in a loop )  , you can set the action to be indicative of the counter . As you see , the value of module gets carried over , if the following syntax is used.

SQL> exec DBMS_APPLICATION_INFO.set_action(action_name => 'Processing 1 of 100');


PL/SQL procedure successfully completed.

SQL> Select username , module , action from GV$SESSion where username ='DBADMIN';

USERNAME MODULE ACTION
-------------- ------------------------- ----------------------------------
DBADMIN Demoing App Info Processing 1 of 100



This property  can be also set via Java .
 
metrics = new String
OracleConnection.END_TO_END_STATE_INDEX_MAX];
metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = "Zahir M";
metrics[OracleConnection.END_TO_END_MODULE_INDEX] = "Hello.java"; metrics[OracleConnection.END_TO_END_ACTION_INDEX] =
 "testing Hello ";
((OracleConnection)connection).setEndToEndMetrics(metrics, (short)0);