|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:
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 youve created a trace file for the statement(s) that you want to tune, exit SQL*Plus and run the TKPROF utility.
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.
|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:
Now lets 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 cant find the TKPROF executable on your system, ask your DBA to help you find it.
|Previous||Table of Contents||Next|