DBMS_APPLICATION_INFO

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.

SET_MODULE
SET_ACTION
SET_CLIENT_INFO
V$SESSION View
Performance Analysis
SET_SESSION_LONGOPS
V$SESSION_LONGOPS View
End-To-End Tracing From Java
Instrumentation Library for Oracle (ILO)
Related articles.

SQL trace, 10046, trcsess and tkprof in Oracle
DBMS_SESSION : Managing Sessions From a Connection Pool in Oracle Databases
SET_MODULE

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.

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

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.

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

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.

BEGIN
  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.
END;
/
V$SESSION View

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

SET LINESIZE 500
SELECT sid,
       serial#,
       username,
       osuser,
       module,
       action,
       client_info
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.