Search 800 + Posts

Apr 8, 2014

Oracle Performance : TKPROF for formatting raw SQL Trace file

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.



No comments:

Post a Comment