Old blog from Antognini regarding trace levels (10046)

Link to the blog: http://antognini.ch/2012/08/event-10046-full-list-of-levels/

Available levels:

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.Up to 10.2 an execution plan is written to the trace file only when the cursor it is associated with is closed. The execution statistics associated to it are values aggregated over all executions.As of 11.1 an execution plan is written to the trace file only after the first execution of every cursor. The execution statistics associated to it are the ones of 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 plans information is written to the trace file for each execution. Available as of 11.1 only.
32 As in level 1, but without the execution plans information. Available as of 11.1 only.
64 As in level 1, plus the execution plans information might be written for executions following the first one. The condition is that, since the last write of execution plans information, a particular cursor consumed at least one additional minute of DB time. This level is interesting in two cases. First, when the information about the first execution is not enough for analysing a specific issue. Second, when the overhead of writing the information about every execution (level 16) is too high. Generally available as of 11.2.0.2 only.

In addition to the levels described in the previous table, you can also combine the levels 4 and 8 with every other level greater than 1. For example:

Level 12 (4 + 8): simultaneously enable level 4 and level 8.
Level 28 (4 + 8 + 16): simultaneously enable level 4, level 8 and level 16.
Level 68 (4 + 64): simultaneously enable level 4 and level 64.

If you are using dbms_monitor or dbms_session for enabling extended SQL trace, here is the mapping between the levels and the parameters:

Level 4: waits=FALSE, binds=TRUE, plan_stat=’first_execution’
Level 8: waits=TRUE, binds=FALSE, plan_stat=’first_execution’
Level 16: waits=FALSE, binds=FALSE, plan_stat=’all_executions’
Level 32: waits=FALSE, binds=FALSE, plan_stat=’never’
Level 64: not available yet

As you can see from the previous list, it is not possible to enable level 64 through dbms_monitor and dbms_session. Hence, statements like the following ones or ORADEBUG have to be used:

alter session set events ‘10046 trace name context forever, level 64’

alter session set events ‘sql_trace wait=false, bind=false, plan_stat=adaptive’

Post a Comment

Your email is never published nor shared. Required fields are marked *