Deep Dive — DBMS_APPLICATION_INFO

Two strings on the session — module and action — and every SQL, every wait, every AWR line the job produces is suddenly attributable. Zero cost. No excuse.

Used in: Chapter 10 — Measure
Appendix entry: DBMS_APPLICATION_INFO
Related: DBMS_UTILITY GET_TIME DBMS_XPLAN DBMS_HPROF DBMS_PARALLEL_EXECUTE

1. What It Is and When

DBMS_APPLICATION_INFO sets three session attributes that Oracle carries into everything it records about the session:

Attribute	Set by	Visible in
`MODULE` (64 bytes)	`SET_MODULE`	`V$SESSION`, `V$SQL` (parse-time), `V$ACTIVE_SESSION_HISTORY`, AWR, SQL trace
`ACTION` (64 bytes)	`SET_MODULE` / `SET_ACTION`	same
`CLIENT_INFO` (64 bytes)	`SET_CLIENT_INFO`	`V$SESSION.CLIENT_INFO`

Plus SET_SESSION_LONGOPS, which publishes a progress row in V$SESSION_LONGOPS — the same view Oracle uses for its own long full scans and sorts.

Use it in every batch job, at every step boundary. It is what lets the DBA answer "what is session 1432 doing?", lets AWR/ASH break time down by job and step, and lets DBMS_MONITOR trace exactly one job without tracing the whole instance.

2. Syntax

SQL

DBMS_APPLICATION_INFO.SET_MODULE(module_name IN VARCHAR2, action_name IN VARCHAR2);
DBMS_APPLICATION_INFO.SET_ACTION(action_name IN VARCHAR2);
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info IN VARCHAR2);
DBMS_APPLICATION_INFO.READ_MODULE(module_name OUT VARCHAR2, action_name OUT VARCHAR2);
DBMS_APPLICATION_INFO.READ_CLIENT_INFO(client_info OUT VARCHAR2);

DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
  rindex      IN OUT BINARY_INTEGER,   -- start with DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT
  slno        IN OUT BINARY_INTEGER,   -- internal, just pass it back
  op_name     IN VARCHAR2 DEFAULT NULL, target IN BINARY_INTEGER DEFAULT 0,
  context     IN BINARY_INTEGER DEFAULT 0, sofar IN NUMBER DEFAULT 0, totalwork IN NUMBER DEFAULT 0,
  target_desc IN VARCHAR2 DEFAULT 'unknown target', units IN VARCHAR2 DEFAULT NULL);

Related: DBMS_SESSION.SET_IDENTIFIER (client identifier, 64 bytes — the per-end-user tag for pooled connections) and DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (trace by module/action).

3. Examples on the Course Schema

3a. Module per job, action per step

SQL

CREATE OR REPLACE PROCEDURE nightly_batch IS
BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('nightly_batch', 'load');
  load_orders;
  DBMS_APPLICATION_INFO.SET_ACTION('reprice');
  reprice_orders;
  DBMS_APPLICATION_INFO.SET_ACTION('archive');
  archive_orders;
  DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);            -- leave the session clean
EXCEPTION
  WHEN OTHERS THEN
    DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
    RAISE;
END;
/

3b. What the DBA now sees

SQL

-- right now
SELECT sid, serial#, module, action, sql_id, event, seconds_in_wait
FROM   v$session WHERE module = 'nightly_batch';

-- top SQL of the job (cursors parsed under this module)
SELECT sql_id, action, executions, ROUND(elapsed_time / 1e6) AS sec, SUBSTR(sql_text, 1, 60) AS txt
FROM   v$sql WHERE module = 'nightly_batch'
ORDER  BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

-- where the time went, by step and wait (needs Diagnostics Pack for ASH)
SELECT action, NVL(event, 'ON CPU') AS event, COUNT(*) AS samples   -- 1 sample ≈ 1 second
FROM   v$active_session_history
WHERE  module = 'nightly_batch' AND sample_time > SYSDATE - 1/24
GROUP  BY action, event
ORDER  BY samples DESC;

The ASH query is the one that answers "3.5 of the 4 hours are in reprice, waiting on db file sequential read" — without a profiler and without the job's cooperation beyond the two strings.

3c. A progress bar in V$SESSION_LONGOPS

SQL

CREATE OR REPLACE PROCEDURE reprice_orders IS
  CURSOR c IS SELECT order_id, amount FROM orders WHERE status = 'NEW';
  TYPE t_rows IS TABLE OF c%ROWTYPE;
  l_rows   t_rows;
  l_total  NUMBER;
  l_done   NUMBER := 0;
  l_rindex BINARY_INTEGER := DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT;
  l_slno   BINARY_INTEGER;
BEGIN
  SELECT COUNT(*) INTO l_total FROM orders WHERE status = 'NEW';   -- or an estimate from stats
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_rows LIMIT 10000;
    EXIT WHEN l_rows.COUNT = 0;
    -- ... process + FORALL + COMMIT ...
    l_done := l_done + l_rows.COUNT;
    DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
      rindex => l_rindex, slno => l_slno,
      op_name => 'reprice orders', sofar => l_done, totalwork => l_total, units => 'rows');
  END LOOP;
  CLOSE c;
END;
/

SQL

SELECT opname, sofar, totalwork, ROUND(100 * sofar / totalwork, 1) AS pct,
       elapsed_seconds, time_remaining
FROM   v$session_longops
WHERE  sid = :sid AND opname = 'reprice orders' AND sofar <> totalwork;

time_remaining is Oracle's own extrapolation. One call per batch (every few seconds) — not per row.

3d. Tracing one job, and nothing else

SQL

-- DBA, once: trace whatever runs under this module/action, on any session
EXEC DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => 'orcl', module_name => 'nightly_batch',
                                            action_name => 'reprice', waits => TRUE, binds => FALSE);
-- ... the job runs; the trace files are found via V$DIAG_INFO / trcsess -module nightly_batch ...
EXEC DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name => 'orcl', module_name => 'nightly_batch',
                                             action_name => 'reprice');

Trace is enabled by name, before the job starts, and catches it wherever it logs in. trcsess merges the files; tkprof summarizes them.

3e. Library code: save, set, restore

SQL

PROCEDURE with_tag (p_action VARCHAR2) IS
  l_mod VARCHAR2(64); l_act VARCHAR2(64);
BEGIN
  DBMS_APPLICATION_INFO.READ_MODULE(l_mod, l_act);
  DBMS_APPLICATION_INFO.SET_ACTION(p_action);
  -- ... work ...
  DBMS_APPLICATION_INFO.SET_MODULE(l_mod, l_act);        -- restore the caller's tag
EXCEPTION
  WHEN OTHERS THEN
    DBMS_APPLICATION_INFO.SET_MODULE(l_mod, l_act);
    RAISE;
END;

A shared routine called from several jobs should not overwrite the caller's module — only refine the action, and put it back.

3f. Tagging DBMS_PARALLEL_EXECUTE chunk jobs

SQL

DBMS_PARALLEL_EXECUTE.RUN_TASK('reprice_2026',
  'BEGIN
     DBMS_APPLICATION_INFO.SET_MODULE(''nightly_batch'', ''reprice chunk'');
     settlement_pkg.settle_range(:start_id, :end_id);
   END;',
  DBMS_SQL.NATIVE, parallel_level => 8);

Scheduler jobs start with a fresh session and no module; set it inside the chunk block so the eight workers show up under the job's name.

3g. Pooled connections — CLIENT_IDENTIFIER for the end user

SQL

-- at the start of each request, from the application tier
EXEC DBMS_SESSION.SET_IDENTIFIER('user:sammy');
EXEC DBMS_APPLICATION_INFO.SET_MODULE('orders_ui', 'search');
-- at the end of the request
EXEC DBMS_SESSION.CLEAR_IDENTIFIER;
EXEC DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);

Module = which code path; client identifier = which human. Most drivers (JDBC setClientInfo, ODP.NET ClientId) set these without a round trip.

4. What Breaks at Scale

Nothing — the calls are memory writes in the session; there is no round trip from PL/SQL. Even per-row calls cost microseconds (but are pointless: tag steps, not rows).
SET_SESSION_LONGOPS per row would be measurable in a 500M loop. Per batch is right.
V$SQL.MODULE/ACTION are recorded at first parse of the cursor. The same SQL text executed later from another module shows the first module. For per-execution attribution use ASH (sample-based, records the session's current module) or AWR's SQL-by-module reports.
Connection pools: a session that keeps a stale module after the request ends attributes the next user's work to it. Clear at request end (3g).

5. Pitfalls

Never setting it — the DBA sees module = 'SQL*Plus' or 'JDBC Thin Client' for every job, and every question starts with "which session is that?".
Not clearing it — stale tags on pooled sessions.
Values longer than 64 bytes — silently truncated.
Reading V$SQL.MODULE as "who is running this now" — it is "who parsed this first".
Overwriting the caller's module in a library routine (3e).
SET_SESSION_LONGOPS with totalwork = 0 — time_remaining is NULL and the row shows 0%; estimate the total, even roughly.
Confusing CLIENT_INFO (free text, SET_CLIENT_INFO) with CLIENT_IDENTIFIER (DBMS_SESSION.SET_IDENTIFIER, used by tracing, auditing, VPD). The second is the important one for end users.
Assuming AWR/ASH is available — V$ACTIVE_SESSION_HISTORY and DBA_HIST_* need the Diagnostics Pack license. V$SESSION, V$SQL, V$SESSION_LONGOPS do not.