||Oracle Database Tips by Donald Burleson
TKPROF is a utility that Oracle provides to
help make sense of raw trace files. It has been available for quite
some time, at least back to version 7. It offers some benefits over
manual analysis of trace files; most importantly, it is much easier
to run TKPROF than to spend hours digging through a trace file.
Another benefit is that it is included with Oracle database software
and installed with the database so there is no additional cost to
TKPROF is run like any other program with a
couple parameters passed to tell it how to process the trace file.
A brief example will be presented here. For further research into
this utility, read the Oracle documentation, pick up one of many
other books like Dave Moore's "Oracle Utilities", or read any of
several articles like "Doc ID: 142898.1" from MOSC or other web
sites to get a deeper understanding of the power of this tool.
A sample run of this tool using the trace
file that was explained in the previous section looks like this:
$ tkprof toy_ora_32622_find_me.trc
tkprof_32622.trc explain=system/manager waits=y sort=PRSCNT
Executing the above command produces a file
named tkprof_32622.trc. It includes an explain plan based on the
user SYSTEM that will include a summary of the wait events found in
the trace file. The SQL statements will be sorted based on the count
of parses. The output file will look like Figure 3.4.
Figure 3.4 - Sample TKPROF output
The following is a line-by-line description
of the output.
Lines 1-4: Version and execution date and
Line 5: Trace file name.
Line 6: Sorts that were used to produce
this output report. The count of parse calls in this example.
Lines 7-15: Additional information about the
columns that are listed in the measurements section of the report.
Lines 17-20: Top SQL statement according to
the sort specified when running TKPROF and reported on line 6.
Lines 23-29: Measurements section. Compare
the number of parses and executes, one parse to many executes is
good. All the information in this section is helpful to understand
the work this SQL statement needs to do.
Lines 35-38: The explain plan used to
execute this SQL statement. Notice that there is a field called
time. This field reports the time taken for each step and its
children steps. In this example, the time taken as shown on line 37
is about 200 microseconds.
There are many sort options that are
available, especially with Oracle 9.2.x. Additional reading to learn
more about this tool is recommended.
The above book excerpt is from:
Oracle Wait Event Tuning
High Performance with Wait
Event Iinterface Analysis