SQL Trace

Another method of collecting SQL statement execution plans is to use the SQL Trace facility. The SQL Trace facility will start tracing your entire session until the session ends or until you turn the trace off. All SQL statements will be logged into a trace file. This trace file can be found in the directory on your database server specified by your USER_DUMP_DEST initialization parameter.

The benefit of the SQL Trace facility is that all activity will be logged to a trace file for future analysis. The trace file will contain all SQL statements issued by your application while tracing has been turned on. It will also contain all recursive SQL statements issued on your behalf to satisfy your queries. This is a great way to see what is going on behind the scenes. The trace file will contain statistics on the execution of each query along with the execution plan used specifically at that time the query was run. There are occasions where the execution plan from EXPLAIN PLAN is not the execution plan that was used when the statement was run. The SQL Trace facility is one way to see the precise execution plan in use.

To start tracing use the ALTER SESSION statement as shown below:

SQL> alter session set sql_trace=true;

Session altered.

Similarly, to turn off tracing set the same parameter to FALSE.

SQL> alter session set sql_trace=false;

Session altered.

Tracing will also end when a session terminates.

With the ALTER SESSION command, you can only start and stop tracing in your session. Many times, it is helpful if the DBA can start tracing in another user's session. The first thing the DBA needs to do is to identify the session in V$SESSION and determine the SID and SERIAL# of that session. Once those values are known, the DBA can start the SQL Trace in another user's session with the DBMS_SYSTEM supplied package, as can be seen below:

exec dbms_system.set_sql_trace_in_session(sid,serial#,TRUE);

Make sure to substitute appropriate values for the SID and SERIAL#. To turn off tracing in that session, use the value of FALSE for the third parameter.

exec dbms_system.set_sql_trace_in_session(sid,serial#,FALSE);

Note that the DBMS_SYSTEM supplied package is owned by SYS and typically does not have a public synonym.

It can be difficult to identify the trace file for your session. To assist you, you can modify your session with the following command:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'my_trace_id';

Your session's trace file will have my_trace_id in the file name. This option is not available with the DBMS_SYSTEM package.

10046 Event

There are numerous events that can be set in the Oracle database. Specific to this discussion, there is the 10046 event, which according to Oracle will "enable SQL statement timing". In reality, the 10046 event is SQL tracing. In fact, the default level for the 10046 event is the same as turning on SQL tracing that we've seen above. There are additional levels for the 10046 event that warrant discussion. There are four levels for the 10046 event. The four levels are:

• Level 1 SQL Tracing
   
• Level 4 Tracing with bind variable values
   
• Level 8 Tracing with wait events
   
• Level 12 Tracing with bind variables and wait events (4+8=12)

Level 1 is no different than regular SQL tracing. Level 4 is the same tracing, but values for bind variables are shown in the trace file. If an application is using bind variables (and it should), then regular tracing will not show the values for those bind variables in the trace file. The only way to obtain bind variable values is through the 10046 trace. Level 8 will show wait event information in the trace file. If a SQL statement is taking a long time, it may be beneficial to see what events the session is waiting on. The only way to obtain wait event information in the trace file is through the 10046 trace. Level 12 will dump bind variable values and wait event information in the trace file. The 10046 event can be set in your session with the ALTER SESSION command. An example can be seen below:

alter session set events '10046 trace name context forever, level 4';

To turn off the 10046 trace, set the level to 0 (zero). If you wish to set the 10046 event in another user's session, you will need to use the DBMS_SYSTEM supplied package. The SET_EV procedure can set an event in any session. You will need the SID and SERIAL# of that session from V$SESSION. An example of setting the 10046 event with level 12 can be seen below:

exec dbms_system.set_ev(sid,serial#,10046,12,'');

Make appropriate substitutions for the SID and SERIAL#. To turn off the 10046 trace, run the same command with 0 as the fourth parameter (event level) to the procedure.