TK Prof in Oracle

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:

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.

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:

For each SQL, check the Elapsed statistic. This shows the elapsed time for each SQL. High values obviously indicate long-running SQL

• Note the Disk and Query columns. These indicate data retrieval from disk and data retrieval from memory respectively. If the Disk column is relatively low compared to the Query column, 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.

• The first row of statistics for each SQL is for the Parse step. If a SQL is run many times, it usually does not need to be re-parsed unless Oracle needs the memory it is taking up, and swaps it out of the shared pool. If you have SQLs parsed more than once, get the DBA to check whether the database can be tuned to reduce this.

• A special feature of the Explain Plan used in TK Prof is that it shows the number of rows read for each step of the execution plan. This can be useful to track down Range Scan problems where thousands of rows are read from an index and table, but only a few are returned after the bulk are filtered out.

• In order to run SQL statements, Oracle must perform its own SQL statements to query the data dictionary, looking at indexes, statistics etc. This is called Recursive SQL. The last two entries in the TK Prof output are summaries of the Recursive and Non-Recursive (ie. "normal") SQL. If the recursive SQL is taking up more than a few seconds, then it is a likely sign that the Shared Pool is too small. Show the TK Prof output to the DBA to see if the database can be tuned.

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

• Make sure you turn tracing off or exit your traced session before running TK Prof. Some statistics are only written at the end.

• Have you run any ALTER SESSION commands that affect the optimizer? If so, then the plan shown may differ from the real plan. Note that the real plan is not shown: TK Prof re-evaluates the plan when you run TK Prof. Make sure that you turn SQL_TRACE on before you ALTER SESSION. TK Prof is clever enough to see the ALTER SESSION command in the trace file and evaluate plans accordingly. It will probably display two plans: the default plan, and the new plan taking the ALTER SESSION into account.