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.
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.
WHERE name = 'user_dump_dest'
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 is simple:
tkprof trace_file output_file [ explain=userid/password@database ]
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.
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:
Elapsedstatistic. This shows the elapsed time for each SQL. High values obviously indicate long-running SQL
Querycolumns. These indicate data retrieval from disk and data retrieval from memory respectively. If the
Diskcolumn is relatively low compared to the
Querycolumn, then it could mean that the SQL has been run several times and the data has been cached. This might not give a true indication of the performance when the data is not cached. Either have the database bounced by the DBA, or try the trace again another day.
If your Explain Plan in the TK*Prof output shows 0 rows for every line, check the following: