Previous Table of Contents Next


Setting Up A Trace File

Before using TKPROF, you must create a trace file. You can create a trace file by executing the following command in SQL*Plus:

ALTER SESSION SET SQL_TRACE = TRUE;

This command instructs SQL*Plus to set up a trace file for all SQL statements and PL/SQL blocks that you execute. When you have finished executing the statements that you wish to examine, execute the following command to stop writing to the trace file:

ALTER SESSION SET SQL_TRACE = FALSE;

Keep in mind that ALTER SESSION is not a DML statement and cannot be run from inside your PL/SQL blocks. Also keep in mind that disconnecting from SQL*Plus or connecting as another user via the CONNECT statement will also cause SQL*Plus to stop writing to the trace file.

The location of your trace file depends on how the DBA has set the USER_DUMP_DEST parameter in the init.ora file for your database. Consult your DBA to determine the location of the trace files, or look at the init.ora file yourself. Each trace file is given a specific ID number by Oracle (in Unix, this is the system process ID for the SQL*Plus session) and has a .trc extension. Trace files are named according to this convention:

ora_ + <id> + .trc

Thus, a valid trace file name might be:

ora_09213.trc

By listing the files in the directory specified by the USER_DUMP_DEST parameter, you can easily determine which trace file is yours by simply taking the most recent trace file (the trace file with the highest ID number). Once you’ve created a trace file for the statement(s) that you want to tune, exit SQL*Plus and run the TKPROF utility.

Running TKPROF

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.


Previous Table of Contents Next