Overview

The Oracle 10046 event serves as an enhanced extension to the standard SQL Trace functionality. This debugging event provides detailed diagnostic information that proves invaluable during performance troubleshooting sessions. Although not officially documented as a customer-facing tool in Oracle's documentation, it remains one of the most powerful events available for analyzing SQL execution behavior and identifying performance bottlenecks.

Event Levels

The 10046 event supports multiple trace levels, with higher levels incorporating all information from lower levels:

Level	Description
0	Disables tracing (equivalent to SQL_TRACE=FALSE)
1	Enables standard trace statistics (equivalent to SQL_TRACE=TRUE)
2	Same as level 1
4	Level 1 + bind variable tracking
8	Level 1 + wait event tracking
12	Level 4 + Level 8 (combination of bind variables and wait events)
16	Level 1 + execution plan information written on each execution (11g and later)

Level 12 represents the most commonly used configuration, providing comprehensive information for thorough performance analysis.

Session-Level Activation

Before enabling 10046 tracing at the session level, several initialization parameters should be configured:

Parameter Configuration:

ALTER SESSION SET tracefile_identifier = 'TRACE_SESSION_001';
ALTER SESSION SET timed_statistics = true;
ALTER SESSION SET statistics_level = all;
ALTER SESSION SET max_dump_file_size = unlimited;

Enable 10046 Event:

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

Execute Target Statement:

SELECT * FROM employees WHERE department_id = 50;

Disable Tracing:

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

Using ORADEBUG

The ORADEBUG utility functions as a SQL*Plus command-line tool capable of tracing sessions, dumping memory structures, and controlling processes. It requires SYSDBA privileges and can directly display trace file paths.

Identifying Target Process:

First, retrieve the OS process ID and session information:

SELECT 'ospid: ' || p.spid || ' # ''' || s.sid || ',' || s.serial# || ''' ' ||
       s.osuser || ' ' || s.machine || ' ' || s.username || ' ' ||
       s.program AS line
  FROM v$session s
  JOIN v$process p ON p.addr = s.paddr
 WHERE s.username IS NOT NULL;

Sample output:

ospid: 32541 # '145,892' oracle prod HR sqlplus@prodserver (TNS V1-V3)
ospid: 18293 # '67,441' oracle prod SYSTEM sqlplus@prodserver (TNS V1-V3)

Obtain Oracle PID:

SELECT p.PID, p.SPID, s.SID
  FROM v$process p
  JOIN v$session s ON s.paddr = p.addr
 WHERE s.sid = &SESSION_ID;

Establish ORADEBUG Connection:

ORADEBUG setospid 18293;
-- ORADEBUG setorapid 67;

Configure Trace File:

ORADEBUG settracefileid HR_TRACE_OUTPUT;

Enable Tracing:

ORADEBUG event 10046 trace name context forever, level 12;

Execute Test Query:

SELECT * FROM employees WHERE department_id = 50;

Disable Tracing:

ORADEBUG event 10046 trace name context off;
ORADEBUG close_trace;

Retrieve Trace File Location:

ORADEBUG tracefile_name;

Using DBMS_SYSTEM Package

The DBMS_SYSTEM package provides SET_EV procedure for enabling 10046 event tracing:

PROCEDURE SET_EV(
    SI    IN BINARY_INTEGER,  -- SID
    SE    IN BINARY_INTEGER,  -- SERIAL#
    EV    IN BINARY_INTEGER,  -- Event code (10046)
    LE    IN BINARY_INTEGER,  -- Event level
    NM    IN VARCHAR2
);

Enable Tracing:

EXEC dbms_system.set_ev(145,892,10046,12,'');

Disable Tracing:

EXEC dbms_system.set_ev(145,892,10046,0,'');
-- Alternative method:
EXEC dbms_system.set_sql_trace_in_session(145,892,FALSE);

Using DBMS_MONITOR Package

The DBMS_MONITOR package offers SESSION_TRACE_ENABLE and SESSION_TRACE_DISABLE procedures:

Enable Tracing:

EXEC dbms_monitor.session_trace_enable(145,892,TRUE,TRUE,'all_executions');

Disable Tracing:

EXEC dbms_monitor.session_trace_disable(145,892);

Using DBMS_SUPPORT Package

This package requires separate installation from the Oracle support scripts:

Installation:

@$ORACLE_HOME/rdbms/admin/dbmssupp.sql
GRANT EXECUTE ON dbms_support TO required_user;
CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;

Procedure Overview:

PROCEDURE START_TRACE(
    WAITS   BOOLEAN DEFAULT,
    BINDS   BOOLEAN DEFAULT
);

PROCEDURE START_TRACE_IN_SESSION(
    SID     NUMBER,
    SERIAL  NUMBER,
    WAITS   BOOLEAN DEFAULT,
    BINDS   BOOLEAN DEFAULT
);

PROCEDURE STOP_TRACE;
PROCEDURE STOP_TRACE_IN_SESSION(
    SID     NUMBER,
    SERIAL  NUMBER
);

Enable Tracing:

EXEC dbms_support.start_trace_in_session(145,892,TRUE,TRUE);

Disable Tracing:

EXEC dbms_support.stop_trace_in_session(145,892);

Database-Level Activation

Database-level tracing affects all newly created sessions and should be used sparingly due to significant performance impact. This approach proves useful when the problematic session cannot be identified in advance.

Using ALTER SYSTEM:

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

-- To disable:
ALTER SYSTEM SET events '10046 trace name context off';

Using DBMS_MONITOR:

EXEC dbms_monitor.database_trace_enable(TRUE,TRUE,'PROD','all_executions');

-- To disable:
EXEC dbms_monitor.database_trace_disable('PROD');

Additional Considerations

More advanced tracing capabilities exist, including multi-session tracking and filtering by SQL_ID, which provide finer granularity for specific problem analysis. The resulting trace files can be formatted using the TKPROF utility to improve readability and facilitate interpretation of the collected data.