Oracle TKPROF

The TKPROF (Transient Kernel Profiler) is an Oracle database utility which converts Oracle trace files into a more human readable form.

Recently a developer wants me to send his trace file output for a particular trace file in a flatfile

Then I performed the below steps:

SQL> show parameter diag;

NAME                                     TYPE         VALUE

---------------------------------- -----------   ------------------------------

diagnostic_dest                    string      /opt/oracle/app

Then go to diag location and go to the trace location and perform the following

$ tkprof  DEV71_ora_24961_INFO_PKG.trc   dba.txt   sys=no

Here in the above I’m putting the trace file output in a dba.txt file

Tkprof parameters:

TRACEfile : The name of the TRACE file containing the statistics by SQL_TRACE.

Output_file: The name of the file where TKPROF writes its output.

SORT= parameters

The order in which to display the statements in the output. There are about 20 different options for sorting the output-you can even combine these options.

PRINT=number

The number of statements to include in the output. included, TKPROF will list all statements in the output.

EXPLAIN=username/password@sid

Run the EXPLAIN PLAN on the user's SQL statements in the TRACE file. This option will create a PLAN_TABLE of its own, so the user will need to have privileges to create the table and space in which to create it. When TKPROF is finished, this table is dropped.

INSERT= filename

This option creates a script to create a table and store the TRACE file statistics for each SQL statement Traced.

RECORD= filename

This option will produce a file of all the user's SQL statements.

SYS= YES/NO

This option allows the user to request the recursive SQL statements not be displayed in the output. The default is set to YES.

Sort – It will be useful if we want to see the top SQL which are consumed the most resources. Resource like CPU usage, disk usage etc.

The following are the data elements available for sorting:

. prscnt – The number of times the SQL was parsed.

· prscpu – The CPU time spent parsing.

· prsela – The elapsed time spent parsing the SQL.

· prsdsk – The number of physical reads required for the parse.

· prsmis – The number of consistent block reads required for the parse.

· prscu – The number of current block reads required for the parse.

· execnt – The number of times the SQL statement was executed.

· execpu – The CPU time spent executing the SQL.

· exeela – The elapsed time spent executing the SQL.

· exedsk – The number of physical reads during execution.

· exeqry – The number of consistent block reads during execution.

· execu – The number of current block reads during execution.

· exerow – The number of rows processed during execution.

· exemis – The number of library cache misses during execution.

· fchcnt – The number of fetches performed.

· fchcpu – The CPU time spent fetching rows.

· fchela – The elapsed time spent fetching rows.

· fchdsk – The number of physical disk reads during the fetch.

· fchqry – The number of consistent block reads during the fetch.

· fchcu – The number of current block reads during the fetch.

· fchrow – The number of rows fetched for the query.

NOTE:

The TKPROF utility puts a TRACED output into a readable format. Without running TKPROF, it would be difficult to read the output of a TRACE. By specifying "explain=username/password" (noted earlier), we are able to get the EXPLAIN PLAN execution path in addition to the execution statistics of the query

More on Tkprof :  http://www.oracleutilities.com/OSUtil/tkprof.html