An Insight about TKPROF

What is TKPROF?

TKProf is an Oracle database utility used to format SQL Trace output into human readable format. The TKProf executable is located in the $ORACLE HOME/bin directory.

TKPROF output can be generated from a raw SQL Trace. It formats and summarizes the diagnostic information from the raw SQL Trace.TKPROF allows you to analyse a trace file in easy way to determine where time is being spent and what query plans are being used on SQL statements.

What does a TKPROF output file contains?

TKPROF contains :

SQL ID along with SQL Text that were executed for the traced session.

• Timing information for the execution count, CPU Time, Elapsed Time, Physical Reads, Logical Reads and the total number of rows returned.
• Wait Information such as the times waited, maximum wait and total waited for each database wait event, both for each SQL and for the whole period of the trace.
• Execution Plan -  The execution plan will also contain the actual tables accessed if the SQL uses views or synonyms.

Syntax of TKPROF:

tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]

explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.

table=schema.tablename   Use 'schema.tablename' with 'explain=' option.

print=integer    List only the first 'integer' SQL statements.

insert=filename  List SQL statements and data inside INSERT statements.

sys=no           TKPROF does not list SQL statements run as user SYS.

sort=option      Set of zero or more of the sort options

Out of all available options, most widely used options for performance tuning are exeela, fchela, prsela.