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


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
   
 

Running TKPROF
Oracle Tips by Burleson
 

TKPROF is an executable that is run at the operating system level. This utility should be stored in the same place as the other Oracle executables. The syntax for running TKPROF is as follows:

TKPROF trace_file output_file
SORT=sort_options
PRINT=num_statements
EXPLAIN=username/password;

Only the first line of this command is required. trace_file specifies the name of the trace file that you created using the ALTER SESSION command. output_file specifies the name of the file generated by TKPROF. The other lines of the command are used to instruct TKPROF of your preferences for the output file. Each of these lines is explained in the following text.

The SORT option instructs TKPROF about how SQL statements and PL/SQL blocks will be ordered in the output file. If no sort option is specified, statements will appear in the same order as in the trace file. A complete list of valid sort options is presented in Table 10.1.

Table 10.1 Valid SORT options for the TKPROF utility.

Sort Option

Description

EXECNT

Number of executions.

EXECPU

Amount of CPU time used during statement execution.

EXECU

Number of current block reads during statement execution.

EXEDSK

Number of physical disk reads during statement execution.

EXEELA

Elapsed time during statement execution.

EXEQRY

Number of consistent block reads during statement execution.

EXEROW

Number of rows processed during statement execution.

EXEMIS

Number of library cache misses during statement execution.

FCHCNT

Number of fetches.

FCHCPU

Amount of CPU time used by the fetch.

FCHCU

Number of current block reads while fetching data.

FCHDSK

Number of physical reads while fetching data.

FCHELA

Elapsed time while fetching data.

FCHQRY

Number of consistent block reads while fetching data.

FCHROW

Number of rows fetched by the statement.

PRSCNT

Total number of parses.

PRSCPU

Amount of CPU time used while parsing the statement.

PRSCU

Number of current block reads while parsing the statement.

PRSDSK

Number of disk reads while parsing the statement.

PRSELA

Elapsed time while parsing the statement.

PRSMIS

Number of library cache misses while parsing the statement.

PRSQRY

Number of consistent block reads while parsing the statement.

If you want to use multiple sort options, use the following syntax:

SORT=(option1, option2,...optionN);

Now let’s return to the command used earlier to run TKPROF. If the PRINT option is used in the command, only the specified number of statements will be included in the output file. TKPROF will create the output file with the specified number of statements after any SORT option has been performed.

If the EXPLAIN option is used, a username and password are specified with the option. TKPROF creates the PLAN_TABLE table (and only this table) under the specified schema, generates an explain plan for each DML statement, and then drops the PLAN_TABLE.

If you can’t find the TKPROF executable on your system, ask your DBA to help you find it.

    
               
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

  
 

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. 

Hit Counter