Using Tkprof

The SQL Trace facility and TKPROF are two basic performance diagnostic tools that can help you monitor and tune applications running against the Oracle Server.
You can run the TKPROF program to format the contents of the trace file and place the output into a readable output file. Optionally, TKPROF can also:
  • Determine the execution plans of SQL statements
  • Create a SQL script that stores the statistics in the database
TKPROF reports each statement executed with the resources it has consumed, the number of times it was called, and the number of rows which it processed. This information lets you easily locate those statements that are using the greatest resource. With experience or with baselines available, you can assess whether the resources used are reasonable given the work done.

Example to get the top 10 costly sqls:
tkprof ora53269.trc ora53269.prf SORT=PRSELA,EXEELA,FCHELA PRINT = 10

Example to get the explain plan as well:

TKPROF dlsun12_jane_fg_sqlplus_007.trc OUTPUTA.PRF
EXPLAIN=scott/tiger TABLE=scott.temp_plan_table_a INSERT=STOREA.SQL SYS=NO
 TKPROF Arguments and Explanation:


Specifies the input file, a trace file containing statistics produced by the SQL Trace facility. This file can be either a trace file produced for a single session, or a file produced by concatenating individual trace files from multiple sessions.


Specifies the file to which TKPROF writes its formatted output.


Specifies whether to record summary for any wait events found in the trace file. Values are YES or NO.


Sorts traced SQL statements in descending order of specified sort option before listing them into the output file. If more than one option is specified, then the output is sorted in descending order by the sum of the values specified in the sort options. If you omit this parameter, then TKPROF lists statements into the output file in order of first use. Sort options are listed as follows:


Number of times parsed.


CPU time spent parsing.


Elapsed time spent parsing.


Number of physical reads from disk during parse.


Number of consistent mode block reads during parse.


Number of current mode block reads during parse.


Number of library cache misses during parse.


Number of executes.


CPU time spent executing.


Elapsed time spent executing.


Number of physical reads from disk during execute.


Number of physical reads from disk during execute.


Number of consistent mode block reads during execute.


Number of current mode block reads during execute.


Number of rows processed during execute.


Number of library cache misses during execute.


Number of fetches.


CPU time spent fetching.


Elapsed time spent fetching.


Number of physical reads from disk during fetch.


Number of consistent mode block reads during fetch.


Number of current mode block reads during fetch.


Number of rows fetched.


Lists only the first integer sorted SQL statements from the output file. If you omit this parameter, then TKPROF lists all traced SQL statements. This parameter does not affect the optional SQL script. The SQL script always generates insert data for all traced SQL statements.


If you specify AGGREGATE = NO, then TKPROF does not aggregate multiple users of the same SQL text.


Creates a SQL script that stores the trace file statistics in the database. TKPROF creates this script with the name filename3. This script creates a table and inserts a row of statistics for each traced SQL statement into the table.


Enables and disables the listing of SQL statements issued by the user SYS, or recursive SQL statements, into the output file. The default value of YES causes TKPROF to list these statements. The value of NO causes TKPROF to omit them. This parameter does not affect the optional SQL script. The SQL script always inserts statistics for all traced SQL statements, including recursive SQL statements.


Specifies the schema and name of the table into which TKPROF temporarily places execution plans before writing them to the output file. If the specified table already exists, then TKPROF deletes all rows in the table, uses it for the EXPLAIN PLAN statement (which writes more rows into the table), and then deletes those rows. If this table does not exist, then TKPROF creates it, uses it, and then drops it.
The specified user must be able to issue INSERT, SELECT, and DELETE statements against the table. If the table does not already exist, then the user must also be able to issue CREATE TABLE and DROP TABLE statements. For the privileges to issue these statements, see the Oracle9i SQL Reference.
This option allows multiple individuals to run TKPROF concurrently with the same user in the EXPLAIN value. These individuals can specify different TABLE values and avoid destructively interfering with each other's processing on the temporary plan table.
If you use the EXPLAIN parameter without the TABLE parameter, then TKPROF uses the table PROF$PLAN_TABLE in the schema of the user specified by the EXPLAIN parameter. If you use the TABLE parameter without the EXPLAIN parameter, then TKPROF ignores the TABLE parameter.
If no plan table exists, TKPROF creates the table PROF$PLAN_TABLE and then drops it at the end.


Determines the execution plan for each SQL statement in the trace file and writes these execution plans to the output file. TKPROF determines execution plans by issuing the EXPLAIN PLAN statement after connecting to Oracle with the user and password specified in this parameter. The specified user must have CREATE SESSION system privileges. TKPROF takes longer to process a large trace file if the EXPLAIN option is used.


Creates a SQL script with the specified filename with all of the nonrecursive SQL in the trace file. This can be used to replay the user events from the trace file.


An integer that controls the output line width of some TKPROF output, such as the explain plan. This parameter is useful for post-processing of TKPROF output.
Full Reference:

No comments:

Post a Comment