Enable a 10046 Trace for an Oracle Session

calendar Jul 10, 2026 / · 13 min read · Oracle DBA SQL Trace 10046 Trace Performance Diagnostics ORADEBUG DBMS_MONITOR Oracle Performance Trace File  ·
Share on: linkedin copy

Enable a 10046 Trace for an Oracle Session

Purpose

A 10046 trace returns wait events only if the level includes them. Enabling the trace at level 1 and then wondering why the file shows no waits is the most common 10046 mistake in production diagnosis — the output has elapsed times and call counts, but none of the db file sequential read, latch: cache buffers chains, or enq: TX - row lock contention events that explain where those seconds actually went. Choosing the wrong level produces a trace file that answers a different question than the one being investigated.

Event 10046 is Oracle's internal SQL trace event. It exposes information about a session's SQL execution that ALTER SESSION SET SQL_TRACE = TRUE cannot: bind variable values and wait event detail, controlled by the trace level. The four levels in routine use are:

  • Level 1 — SQL text, parse count, execute count, rows processed, and elapsed time per call phase (parse, execute, fetch). Equivalent to enabling SQL_TRACE.
  • Level 4 — level 1 plus the actual bind variable values passed at execution time. Required when a statement uses bind variables and the plan or performance differs from expectations.
  • Level 8 — level 1 plus wait events: every wait the session experiences, named by event, with duration in microseconds. Essential for any diagnosis involving I/O, latches, or lock contention.
  • Level 12 — levels 4 and 8 together: SQL text, bind variable values, and wait events in one file. This is the standard level for production SQL performance diagnosis.

Three mechanisms can enable the trace. ALTER SESSION works for the current session only and is the simplest form. ORADEBUG, run as SYSDBA, targets any Oracle server process — including background processes like LGWR and SMON — by attaching to its OS-level process ID. DBMS_MONITOR.SESSION_TRACE_ENABLE, available from Oracle 10g onwards, targets any user session by SID and SERIAL# without requiring SYSDBA privilege and is the preferred packaged API for targeting another session. This post covers all three, the queries that locate the trace file once tracing is complete, and the tkprof command that converts raw trace output into a readable per-SQL report.

Code

 1-- ─── METHOD 1: ALTER SESSION (current session only) ─────────────────────────
 2
 3-- Level 12: SQL text + bind variables + wait events (most complete form)
 4ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
 5
 6-- Run the SQL statements you want to trace here, then disable:
 7ALTER SESSION SET EVENTS '10046 trace name context off';
 8
 9-- ─── METHOD 2: ORADEBUG (any session or background process, requires SYSDBA) ─
10
11-- Step 1: retrieve the OS process ID (SPID) of the target session
12SELECT s.sid,
13       s.serial#,
14       s.username,
15       s.status,
16       p.spid AS os_pid,
17       p.tracefile
18FROM   v$session s
19       JOIN v$process p ON p.addr = s.paddr
20WHERE  s.username = 'APPUSER'   -- filter by username, SID, or other criteria
21  AND  s.type     = 'USER';
22
23-- Step 2: attach ORADEBUG to the OS PID and enable the trace (run as SYSDBA)
24ORADEBUG SETOSPID 12345
25ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
26
27-- Step 3: after the relevant SQL has executed, disable and close the trace
28ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF
29ORADEBUG CLOSE_TRACE
30
31-- ─── METHOD 3: DBMS_MONITOR (preferred API, Oracle 10g and later) ────────────
32
33-- Enable trace for a target session identified by SID and SERIAL#
34EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(
35       session_id  => 142,      -- SID from V$SESSION
36       serial_num  => 8821,     -- SERIAL# from V$SESSION
37       waits       => TRUE,     -- enables wait event capture (level 8 component)
38       binds       => TRUE);    -- enables bind variable capture (level 4 component)
39
40-- Disable when done
41EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(
42       session_id  => 142,
43       serial_num  => 8821);
44
45-- ─── FIND THE TRACE FILE (Oracle 11g and later) ──────────────────────────────
46
47-- V$PROCESS.TRACEFILE holds the full path for each server process
48SELECT s.sid,
49       s.serial#,
50       s.username,
51       p.spid     AS os_pid,
52       p.tracefile
53FROM   v$session s
54       JOIN v$process p ON p.addr = s.paddr
55WHERE  s.sid = 142;
56
57-- ADR trace directory and the current session's default trace file path
58SELECT name, value
59FROM   v$diag_info
60WHERE  name IN ('Diag Trace', 'Default Trace File');
61
62-- Pre-11g: trace files land in USER_DUMP_DEST; filename = <instance>_ora_<spid>.trc
63SHOW PARAMETER user_dump_dest

1# Convert the raw trace file to a readable report with tkprof
2# Sort by elapsed time summed across parse + execute + fetch calls
3tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12345.trc \
4       /tmp/trace_report.txt \
5       explain=appuser/password \
6       sort=prsela,exeela,fchela

Code Breakdown

Method 1: ALTER SESSION

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12' enables the trace at level 12 for the session that issues it. The clause context forever means the trace remains active until explicitly disabled — it does not reset between statement executions. context off disables the trace and flushes any buffered trace data to disk.

This is the simplest form and the right choice when you can reach the session directly: reproducing a problem interactively in your own SQL*Plus connection, or calling the statement from inside the application code path during a test. The restriction is scope — it applies only to the issuing session. Tracing another user's already-running session requires Method 2 or Method 3.

Method 2: ORADEBUG

ORADEBUG SETOSPID connects the current SYSDBA session to another Oracle server process by its OS-level process ID. That PID comes from V$PROCESS.SPID — not the Oracle SID. Once attached, every subsequent ORADEBUG EVENT command targets the foreign process rather than the DBA's own session. This allows tracing of any connected session and, critically, of Oracle background processes such as SMON, LGWR, or DBWn, which have no user session and cannot be reached by ALTER SESSION or DBMS_MONITOR.

The join V$SESSION s JOIN v$process p ON p.addr = s.paddr is the standard way to retrieve the SPID for a known SID. The TRACEFILE column on the same row confirms where the output will land.

After the target SQL has run, issue EVENT 10046 TRACE NAME CONTEXT OFF to disable the event, then CLOSE_TRACE to ensure the file handle is released and the buffer is flushed completely. Skipping CLOSE_TRACE can leave an open file handle on the trace output even after the originating session ends.

Method 3: DBMS_MONITOR

DBMS_MONITOR.SESSION_TRACE_ENABLE is the packaged API Oracle introduced in 10g as the structured alternative to raw event syntax. It accepts the target session's SID and SERIAL# from V$SESSION, along with boolean flags: waits => TRUE activates wait event capture (the level 8 component) and binds => TRUE activates bind variable capture (the level 4 component). Setting both to TRUE is functionally equivalent to level 12.

The SERIAL# argument is what distinguishes this method: Oracle reuses SID values after a session disconnects. Because the API matches on both SID and SERIAL# together, it cannot accidentally enable a trace on a new session that has inherited an old SID. The disable call, SESSION_TRACE_DISABLE, uses the same two arguments and cleanly ends the trace.

DBMS_MONITOR does not require SYSDBA. The executing user needs EXECUTE on the package, which is granted through the DBA role by default. This makes it the preferred method for a team where the DBA enabling the trace is not the same individual who holds SYSDBA access.

Finding the trace file on Oracle 11g and later

Oracle 11g introduced the Automatic Diagnostic Repository (ADR) and placed all diagnostic output — including SQL trace files — under the directory defined by the DIAGNOSTIC_DEST parameter. The V$PROCESS.TRACEFILE column holds the fully qualified path to each server process's trace file. Joining V$SESSION to V$PROCESS on p.addr = s.paddr for the target SID returns the exact path without any manual directory construction.

V$DIAG_INFO provides the directory-level view. The Diag Trace row returns the trace directory path; the Default Trace File row returns the file path that the current session's own trace output would use. These are useful when the TRACEFILE column is empty because the target process has not yet written any trace data.

On Oracle 10g and earlier, before ADR existed, trace files landed in the directory reported by user_dump_dest. The filename format was <instance_name>_ora_<spid>.trc where <spid> is the OS process ID from V$PROCESS.SPID.

tkprof

The raw .trc file records every parse, execute, and fetch call in chronological order with microsecond timestamps. For a session that executed hundreds of cursors, reading the raw file directly is impractical. tkprof aggregates the data into a per-SQL summary showing rows processed, elapsed time, CPU time, buffer gets, and disk reads for each call phase.

The sort=prsela,exeela,fchela argument orders the report by descending elapsed time summed across all three call phases, placing the worst-performing SQL at the top. The explain= argument runs a live EXPLAIN PLAN against each statement using the specified credentials and appends the execution plan to each entry. Use a user who has access to the traced objects so the plans are accurate.

Key Points

  • Level 12 is the standard diagnostic level. Levels 1 and 4 are appropriate in specific situations — high-volume sessions where wait event output would be prohibitively large, or static SQL where bind values are not relevant — but for general SQL performance diagnosis, all three dimensions (SQL, binds, waits) are needed together.
  • Each method has a different scope. ALTER SESSION reaches only the issuing session; ORADEBUG reaches any Oracle process including background processes; DBMS_MONITOR reaches any user session by SID and SERIAL# without requiring SYSDBA.
  • V$PROCESS.TRACEFILE is the reliable file locator on 11g+. Directory-parsing approaches break when a non-default DIAGNOSTIC_DEST is configured; the column delivers the exact path regardless of configuration.
  • Trace files grow without a size cap. A busy OLTP session at level 12 can generate hundreds of megabytes per minute. Always plan the disable step before enabling.
  • SERIAL# prevents false matches. Oracle reuses SID values. Supplying the SERIAL# to DBMS_MONITOR ensures the trace targets the intended session and not a replacement session that reused the same SID after a disconnect.
  • ORADEBUG is the only path to background processes. DBMS_MONITOR and ALTER SESSION work only on user sessions. LGWR, DBWn, SMON, and other background process traces require ORADEBUG SETOSPID with the PID from V$PROCESS.

Insights and Best Practices

Match the method to who owns the session

The method decision follows from access, not preference. Own session and interactive access: ALTER SESSION. Another user's live application session without SYSDBA: DBMS_MONITOR using SID and SERIAL# from V$SESSION. Background process or a release before 10g: ORADEBUG. Using ORADEBUG for user sessions when DBMS_MONITOR is available adds unnecessary SYSDBA dependency and offers no benefit.

Prepare the disable command before enabling the trace

Have the disable command staged and ready to execute before issuing any enable. For ALTER SESSION, type the context off variant in a second buffer window. For DBMS_MONITOR, fill in the SESSION_TRACE_DISABLE call with the correct SID and SERIAL# before the enable runs. If a terminal disconnects or an alert fires mid-trace, the file will grow uncontrolled until the session ends. Having the disable ready means it can be issued immediately from any tool or by another team member.

Estimate file growth before enabling on a production session

A level 12 trace on a session processing thousands of rows per second can fill the ADR trace directory in minutes. Before enabling, check available disk space on the DIAGNOSTIC_DEST mount with df -h and look at the session's current execution rate via V$SESSTAT (statistic execute count). For high-volume OLTP sessions, a five to thirty second trace window is usually sufficient to capture the problem cursor. Longer windows are appropriate only for slow batch processes with sparse SQL execution.

Use TRACEFILE_IDENTIFIER to make the file findable on busy instances

On an instance where many sessions are generating trace output simultaneously, locating one specific file among dozens can be difficult. If you have access to the target session — for example, when enabling the trace on your own connection — run ALTER SESSION SET TRACEFILE_IDENTIFIER = 'LABEL' before enabling the event. Oracle appends the identifier string to the trace filename, making it immediately distinguishable from the surrounding files in the ADR trace directory.

Read tkprof output top-down, then verify with the raw file

The sort=prsela,exeela,fchela sort order surfaces the statements that consumed the most elapsed time over the trace window. Use that as the triage list — identify the top two or three offenders before reading anything else. Once the worst statement is identified, open the raw .trc file and read the wait event lines around that cursor's EXEC and FETCH markers. tkprof summarises waits across all executions of a cursor; the raw file shows the sequence — whether waits clustered at the first execution, at a specific fetch boundary, or were uniformly distributed. That distinction often changes the root cause.

Confirm the trace is active before walking away

After enabling any of the three methods, verify the trace took effect. For DBMS_MONITOR, query SELECT sql_trace FROM v$session WHERE sid = <sid> — the value should be ENABLED. For ORADEBUG, the response Statement processed. after the EVENT command confirms acceptance. For ALTER SESSION, the absence of an ORA error is sufficient. If the trace is not confirmed active and the session runs through the problem SQL without tracing, the diagnostic window is wasted.

When to Use This

  • Diagnosing a SQL statement that is slow for one user but fast for another, where bind variable capture is needed to compare the values driving each execution.
  • Investigating a long-running batch operation where AWR shows elevated wait events at the instance level but does not identify the specific session or statement responsible.
  • Capturing a complete wait event profile for a session during a known slow period to share with a developer or to submit to Oracle Support as an attached .trc file.
  • Tracing a background process — SMON during space reclamation, LGWR during a redo write bottleneck — where no user session interface exists.
  • Reproducing a reported slow query interactively by enabling the trace in your own session before running the identical code path.
  • Pre-upgrade and post-upgrade comparison: capturing full execution traces before and after a major database version change to compare wait profiles and execution plans for critical workload statements.

Troubleshooting Common Issues

Trace file exists but contains no SQL or wait events. The trace was enabled after the relevant SQL had already executed, or disabled before any SQL ran. Re-enable the trace before triggering the code path, confirm the session is still active with SELECT status FROM v$session WHERE sid = <sid>, and disable only after the SQL of interest has completed.

ORADEBUG returns ORA-00073 or does not attach. The SETOSPID step and the EVENT command must run in the same SYSDBA session. If the DBA session was reconnected between the two commands, reissue SETOSPID before EVENT. Confirm the PID value is from V$PROCESS.SPID — an OS process ID — not from Oracle's SID column.

DBMS_MONITOR.SESSION_TRACE_ENABLE raises ORA-13854: cannot enable SQL tracing for background process. The DBMS_MONITOR API does not support background processes. Use ORADEBUG SETOSPID with the SPID of the background process retrieved from V$PROCESS.

V$PROCESS.TRACEFILE is blank or the column does not exist. The column was introduced in Oracle 11g. On 10g and earlier, use SHOW PARAMETER user_dump_dest for the directory and construct the filename as <instance_name>_ora_<spid>.trc using the SPID from the V$PROCESS join query.

The trace file is very large and still growing after the session ended. A trace left open by ORADEBUG without CLOSE_TRACE can hold a file handle beyond the life of the originating session on some platforms. Run ORADEBUG CLOSE_TRACE in the same DBA session to release it. If the DBA session was also closed, the file handle will be released at the next instance bounce or when the OS reclaims orphaned handles — compressing the file immediately limits further space consumption.

References

Posts in this series