317-322

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