for Analyzing SQL execution
There are several utilities for analyzing Oracle trace files.
These include trace assist (trcasst), session tracer (trcsess),
trace analyzer (trcanlzr.sql) and tkprof. Many DBAs are very
familiar with the Oracle trace facility, but just in case, here are
some brief instructions for using this powerful Oracle utility.
- TKPROF: Tkprof does not control the
contents of a trace file, it simply formats them. Oracle
provides multiple ways to actually generate the trace file.
Tkprof is valuable for detailed trace file analysis. For those
DBAs that prefer a simpler tracing mechanism with instant
feedback, the autotrace utility should be used. You do a
detailed dump using the TKPROF utility and setting
sql_trace=true is a prerequisite, but there are many other
steps and you will also need special scripts to analyze the
- trcasst with sql_trace: The trace
assist (trcasst) utility is used to analyze Oracle trace files
generated by most Oracle error messages. This utility will
analyze the trace file and put it into a readable format. The
SQL Trace facility is not used much anymore and I recommend it
only if you need a detailed dump, i.e. when the SQL returns
Before sql_trace tracing can be enabled, the
environment must first be configured by performing the following
- 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: meaningful with these statistics. The command to enable
timed statistics is:
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.
SQL> select value
from v$parameter where name = 'user_dump_dest';
Next, delete elderly ND unwanted
trace files before starting a new trace to free up the disk
- Turn on sql_trace: The next step in the process
is to enable tracing. By default, tracing is disabled due to the
burden (5-10%) it places on the database. Tracing can be defined
at the session level by setting sql_trace equal to
alter session set sql_trace = true;
A DBA may enable sql_trace for tracing for
another user's session by using the following statement using
Note that the SID (Session ID) and serial# parameters for
set_sql_trace_in_session can be obtained from the v$session
view. Also note that the dbms_system.set_sql_trace_in_session
procedure is owned by the SYS user and therefore the executor
must be SYS or be granted the EXECUTE privilege by SYS user. Once
tracing with Oracle tkprof is enabled via sql_trace, Oracle
generates and stores the statistics in the trace file directory as a
Tips for using sql_trace:
- sql_trace is rarely
required: Enabling sql_trace and tkprof
tracing only on those sessions that have already been examined
with autotrace and explain plan. In over 90% of cases, a DBA can
find a SQL problem by using STATSPACK (or AWR) SQL historical
data, or by using autotrace.
- Beware of sql_trace
execution volume: When tracing a session with
sql_trace, remember that nothing in v$session
indicates that a session is being traced and the trace file can
grow to gigabytes in minutes for large SQL statements.
Therefore, trace with caution and remember to disable tracing
after an adequate amount of trace data has been generated.
Related sql_trace topics:
See these links for more details on
See these notes on using
TKPROF with sql_trace.
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.