Database Reference
In-Depth Information
Internally, SQL trace is based on debugging event 10046. Table
3-1
describes the supported levels, which define
the amount of information provided in trace files. When SQL trace is used at a level higher than 1, it's also called
extended SQL trace
.
Table 3-1.
Levels of the Debugging Event 10046
Level
Description
0
The debugging event is disabled.
1
The debugging event is enabled. For each processed database call, the following information is given: SQL
statement, response time, service time, number of processed rows, number of logical reads, number of
physical reads and writes, execution plan, and little additional information.
In version 10.2, an execution plan is written to the trace file only when the cursor it's associated with is
closed. The execution statistics associated with the execution plan are values aggregated over all executions.
As of version 11.1, an execution plan is written to the trace file only after the first execution of every cursor.
The execution statistics associated with the execution plan are thus from the first execution only.
4
As in level 1, with additional information about bind variables. Mainly, the data type, its precision, and the
value used for each execution.
8
As in level 1, plus detailed information about wait time. For each wait experienced during the processing,
the following information is given: the name of the wait event, the duration, and a few additional
parameters identifying the resource that has been waited for.
16
As in level 1, plus the execution plan's information is written to the trace file after each execution.
Available as of version 11.1 only.
32
As in level 1, but without the execution plan's information. Available as of version 11.1 only.
64
As in level 1, plus the execution plan's information might be written for executions following the first
one. The condition is that, since the last write of the execution plan's information, a particular cursor
has consumed at least one additional minute of DB time. This level is useful in two cases. First, when
the information about the first execution isn't enough for analysing a specific issue. Second, when the
overhead of writing the information about every execution (level 16) is too high. Available as of
version 11.2.0.2
1
only.
In addition to the levels described in Table
3-1
, you can also combine levels 4 and 8 with every other level greater
than 1. For example:
Level 12 (4 + 8): simultaneously enables level 4 and level 8.
Level 28 (4 + 8 + 16): simultaneously enables level 4, level 8 and level 16.
Level 68 (4 + 64): simultaneously enables level 4 and level 64.
The next sections describe how to enable and disable SQL trace, how to configure the environment to our best
advantage, and how to find the trace files it generates.
