8i | 9i | 10g | 11g | 12c | Misc | PL/SQL | SQL | RAC | Linux

Home » Articles » 8i » Here

DBMS_APPLICATION_INFO : For Code Instrumentation

The DBMS_APPLICATION_INFO package allows programs to add information to the V$SESSION and V$SESSION_LONGOPS views to make tracking of session activities more accurate. Later releases of Oracle can make use of this information in auditing, SQL tracing and performance tuning.

The dynamic performance views are not conventional views against tables, but wrappers over memory structures in the Oracle kernel, so the DBMS_APPLICATION_INFO package is writing the data to memory, which means there is very little overhead in using this functionalty.

Related articles.


Once a program makes a connection to the database it should register itself as a module using the SET_MODULE procedure. In doing so it also sets the initial action. The following code shows how to register a program called "add_order" and indicate it is currently attempting to add records to the "order" table.

  DBMS_APPLICATION_INFO.set_module(module_name => 'add_order',
                                   action_name => 'insert into orders');
  -- Do insert into ORDERS table.


Subsequent processing should use the SET_ACTION procedure to reflect the current status or action of the session. Following on from the previous example, the program continues by adding records to the "order_lines" table, so the action is altered reflect this status change.

  DBMS_APPLICATION_INFO.set_action(action_name => 'insert into order_lines');
  -- Do insert into ORDER_LINES table.


The SET_CLIENT_INFO procedure can be used if any additional information is needed. It is useful to adding a little more context to the action, as shown in the example below. This should not be confused with the CLIENT_IDENTIFIER column, set using the DBMS_SESSION package.

  DBMS_APPLICATION_INFO.set_action(action_name => 'insert into orders');
  DBMS_APPLICATION_INFO.set_client_info(client_info => 'Issued by Web Client');
  -- Do insert into ORDERS table.


The information set by these procedures can be read from the MODULE, ACTION and CLIENT_INFO columns of the V$SESSION view.

FROM   v$session;

As well as being useful in its own right, this extra information in the V$SESSION view is very useful for later versions of the database. The DBMS_MONITOR package, introduced in 10g, can use specific combinations of the MODULE and ACTION columns to enable and disable SQL trace.

Performance Analysis

In later releases of the database, the instrumentation provided by the DBMS_APPLICATION_INFO package comes into its own during performance analysis, because the module and action information is visible in a number of locations, including Enterprise Manager performance graphs, ASH and AWR reports.

The following image is taken from the Enterprise Manager Top Activity screen, where the activity is broken down by module.

Top Activity - Modules

The following image is taken from the Enterprise Manager Top Activity screen, where the activity is broken down by action.

Top Activity - Actions

The ASH Report includes a breakdown of activity based on Service/Module. The AWR Report includes the module in all the reported SQL statistics. You can see examples of those reports below.


The SET_SESSION_LONGOPS procedure can be used to show the progress of long operations by inserting rows in the V$SESSION_LONGOPS view. For it to work effectively, you must know the total amount of work you are planning to do and how much work you have done so far.

  v_rindex     PLS_INTEGER;
  v_slno       PLS_INTEGER;
  v_totalwork  NUMBER;
  v_sofar      NUMBER;
  v_obj        PLS_INTEGER;
  v_rindex     := DBMS_APPLICATION_INFO.set_session_longops_nohint;
  v_sofar     := 0;
  v_totalwork := 10;

  WHILE v_sofar < 10 LOOP
    -- Do some work

    v_sofar := v_sofar + 1;
    DBMS_APPLICATION_INFO.set_session_longops(rindex      => v_rindex, 
                                              slno        => v_slno,
                                              op_name     => 'Batch Load', 
                                              target      => v_obj, 
                                              context     => 0, 
                                              sofar       => v_sofar, 
                                              totalwork   => v_totalwork, 
                                              target_desc => 'BATCH_LOAD_TABLE', 
                                              units       => 'rows processed');


The information written by the SET_SESSION_LONGOPS procedure can be read using the the V$SESSION_LONGOPS view as follows.

SELECT  opname,
FROM    v$session_longops;

Some of the diagnostics features of Enterprise Manager use the contents of the V$SESSION_LONGOPS view when generating progress bars for existing processing.

End-To-End Tracing From Java

Java programs connecting to the database using JDBC can set the MODULE, ACTION and CLIENT_IDENTIFIER columns of the V$SESSION view without calls to the DBMS_APPLICATION_INFO or DBMS_SESSION packages. This make it a more scalable solution, since extra calls to the database are not required.

When a new connection (conn) is made, or a connection is pulled from the connection pool, the details are set using the following type of code.

try {
  String e2eMetrics[] = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
  e2eMetrics[OracleConnection.END_TO_END_ACTION_INDEX]   = "Starting";
  e2eMetrics[OracleConnection.END_TO_END_MODULE_INDEX]   = "MyProgram";
  e2eMetrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = "tim_hall";
  ((OracleConnection) conn).setEndToEndMetrics(e2eMetrics, (short) 0);
} catch (SQLException sqle) {
  // Do something...

Before releasing a connection back to the connection pool, the details should be blanked.

try {
  String e2eMetrics[] = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
  e2eMetrics[OracleConnection.END_TO_END_ACTION_INDEX]   = null;
  e2eMetrics[OracleConnection.END_TO_END_MODULE_INDEX]   = null;
  e2eMetrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = null;
  ((OracleConnection) conn).setEndToEndMetrics(e2eMetrics, Short.MIN_VALUE);
} catch (SQLException sqle) {
  // Do something...

Instrumentation Library for Oracle (ILO)

Those kind folks at Method-R have produced a wrapper API that adds some nice functionality to DBMS_APPLICATION_INFO. You can find it here.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.