oracle events 10046,10053 & levels(0,1,4,8,12,16,32,64)

Oracle Events:

Oracle events are assigned a unique number that identifies the event.

There are four different kinds of Oracle event types. These are:

  Process Trace events ? The 10053 and 10046 events are trace events. These trace certain system operations continuously until the tracing is canceled. The results are written to a trace files.

 

  Events that change database behavior  ? These events are designed to modify the behavior of the database. An example is event 10262 that causes the database to stop reporting memory smaller than a defined size.

 

  Immediate ? These events dump diagnostic information. This information is dumped as soon as the event is executed. These includes things like system state dumps and dumps of file headers.

 

On error events -  These events are not unlike immediate events, in that they dump information immediately. This time though, execution of the event is triggered by an error event. The result is a dump file that can be used to discover the cause of the error.

 The 10046 Trace Event

The 10046 event allows you to track, at a very fine level, the execution of a given set of SQL statements. The 10046 event can be set at both the session level (for tracing of a specific session) or at a database level. It can be set either via a parameter file setting, or it can be set dynamically for the database, for your session or even for another session.

 

The 10046 even can also be set to cause the trace output to be generated with differing levels of detail. For example, level 1 tracing is equivalent to the output you get when enabling the SQL_TRACE facility within Oracle (ALTER SESSION SET sql_trace=TRUE).

 

The real bang from the 10046 trace results is in enabling the higher levels of detail. With these higher levels of detail you can see wait related information and bind variable related information. The following table lists the typically used event levels present in the 10046 trace event in Oracle Database 10g:

 

Extended SQL trace (debugging event 10046 at a level higher than 1) is one of the key features provided by Oracle to troubleshoot applications using Oracle Database

For many years the available levels were always the same (4, 8 and 12).

With 11g,  new levels (16 and 32) were introduced.

More recently, with the introduction of the fix for bug 8328200, a new one was added to the list (64).

 

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.

level 1 -  Basic trace level. Like the standard SQL_TRACE trace file. Provides statistics for parse, execute, fetch, commit and rollback database calls.

level 4 - Displays bind variables

level 8 - Displays wait statistics

level 12 - Display both bind variables  & wait statistics  

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.(i.e. Binds info+ waits info)
• 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.

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’