# 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](../course-plan.md) · Appendix entry: [DBMS_APPLICATION_INFO](../appendix.md#dbms_application_info) · Related: [DBMS_UTILITY GET_TIME](dbms_utility-get_time.md), [DBMS_XPLAN](dbms_xplan.md), [DBMS_HPROF](dbms_hprof.md), [DBMS_PARALLEL_EXECUTE](dbms_parallel_execute.md)

---

## 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

1. 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?".
2. Not clearing it — stale tags on pooled sessions.
3. Values longer than 64 bytes — silently truncated.
4. Reading `V$SQL.MODULE` as "who is running this now" — it is "who parsed this first".
5. Overwriting the caller's module in a library routine (3e).
6. `SET_SESSION_LONGOPS` with `totalwork = 0` — `time_remaining` is NULL and the row shows 0%; estimate the total, even roughly.
7. 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.
8. 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.

## 6. Related

- [DBMS_UTILITY GET_TIME](dbms_utility-get_time.md) — the job's own timing, next to the tags.
- [DBMS_XPLAN](dbms_xplan.md) — finding the job's SQL by module, then its plan.
- [DBMS_HPROF](dbms_hprof.md) — per-function profile of the job.
- [DBMS_PARALLEL_EXECUTE](dbms_parallel_execute.md) — tagging chunk jobs.