TKProf Utility

TKProf Utility

TKProf (an acronym for the Transient Kernel PROFile) 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 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. 

experience or with baselines available, you can assess whether 

the resources used are reasonable given the work done.

You can ran a query to get your SPID, (server process ID) – 

this is very useful in identifying the trace file. 

Finally you can get the trace file in the directory on 

the database server specified by the USER_DUMP_DEST init.ora parameter.

You can retrieve the value of this parameter online by querying 

the V$PARAMETER view or by issuing show parameter dump at sql prompt.

The query is as follows:

select a.spid

from v$process a, v$session b

where a.addr = b.paddr

and b.audsid = userenv(ʹsessionidʹ)

/

Syntax is:

tkprof input_file_name.trc output_file_name.txt [options]

Example:

$ tkprof orcl102_ora_3064.trc output.txt

Note: The trace file should be in your current working directory 

and your output file will be placed in the same directory.

You should check the trace_file/output_file as it contains 

information that will tell you whether you are looking 

at the correct file or not.

TKPROF has many command line options and if you just type TKPROF 

on the command line, you’ll get to see them all:

C:\Documents and Settings\....\Desktop>tkprof

Usage: tkprof tracefile outputfile [explain= ] [table= ]

              [print= ] [insert= ] [sys= ] [sort= ]

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

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

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

  ………………………………………………………………..

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

  record=filename  Record non-recursive statements found in the trace file.

  waits=yes|no     Record summary for any wait events found in the trace file.

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

    prscnt  number of times parse was called 

    prscpu  cpu time parsing

    prsela  elapsed time parsing

    prsdsk  number of disk reads during parse

    and the list goes on …..