Oracle Database Session Tracing Using Event 10046

 Tracing is one of the most powerful techniques available in Oracle Database for diagnosing SQL performance issues, wait events, execution behavior, and session activity.

The 10046 trace event is commonly used to collect detailed SQL trace information, including:

• SQL execution statistics
• Wait events
• Bind variables
• Execution timings

This article covers multiple ways to enable 10046 tracing in Oracle Database.

---

1. Session Level Tracing

Use this method when you want to trace only the current database session.

ALTER SESSION SET tracefile_identifier='10046';

ALTER SESSION SET timed_statistics = TRUE;

ALTER SESSION SET statistics_level = ALL;

ALTER SESSION SET max_dump_file_size = UNLIMITED;

ALTER SESSION SET events '10046 trace name context forever, level 12';

Now execute the SQL statements or operations you want to trace:

SELECT * FROM dual;

Disable tracing after completing the test:

ALTER SESSION SET events '10046 trace name context off';

Understanding Level 12

Level 12 tracing includes:

• SQL Statements
• Wait Events
• Bind Variables

This is the most commonly used level for SQL performance diagnostics.

---

2. Tracing an Existing Running Session

Sometimes the target session is already running, and you need to attach tracing dynamically.

Step 1: Identify the Session and Process

SELECT p.pid,
       p.spid,
       s.sid
FROM   v$process p,
       v$session s
WHERE  s.paddr = p.addr
AND    s.sid = &SESSION_ID;

Important Columns

Column	Description
SPID	Operating System Process ID
PID	Oracle Process ID
SID	Oracle Session ID

---

Step 2: Enable Trace Using ORADEBUG

Connect as SYSDBA:

CONNECT / AS SYSDBA;

Attach to the operating system process:

ORADEBUG SETOSPID 9834;

Remove trace size limitation:

ORADEBUG UNLIMIT;

Enable tracing:

ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12;

Disable tracing after collecting required diagnostics:

ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;

---

3. Instance-Wide Tracing

This enables tracing across the entire database instance.

⚠️ Use carefully in production environments because it can generate very large trace files and impact performance.

Enable

ALTER SYSTEM SET events '10046 trace name context forever, level 12';

Disable

ALTER SYSTEM SET events '10046 trace name context off';

---

4. Enabling Trace Through a Logon Trigger

This method automatically enables tracing whenever a specific user logs in.

CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER LIKE '&USERNAME')
DECLARE
BEGIN
    EXECUTE IMMEDIATE
        'alter session set tracefile_identifier=''From_Trigger''';

    EXECUTE IMMEDIATE
        'alter session set statistics_level=ALL';

    EXECUTE IMMEDIATE
        'alter session set max_dump_file_size=UNLIMITED';

    EXECUTE IMMEDIATE
        'alter session set events ''10046 trace name context forever, level 12''';
END set_trace;
/

This is particularly useful for:

• Intermittent issues
• Application troubleshooting
• Capturing trace automatically during login

---

Best Practices

• Prefer session-level tracing whenever possible
• Avoid instance-wide tracing on busy production systems
• Always disable tracing after diagnostics collection
• Use meaningful tracefile_identifier values for easier trace file identification
• Combine tracing with TKPROF for better analysis

---

Reference

• How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues
  (Doc ID 376442.1)