Database Reference
In-Depth Information
print
is used to limit the number of SQL statements provided in the output file. Per default
there's no limit. It makes sense to specify this argument only together with the
sort
argument
(described shortly), to print only the top SQL statements. For example, to get only 10 SQL
statements, the argument must be set to
print=10
.
•
•
aggregate
specifies how TKPROF handles SQL statements having the same text. By default
(
aggregate=yes
), all information belonging to a specific SQL statement is aggregated.
Version 11.2 adds the further requirement that the execution plan needs to match as well.
Thus, in version 11.2, the default is to aggregate information for each execution plan of
a given SQL statement. This aggregation is done independently of the number of SQL
statements present in the trace file.Therefore, as with any aggregation, there might be a loss
of information. Even if the default is good in many cases, it's sometimes better to specify
aggregate=no
and be able to take a look at single SQL statements.
•
insert
instructs TKPROF to generate a SQL script that can be used to store all information in a
database. The name of the SQL script is specified by the argument itself, as in
insert=load.sql
.
•
sys
specifies whether SQL statements executed by the
sys
user (typically, recursive queries
against the data dictionary during parse operations) are written to the output file. The
default value is
yes
, but most of the time I prefer to set it to
no
to avoid having unnecessary
information in the output file. It's unnecessary because you usually have no control over the
SQL statements executed recursively by the
sys
user.
•
record
instructs TKPROF to generate a SQL script containing all nonrecursive statements
found in the trace file. The name of the SQL script is specified by the argument itself (for
example,
record=replay.sql
). According to the documentation, this feature could be used to
manually replay the SQL statements. Because bind variables aren't handled, this is usually not
possible.
•
waits
determines whether information about wait events is added in the output file. Per
default, it's added. Personally, I see no good reason for specifying
waits=no
and consequently
not having the very important wait events in the output file.
•
sort
specifies the order in which the SQL statements are written to the output file. Per
default it's the order in which they're found in the trace file. Basically, by specifying one of
the proposed options, you can sort the output according to resource utilization (for example,
the number of calls, CPU time, and number of physical reads) or response time (that is, the
elapsed time). As you can see for most options (for example, the elapsed time), one value for
each type of database call is available: for example,
prsela
for the time spent parsing a cursor,
exeela
for the time spent executing a cursor, and
fchela
for the time spent fetching rows from
a cursor. Even if you have many choices and combinations, there's only one sort order that's
really useful for investigating performance problems: response time. Therefore, you should
specify
sort=prsela,exeela,fchela
. When you specify a comma-separated list of values,
TKPROF sums the value of the options passed as arguments. This occurs even if they're based
on different units of measurement. Note that when a trace file contains several sessions and
the argument
aggregate=no
is specified, the SQL statements are sorted independently for
each session.
Based on the information just provided, I personally usually run TKPROF with the arguments shown in the
following example:
tkprof {input trace file} {output file} sys=no sort=prsela,exeela,fchela
