Performance Tuning Basics 6 : Trace and TKPROF – Part 2: Generating TKPROF

We have found sort= fchela,exeela,prsela options to be most effective in breaking performance issues.
Sort options are listed as follows:

PRSCNT – Number of times parsed
PRSCPU – CPU time spent parsing
PRSELA – Elapsed time spent parsing
PRSDSK – Number of physical reads from disk during parse
PRSQRY – Number of consistent mode block reads during parse
PRSCU – Number of current mode block reads during parse
PRSMIS – Number of library cache misses during parse
EXECNT – Number of executions
EXECPU – CPU time spent executing
EXEELA – Elapsed time spent executing
EXEDSK – Number of physical reads from disk during execute
EXEQRY – Number of consistent mode block reads during execute
EXECU – Number of current mode block reads during execute
EXEROW – Number of rows processed during execute
EXEMIS – Number of library cache misses during execute
FCHCNT – Number of fetches
FCHCPU – CPU time spent fetching
FCHELA – Elapsed time spent fetching
FCHDSK – Number of physical reads from disk during fetch
FCHQRY – Number of consistent mode block reads during fetch
FCHCU – Number of current mode block reads during fetch
FCHROW – Number of rows fetched
USERID – Userid of user that parsed the cursor

NOTE: Although using these options will help you most of the time but sometimes it can be useful to have an unsorted tkprof. The SQLs are listed in the order they were parsed. So this can sometimes help locate the portion of code causing the issue or help identify if trace was disabled (at end of tkprof).

It is good idea many times to skip the SYS executed statements so that you can focus on the actual issue.

If multiple user wants to run the tkprof simultaneously and also use the EXPLAIN PLAN option of the TKPROF then it is important that each user should specify its own PLAN table so that table can avoid destructively interfering with each other’s processing on the temporary plan table.

TKPROF supports the following combinations:

a) The EXPLAIN parameter with the TABLE parameter
TKPROF uses the table mentioned to save the EXPLAIN PLAN details

b) The EXPLAIN parameter without the TABLE parameter
TKPROF uses the table PROF$PLAN_TABLE in the schema of the user specified by the EXPLAIN parameter.If no plan table exists, then TKPROF creates the table PROF$PLAN_TABLE and then drops it at the end.

c) The TABLE parameter without the EXPLAIN parameter
TKPROF ignores the TABLE parameter.

Use this option to determine the execution plan for each SQL statement in the trace file and writes these execution plans to the output file. TKPROF also displays the number of rows processed by each step of the execution plan.In order to maximize the
chances of getting the right execution plans, you should specify a user with access to the same objects

This parameter should not be necessary most of the times. This only generates the explain plan (that would be used) at TKPROF time, it is not the actual execution plan and can often be different. It also does not have any actual row source statistics (row source operation counts).

It is the actual runtime execution plans (row source operation counts) that are really important; these are stored in the raw trace file (STAT lines) and automatically reported in the TKPROF file.