Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle


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:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.