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:

timed_statistics=true



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