Tracing using Event 10046

Event 10046 is an well known feature within the Oracle database that generates detailed information on how a statement is parsed, what the values of bind variables are, and what wait events occurred during a particular session . The 10046 event allows us 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 our session or even for another session. 

Event 10046 is quite helpful for DBA to determine exactly, why the database is performing in a certain way, by documenting how a statement is parsed and noting what wait events occured during the statement execution. 

Most of us are well known familiar with Oracle's sql_trace facility, which emits performance information about Oracle parse, execute, fetch, commit, and rollback database calls. Using sql_trace is actually the equivalent of using event 10046 set at level 1. 

The event 10046 is internally described as:

SQL> set serveroutput on 
SQL> begin  
   dbms_output.put_line (SQLERRM (-10046)) ; 
end ; 
/

Event 10046 level 
There are five different levels . The Levels are  as

1.) Level 1  is the default. This level traces all activities until the trace session is stopped. This event enable the standard SQL_TRACE facility (same as SQL_TRACE=TRUE).

2.)  Level 4  provides level 1 tracing and displays the entered value for all bind variables. Bind variables are the values that the user enters. The code displays these bind variables as: b1, etc. When level 4 is activated, the substituted value for the bind variable is displayed in the trace file.

3.) Level 8  provides level 1 tracing and displays a list of all database wait events. Database wait events list the reasons if the elapsed time is greater than the CPU time in the tkprof report.

4.) Level 12  provides level 1 tracing in addition to both bind variable substitution and database wait events.

5.) Level 16  added in 11g to generate STAT line dumps for each execution. STAT dumping has been amended in 11g so that they are not aggregated across all executions but are dumped after execution.

How to enable the event 10046 
The following  steps are required to enable event 10046

1.) Set timed statistics to TRUE
 SQL> alter session set timed_statistics=true ; 
Session altered.

2.) set statistics level
SQL> alter session set statistics_level=all ; 
Session altered.

3.) Set max_dump_file_size to UNLIMITED 
SQL> alter session set max_dump_file_size=unlimited ; 
Session altered.

4.) Enable the event 
SQL> alter session set events '10046 trace name context forever, level 12' ; 

We can check the generated trace file in the directory specified in the parameter user_dump_dest
SQL> show parameter user_dump_dest  

Stop Tracing   
If the session is not exited then the trace can be disabled using :
SQL> alter session set events '10046 trace name context off' ; 

Enabling  tracing in other sessions

We can enable tracing in other session using the oradebug utility .Here the steps to enable the tracing in another session.

1.) Find the SPID of the session which we want to trace 
SQL> select p.PID,p.SPID,s.SID   from v$process p,v$session s
where s.paddr = p.addr  and s.sid = &SESSION_ID ;   
or 
SQL> select spid from v$process 
where addr=(select paddr from v$session where sid=<enter the SID>); 
We can identify the session_id by using the v$session view using the following attribute  sid, serial#, username, osuser, machine ,server ,terminal , program .

2.) Once the OS process id for the process has been determined then login to SQL*Plus as a dba and execute the following 

c:\> sqlplus / as sysdba
SQL> oradebug setospid <SPID> ;    or
SQL> oradebug setorapid <PID> ; 
SQL> oradebug unlimit ; 
SQL> oradebug event 10046 trace name context forever, level 8 ; 


Turn off tracing 
SQL> oradebug event 10046 trace name context off ; 

There are various way of enable, disable and vary the contents of this trace . Here are the some way to enable and disable the tracing .

SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>12, nm=>' ');   <--enable
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');   <--disable

In next post , i will try to cover the topic " Interpreting Trace File" .


Enjoy         :-)