René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

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.

Levels

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.

Format of the trace file

See this link.