How to Track Sessions with DBMS_APPLICATION_INFO

When a DBA sees a busy or blocked session, the first question is what it is doing. DBMS_APPLICATION_INFO lets your code answer that: it labels the session with a module, an action, and client information, which appear in V$SESSION, in V$SQL, and in monitoring tools.

Code for This Guide

The main examples are in the examples/pkg-utilities folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

dbms_application_info.set_module(module_name => '...', action_name => '...');
dbms_application_info.set_action('...');
dbms_application_info.set_client_info('...');
dbms_application_info.read_module(module_out, action_out);
dbms_application_info.read_client_info(client_info_out);

Each value is cut to 64 bytes if it is longer.

Label a Session

A fare loader sets its module and first action, moves on to a second action, and adds client information. Then it reads the values back, and V$SESSION shows them for the current session.

Example:

declare
  v_module varchar2(64);
  v_action varchar2(64);
  v_client varchar2(64);
begin
  dbms_application_info.set_module(module_name => 'FARE_LOADER',
                                   action_name => 'reading file');
  dbms_application_info.set_action('updating fares');
  dbms_application_info.set_client_info('run for route 12');
  dbms_application_info.read_module(v_module, v_action);
  dbms_application_info.read_client_info(v_client);
  dbms_output.put_line(v_module || ' / ' || v_action || ' / ' || v_client);
end;
/
select module, action, client_info from v$session where sid = sys_context('USERENV', 'SID');

Output:

FARE_LOADER / updating fares / run for route 12

PL/SQL procedure successfully completed.

MODULE         ACTION            CLIENT_INFO
______________ _________________ ___________________
FARE_LOADER    updating fares    run for route 12

SET_ACTION replaced only the action, so the module stays FARE_LOADER. Anyone looking at V$SESSION now knows which program this session is and which step it is in.

Find SQL by Module

Statements run while a module is set are recorded with that module and action in V$SQL.

Example:

-- SQL run under a module is tagged with it in V$SQL
exec dbms_application_info.set_module('FARE_REPORT', 'monthly totals')

select /* fare report */ count(*) from bookings;

select module, action, executions
from   v$sql
where  sql_text like 'select /* fare report */%';

exec dbms_application_info.set_module(null, null)

Output:

PL/SQL procedure successfully completed.

   COUNT(*)
___________
        700

MODULE         ACTION               EXECUTIONS
______________ _________________ _____________
FARE_REPORT    monthly totals                1

PL/SQL procedure successfully completed.

The report query is tagged FARE_REPORT / monthly totals, so its cost can be found and grouped by program. The last call clears the module and action.

Things to Know

• Set the module when a unit of work starts and the action at each step; clear them when it finishes, because they stay on pooled sessions.
• Reading V$SESSION and V$SQL needs privileges on those views; setting the values needs none.
• Many tools, including Active Session History and SQL Monitor, report time by module and action.

Related Guides

• Oracle SYS_CONTEXT Function
• How to Query V$ Views in Oracle

Conclusion

DBMS_APPLICATION_INFO labels a session with module, action, and client information that show up in V$SESSION, V$SQL, and monitoring tools. Set them as your code moves through its work so every session and statement can be traced back to the program and step that ran it.