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.


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.