TKPROF

Now that you have traced a session through SQL Trace or the 10046 event, you will want to examine the contents of your trace file. The trace file is in raw format, and it can be difficult to read. The TKPROF command line utility can be used to change a raw trace file into a readable format. The simplest way to use the TKPROF utility is run the utility from the command line with two parameters, the trace file name and the name of the output file.

tkprof tracefile outputfile

TKPROF will create a readable file for you. Each of your SQL statements will be shown along with statistics on the parse, execute, and fetch phases. The execution plan for that statement will then be shown. An example of output from TKPROF can be seen below:

SELECT object_name
FROM
 db_object WHERE object_id = :b1


call    count     cpu   elapsed     disk    query  current      rows
------- -----  ------ --------- -------- -------- --------  --------
Parse       1    0.00      0.00        0        0        0         0
Execute 10000    0.38      0.38        0        0        0         0
Fetch   10000    0.27      0.30        0    30020        0     10000
------- -----  ------ --------- -------- -------- --------  --------
total   20001    0.65      0.68        0    30020        0     10000

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 29     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
  10000  TABLE ACCESS BY INDEX ROWID DB_OBJECT (cr=30020 r=0 w=0 time=265932 us)
  10000   INDEX RANGE SCAN DB_OBJ_IDX (cr=20020 r=0 w=0 time=169506 us)(object i
d 36480)

We can see one of our queries. The three phases of processing that statement are shown; Parse, Execute, and Fetch. For each phase, we can see the number of times that phase occurred, the total cpu time for that phase, the total elapsed time for that phase, the number of physical reads (disk), the number of consistent reads (current), and the total number of rows processed. In our example, we can see that this statement was executed 10,000 times, returning 10,000 rows.

The optimizer mode is shown along with the execution plan. We can see that this example used the DB_OBJ_IDX index to access the DB_OBJECT table. All highlighted text above is only to facilitate finding the information we are talking about. The TKPROF utility will not highlight this information for you. At the end of the TKPROF output file are the totals of the Parse, Execute, and Fetch phases for non-recursive SQL statements.

TKPROF Options

There are many options to the TKPROF command line utility. The only required parameters are the trace file name and the output file name. The table below summarizes the options available in the TKPROF utility.

Option	Description
TABLE=schema.tablename	Specifies the optional plan table for use with the EXPLAIN option
EXPLAIN=user/password	Specifies the userid/password for the TKPROF utility to sign on to the database to run the EXPLAIN PLAN command.
PRINT=integer	Output only the first integer SQL statements.
AGGREGATE=yes|no	Specifies whether to aggregate identical SQL statements or not.
INSERT=filename	Creates a script called filename that can be used to insert trace file statistics into a database table.
SYS=yes|no	Specifies whether or not to include recursive SQL statements in the output file.
RECORD=filename	Creates a script called filename that will hold all non-recursive SQL statements in the trace file. This script can be used to replay the SQL statements at a later time.
WAITS=yes|no	Specifies whether or not to display summaries of wait events in output file. If using 10046 event, level 8 or 12, then you will want to set this option to YES.
SORT=option	Sorts the SQL statements in descending order of the specified option. PRSCNT Number of times parsed PRSCPU - CPU time spent during parse phase PRSELA - Elapsed time spent during parse phase PRSDSK - Disk reads during parse phase PRSQRY - Consistent reads during parse phase PRSCU - Current mode block reads during parse phase PRSMIS - Library Cache misses during parse phase EXECNT - Number of times executed EXECPU - CPU time spent during execute phase EXEELA - Elapsed time spent during execute phase EXEDSK - Disk reads during execute phase EXEQRY - Consistent reads during execute phase EXECU - Current mode block reads during execute phase EXEROW - Rows processed during execute phase EXEMIS - Library Cache misses during execute phase FCHCNT - Number of times fetched FCHELA - Elapsed time spent during fetch phase FCHDSK - Disk reads during fetch phase FCHQRY - Consistent reads during fetch phase FCHCU - Current mode block reads during fetch phase FCHROW - Rows processed during fetch phase USERID - Userid of parsing user