Donald K. Burleson
Oracle Utilities Tips
Analyzing tkprof Results
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.
The tkprof process will be explained in six easy steps.
Step 1: Check the Environment
Before tracing 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:
ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
• 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.
SQL> select value
where name = 'user_dump_dest';
Once the directory name is obtained, the corresponding space command (OS
dependent) will report the amount of available space. Delete unwanted trace
files before starting a new trace to free up the disk space.
For more details on Oracle utilities, see
the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve
You can buy it direct from the publisher for
30% off directly from
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA