Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Tips


Gather detailed optimizer statistics for an SQL query


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.


1 - The sql_trace 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;


2 - 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;


3 - The user_dump_dest  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


4 - The max_dump_file_size  parameter must be set high enough to allow the complete trace file for a complex SQL statement.


Generating the SQL Trace File


Once the four steps above are complete, you will get a trace file for all SQL commands issued by your session. This trace file is known as a level-1 trace.


Oracle will generate trace files for every session where the value of sql_trace=true and write them to the user_dump_dest destination (the UDUMP directory).


Once you have generated the trace files, the next step is to format the trace file. This utilizes the TKPROF utility to format and make the trace file readable.


TKPROF <input-tracefile> <output-file> EXPLAIN=user/password


You can then view the detailed SQL execution for your query. This report shows the time spent in each state of the query and provides valuable information about the internal workings of any SQL query:


call     count       cpu    elapsed       disk      query    current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ---------
Parse        2      0.02       0.02          0          0          0          0
Execute      2    239.39    1003.16     274981    3792129        534        242
Fetch        0      0.00       0.00          0          0          0          0
------- ------  -------- ---------- ---------- ---------- ----------  ---------
total        4    239.41    1003.18     274981    3792129        534        242



For complete details on using TKPROF, see the Oracle Press Book “Oracle High Performance SQL Tuning”.






Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.