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 particular 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);

Originally published at https://zahirmohideen.blogspot.com/2010/02/real-time-monitoring.html