Tracing Sessions with Event 10046

10046 Event is used to trace SQL statements (similar to sql_trace=true) with additional details depending on the tracing level which is specified.

10046 EVENT levels:

1 - Enable standard SQL_TRACE functionality (Default)
4 - As Level 1 PLUS trace bind values
8 - As Level 1 PLUS trace waits
12 - As Level 1 PLUS both trace bind values and waits

There are various ways to enable the tracing

1) Using Alter session command

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

To Turn it off
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

2) Using DBMS_SESSION

EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);


The TRUE argument specifies that wait information will be present in the trace. The FALSE argument specifies that bind information will not be present in the trace.
The SESSION_TRACE_DISABLE procedure disables the trace for the invoking session.

For example:
EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE();

3) Using DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION

EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,TRUE);

Where SID and SERIAL# points to the session being traced and can be retrieved from V$SESSION

To turn it off
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,FALSE);

4) Using DBMS_MONITOR

Starting from Oracle 10g, there is a new package which can be used to enable tracing.

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID=>sid,SERIAL_NUM=>serial#,WAITS=>TRUE,BINDS=>TRUE)

To turn it off

EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => sid, serial_num => serial#);

Please refer to following documentation link for more details

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#i8759

5) Using ORADEBUG

connect / as sysdba
Sql>oradebug setospid 1234
Sql>oradebug unlimit
Sql>oradebug event 10046 trace name context forever,level 12

Here 1234 is the OS process Id for the session to be traced.

Sql>oradebug tracefile_name

Above command will show the location of the trace file

To disable oradebug tracing

Sql> oradebug event 10046 trace name context off

Whichever method you use, Oracle will create the trace files in the server's user_dump_dest directory.

Leave a Reply