Diagnostic event 10046

Event 10046 can be used to trace SQL statements. Arguably, this is the most important event that can be set.

It is functionally equivalent to setting sql_trace = true. However, it allows to output extra details by setting the level to 4, 8 or 12.

This event can be set with any of the four following levels:

• 1: Standard SQL tracing
• 4: Level 1 + tracing bind variables
• 8: Level 1 + tracing wait events
• 12: Level 4 + Level 8 (SQL tracing, bind vars and wait events)

The following command shows bind variables in the trace file:

alter session set events '10046 trace name context forever, level 4';

After issuing this command, a trace file will be created.

Because event 10046 is somewhat special, there is a dedicated package procedure that allows to set that event: dbms_support.start_trace.

Faster tracing with 10g: Oracle 10g has a new view (v$sql_bind_capture) that captures bind variables faster than tracing them.

See this link.