TK*Prof is an Oracle tool used to display the statistics generated during a trace. When an Oracle session is traced (by SQL*Trace, Oracle Trace, or Database Trace), a trace file is generated. This trace file is barely human-readable; TK*Prof collates and formats the data into a a more meaningful form.

Finding the trace file

All trace files are written to the same location: a directory that is defined when the database is booted. To find out the location of this directory, run the following SQL.

If this returns a 'Table or view does not exist' error, then have the DBA grant select privileges on sys.v_$parameter to everybody. Go to the directory shown, and list the files in date order; on Unix, this is ls -ltr. If the trace files are not readable, ask the DBA to change the privileges. There is a database initialisation parameter that the DBA can set so that all future trace files are created readable.

Running TK*Prof

Running TK*Prof is simple:

trace_file is the name of the trace file you found in the previous step, and output_file is the file to which TK*Prof will send the output. The optional explain argument will display an Explain Plan for all SQLs in the trace file. There are other optional arguments to tkprof, see the Oracle Utilities manual for more detail.

TK*Prof output

The output of TK*Prof is very well described in the Oracle Utilities manual, so it will not be described again here. The sort of things you should be looking for are:

If your Explain Plan in the TK*Prof output shows 0 rows for every line, check the following:

©Copyright 2003