 |
|
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.
sql_trace=true
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=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;
-
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.
