Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle sql_trace Tips

Oracle Database Tips by Donald BurlesonSeptember 30, 2015

Using sql_trace 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 dump!
  • 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 invalid data.

 Before sql_trace tracing can be enabled, the environment must first be configured by performing the following steps:  

  1.  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:

    alter system set timed_statistics = true;  
  2.  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 space.  
  3. 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 true:  

    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 set_sql_trace_in_session:

    dbms_system.set_sql_trace_in_session (sid,serial#,true);  

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 flat file.  

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 sql_trace usage.  

See these notes on using TKPROF with sql_trace.

Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle 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 buy it for 30% off directly from the publisher.




Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster