TKPROF is a program that formats a raw SQL Trace file into a user-friendly file. The program reads the Trace file and creates a file that has the following section:
The header section contains the version of TKPROF, any sort options used and a glossary of terms. The body section displays the following information for each user level SQL statement traced:
• SQL statement text
• Tabulated Parse
• Execute and Fetch statistics
• Number of library cache misses during Parse
• Parsing user id
If specified, TKPROF also:
• Shows the explain plans when the SQL Trace was executed and when TKPROF was run
• Creates a SQL script that creates a table and inserts a row of statistics for each SQL statement
The power of TKPROF is the ability to sort the SQL statements. The sorting helps identify and sequence statements that are using the most resources. At the end of the report, a tabular summary for all the user level and recursive SQL statements is provided.
Formatting a Trace File using TKPROF
You execute TKPROF from the command line. Although TKPROF has many arguments, generally only two mandatory and three optional arguments are used. The execution syntax and arguments are as follows:
tkprof infile outfile sort=options explain=username/password@db print=integer
The tkprof arguments are:
Sample Header , Body and Summary generated by TKPROF from a raw SQL Trace file.