Various Ways to SQL_Trace

Various Ways to SQL_Trace

 

SQL_TRACE enables the logging of all the SQL your application performs, performance statistics regarding the execution of that SQL, and the query plans your SQL actually used.

Check the Environment

Before tracing using SQL_TRACE, the environment must first be configured by performing the following steps:

•Enable Timed Statistics – This parameter enables the collection of certain vital statistics such as CPU execution time, wait events, and elapsed times. The resulting trace output is more meaningful with these statistics. The command to enable timed statistics is:

ALTER SESSION SET TIMED_STATISTICS = TRUE; OR
ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

•Check the User Dump Destination Directory – The trace files generated by Oracle can be numerous and large. These files are placed by Oracle in the user_dump_dest directory as specified in the init.ora. The user dump destination can also be specified for a single session using the alter session command. Make sure that enough space exists on the device to support the number of trace files that you expect to generate.

You can ran a query to get your SPID, (server process ID) – this is very useful in identifying the trace file. Finally you can get the trace file in the directory on the database server specified by the USER_DUMP_DEST init.ora parameter.
You can retrieve the value of this parameter online by querying the V$PARAMETER view or by issuing show parameter dump at sql prompt.

The query is as follows:

select a.spid
from v$process a, v$session b
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
/

Turn Tracing On

The next step in the process is to enable tracing. By default, tracing is disabled due to the burden it places on the database.

What are the various ways to enable SQL_TRACE? There are quite a few, but the following are some of them:

• ALTER SESSION SET SQL_TRACE=TRUE|FALSE – Executing this SQL will enable the default mode of SQL_TRACE in the current session. This is most useful in an interactive environment such as SQL*PLUS or embedded in an application so that the application, may turn SQL_TRACE on and off at will. It is a nice feature in all applications, as it would allow you to turn SQL_TRACE on and off for the application via a command line switch, menu selection, parameter, and so on, easily.

ALTER SESSION SET SQL_TRACE=TRUE [Turn on the SQL TRACE]
ALTER SESSION SET SQL_TRACE=FALSE [Turn off the SQL TRACE]

• SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION – This packaged procedure allows us to set SQL_TRACE on and off for any existing session in the database. All we need to do is identify the SID and SERIAL# for the session, this is available in the dynamic performance view V$SESSION.

Syntax:
exec sys.dbms_system.set_sql_trace_in_session( sid_here, serial#_here, TRUE);

So, if you get the SID, SERIAL# of a session from v$session you can turn on tracing using dbms_system. You will need execute privilege on it of course (if you are not sys) and since there is no public synonym you'll need to refer to it by its fully qualified name. So, a call such as:

Sql prompt> exec sys.dbms_system.set_sql_trace_in_session( 46, 13217, TRUE );

PL/SQL procedure successfully completed.

would enable tracing in that session.

AND

Sql prompt> exec sys.dbms_system.set_sql_trace_in_session( 46, 13217, FALSE);

PL/SQL procedure successfully completed.

would disable tracing in that session.

• ALTER SESSION SET EVENTS – We can set an event to enable tracing with more information than is normally available via ALTER SESSION SET SQL_TRACE=TRUE. The SET EVENTS approach is not documented or supported by Oracle Corporation, however its existence is generally available knowledge (go to http://www.google.com/ and search for alter session set events 10046 to see the many web sites that document this feature). Using this event we can not only get everything that SQL_TRACE tells us but we can also see the values of bind variables used by our SQL as well as the wait events (what slowed us down) for our SQL as well.


alter session set events '10046 trace name context forever, level <N>';
alter session set events '10046 trace name context off';
Where N is one of the following values:
•    N=1 – Enable the standard SQL_TRACE facility. This is no different than setting SQL_TRACE=true.
•    N=4 – Enable standard SQL_TRACE but also capture bind variable values in the trace file.
•    N=8 – Enable standard SQL_TRACE but also capture wait events at the query level into the trace file.
•    N=12 – Enable standard SQL_TRACE and include both bind variables and waits.


ILLUSTRATIVE EXAMPLE

alter session set timed_statistics=true;

show parameter dump

select a.spid
from v$process a, v$session b
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
/

alter session set events '10046 trace name context forever, level <N>';

YOUR_QUERIE(s) HERE …………..

alter session set events '10046 trace name context off';

disconn

exit

--- Go to the user_dump_dest directory or wherever your trace file is. If possible you can read the trace file directly or can use the TKPROF utility to get the trace file output in a human readable format.