Donald K. Burleson
Oracle Utilities Tips
Analyzing tkprof Results
tkprof Command Line Options
tkprof provides many useful command line options that provide additional
functionality for the DBA.
• print – Lists only the first n SQL statements in the output file. If
nothing is specified, all statements will be listed. Use this option when
the list needs to be limited to the “Top n” statements. This is useful when
combined with a sorting option to enable the top n statements by CPU, or
disk reads, or parses, etc.
• aggregate – When “Yes”, tkprof will combine the statistics from multiple
user executions of the same SQL statement. When “No”, the statistics will
be listed each time the statement is executed.
• insert – Creates a file that will load the statistics into a table in the
database for further processing. Choose this option if you want to perform
any advanced analysis of the tkprof output.
• sys – Enables or disables the inclusion of SQL statements executed by the
SYS user, including recursive SQL statements. The default is to enable.
• table – Used in the Explain Plan command (if specified) for Oracle to
load data temporarily into an Oracle table. The user must specify the
schema and table name for the plan table. If the table exists all rows will
be deleted otherwise tkprof will create the table and use it.
• record - creates a SQL script with the specified filename that contains
all non-recursive SQL statements from the trace file. For DBAs wanting to
log the SQL statements in a separate file, this is the option to use. In
the example earlier, the contents of the Allsql.sql file include:
alter session set sql_trace=true ;
select * from employee where emp_id = 87933 ;
alter session set sql_trace=false ;
• explain – Executes an Explain Plan for each statement in the trace file
and displays the output. Explain Plan is less useful when used in
conjunction with tkprof than it is when used alone. Explain Plan provides
the predicted optimizer execution path without actually executing the
statement. tkprof shows you the actual execution path and statistics after
the statement is executed. In addition, running Explain Plan against SQL
statements that were captured and saved is always problematic given
dependencies and changes in the database environment.
• sort – Sorts the SQL statements in the trace file by the criteria deemed
most important by the DBA. This option allows the DBA to view the SQL
statements that consume the most resources at the top of the file, rather
than searching the entire file contents for the poor performers. The
following are the data elements available for sorting:
• prscnt – The number of times the SQL was parsed.
• prscpu – The CPU time spent parsing.
• prsela – The elapsed time spent parsing the SQL.
• prsdsk – The number of physical reads required for the parse.
• prsmis – The number of consistent block reads required for the parse.
• prscu - The number of current block reads required for the parse.
• execnt – The number of times the SQL statement was executed.
• execpu – The CPU time spent executing the SQL.
• exeela – The elapsed time spent executing the SQL.
• exedsk – The number of physical reads during execution.
• exeqry – The number of consistent block reads during
• execu – The number of current block reads during execution.
• exerow – The number of rows processed during execution.
• exemis – The number of library cache misses during execution.
• fchcnt – The number of fetches performed.
• fchcpu – The CPU time spent fetching rows.
• fchela – The elapsed time spent fetching rows.
• fchdsk – The number of physical disk reads during the fetch.
• fchqry – The number of consistent block reads during the fetch.
• fchcu – The number of current block reads during the fetch.
• fchrow – The number of rows fetched for the query.
Many sort options exist, however some are more useful than others.
Execnt, execpu, exedsk and prscnt are the most useful sort parameters when
formatting trace output with tkprof because they are more indicative of
most SQL performance issues. The execution counts are most indicative of
performance issues and therefore should bubble to the top. In particular,
this is true of the SQL statement that used the most CPU – execpu. The
prscnt parameter is important because it shows the SQL statements that are
parsed most, usually a result of not using bind variables.
To learn more about these techniques, see the book "Advanced
Oracle Utilities: The Definitive Reference".
You can buy it
directly from the
publisher and get instant access to the code depot of utilities scripts.
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA