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 Tips by Burleson Consulting
Don Burleson

February 2, 2015


TKPROF is an acronym for the Transient Kernel PROFile, set via the sql_trace parameter, used to format a trace file into readable form.

In addition to TKPROF, see here for using trace analyzer to trace SQL and complete directions for using TKPROF in the book "Advanced Oracle Utilities":

Analyzing TKPROF Results

This is from the book Oracle Utilities: Using Hidden Programs, Import/Export, SQL Loader, oradebug, Dbverify, Tkprof and More Order now and receive immediate access to the Online Code Depot!

So what should DBAs be looking for? Here's a small checklist of items to watch for in TKPROF formatted files:

  • Compare the number of parses to number of executions. A well-tuned system will have one parse per n executions of a statement and will eliminate the re-parsing of the same statement.
  • Search for SQL statements that do not use bind variables (:variable). These statements should be modified to use bind variables.
  • Identify those statements that perform full table scans, multiple disk reads, and high CPU consumption. These performance benchmarks are defined by the DBA and need to be tailored to each database. What may be considered a high number of disk reads for an OLTP application may not even be minimal for a data warehouse implementation.

Uses for the TKPROF utility

The TKPROF utility can be used for the following:

  • Compare the number of parses to number of executions.
  • Search for SQL statements that do not use bind variables (:variable). These statements should be modified to use bind variables.
  • Identify those statements that perform full table scans, multiple disk reads, and high CPU consumption.

The Oracle TKPROF utility will be explained below:

Step 1: Check the Environment

Before tracing using TKPROF can be enabled, 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:

  • 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.

Step 2: Turn Tracing On

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:



A DBA may enable tracing for another user's session by using the following statement:


The sid (Session ID) and serial# can be obtained from the v$session view. Once tracing with Oracle TKPROF is enabled, Oracle generates and stores the statistics in the trace file. The TKPROF trace file name is version specific.

  • Enable Oracle TKPROF tracing only on those sessions that are having problems.

  • Explain Plan is not as useful when used in conjunction with TKPROF since the trace file contains the actual execution path of the SQL statement. Use Explain Plan when anticipated execution statistics are desired without actually executing the statement.
  • When tracing a session with TKPROF, remember that nothing in v$session indicates that a session is being traced. Therefore, trace with caution and remember to disable tracing after an adequate amount of trace data has been generated.
  • 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.

Get the Complete
Oracle Utility Information 

The landmark book "Advanced Oracle Utilities The Definitive Reference"  contains over 600 pages of filled with valuable information on Oracle's secret utilities. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


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.