Trace Files -- 1 : Generating SQL Traces (own session)

Beginning a new series of posts on Trace Files.


An SQL Trace captures SQL statements (including recursive SQL calls -- e.g. data dictionary lookups or triggers being executed, which are not "directly" visible to the client or explicitly executed by the client).   Optionally, it can capture Wait Events and Binds.

Tracing for Wait Events allows us to capture the Wait Events that occur within the duration of an SQL call.  Tracing for Binds allows us to capture the Bind Values that were passed by the Client (where the SQL code uses Bind Variables instead of Literals)


Here are a few methods to get the SQL Trace for one's own session (whether interactively in sqlplus or programmatically through any other client)


EVENT 10046
This is not recommended by Oracle although it seems to be widely in use.  Use with extreme caution as specifying the wrong event number or level when issuing an ALTER SESSION command can result in unpredictable behaviour and possible corruption.  I include it here not as a recommendation but only because I anticipate that I will be asked about this.  I do NOT recommend using this method.  (Levels 16 and 32 have not been tested by me)

This method is used with either of these commands :

ALTER SESSION SET EVENTS '10046 trace name context forever, level 1'; -- for the same behaviour as SQL_TRACE=TRUE
ALTER SESSION SET EVENTS '10046 trace name context forever, level 4'; -- for tracing Binds with the SQLs
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8'; -- for tracing Wait Events with the SQLs
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; -- for tracing both Binds and Wait Events with the SQLs
ALTER SESSION SET EVENTS '10046 trace name context forever, level 16'; -- to dump STAT lines for each execution  
ALTER SESSION SET EVENTS '10046 trace name context forever, level 32'; -- never dump execution statistics

After executing the desired SQLs to be traced, tracing is disabled with :

ALTER SESSION SET EVENTS '10046 trace name context off';

(I have updated the "context forever, level 0" to "context off"  which is the correct method.  This just shows that I haven't used this method of tracing for a long time !)

SQL_TRACE
The instance / session parameter SQL_TRACE is the oldest method and may still be in use although it has been deprecated in recent versions.

This is done with the SQL command :

ALTER SESSION SET SQL_TRACE=TRUE;

After executing the SQLs that need to be traced, it is then disabled with the SQL command :

ALTER SESSION SET SQL_TRACE=FALSE;

DBMS_SESSION.SET_SQL_TRACE
This PLSQL procedure is the proper alternative to setting the parameter SQL_TRACE.

This is done with

exec DBMS_SESSION.SET_SQL_TRACE(sql_trace=>TRUE);

The EXEC call allows the PLSQL procedure to be executed from the command line.

After executing the SQLs that need to be traced, it is then disabled with the command :

exec DBMS_SESSION.SET_SQL_TRACE(sql_trace=>FALSE);

DBMS_SESSION.SESSION_TRACE_ENABLE
This PLSQL procedure is the preferred method.  It also offers switches to enable/disable tracing for Wait Events and Binds separately.

This is done with

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

Thus, the switch for tracing Waits is active but tracing Binds is deactivated.  (Optionally, Binds could also be traced with the binds switch set to TRUE).

After executing the target SQLs in the session, it is then disabled with the command :

exec DBMS_SESSION.SESSION_TRACE_DISABLE;

Thus, no flags need to be supplied to disable both Waits and Binds.

.
.
.