BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










The TKPROF Utility

Oracle Tips by Burleson Consulting

The TKPROF utility is also known as the SQL trace facility. In addition to the execution plan, TKPROF provides a detailed report that shows the execution details for the SQL statement. Let’s take a look at how TKPROF works.

Set the Environment for SQL Tracing

To enable TKPROF, you must set several Oracle initialization parameters and then turn on tracing with the alter session set sql_trace=true command. The following parameters need to be set up to get a SQL trace file.

  • sql_trace This parameter can be set at the instance level or at the session level. To set SQL trace for whole instances, add the following to your initialization file. This can create a huge amount of data in the Oracle user_dump_dest directory, and the system-wide option is rarely used because of the large amount of data generated.


To enable SQL trace at the session level, the following command can be entered from SQL*Plus:

alter session set sql_trace=true;

  •  timed_statistics The timed statistics parameter allows for the computation of SQL statistics such as CPU usage and elapsed time. This can be set in three ways. For instance-wide statistics, the following initialization parameter can be set:


For database-wide statistics after the instance is started, you can use the alter system command to turn on timed statistics:

alter system set timed_statistics=true;

At the session level, time statistics can be set in SQL*Plus with the following command:

alter session set timed_statistics=true;

  • user_dump_dest This initialization parameter specifies the location of the trace files. You will need to know the location of the trace files to create your TKPROF report

  • max_dump_file_size This parameter must be set high enough to allow the complete trace file for a complex SQL statement. 

Note: You must ensure that the ORACLE account that runs the query has a plan table by running utlxplan.sql when connected as the schema owner.  You must also be connected as the schema owner user ID when running access.sql.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter