Skip to content

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

Published November 24, 2017 By Brijesh Gogia

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.

This post is part 2 of the below posts on trace and tkprof. Please refer other posts to read related information.

Part 1: Trace and the methods to generate trace
Part 2: Generating TKPROF from trace file
Part 3: Analyzing TKPROF files
Part 4: Using Trace Analyzer (TRCANLZR) Tool

This post will cover Part 2: Generating a tkprof file from trace file.

WHAT DOES A TKPROF FILE CONTAINS:

  • SQL Text that were executed
  • Timing information: The execution count, elapsed time, CPU time, physical reads (disk), logical reads (query/current) and the number of rows returned for each SQL. This is further broken down into Parse, Execute and Fetch stages.
  • Wait information: 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. It only does this if the level is 8 (with waits) or 12 (with waits and binds). At least level 8 is recommend because it gives this extra information.
  • Execution Plan: The runtime execution plan for each SQL with the actual row source operation counts for each execution plan line. It only does this if the cursor has been closed for the SQL (in 10g and before) or the row source statistics for the SQL have been written to the trace. The execution plan will also contain the actual tables accessed if the SQL uses views or synonyms.

It also contains information like Library cache misses (hard parse) and total SQL and wait statistics for Recursive and Non-recursive SQL Statements

HOW TO GENERATE A TKPROF FILE FROM TRACE FILE

The following is a typical command  for generating TKPROF file from a trace file:

tkprof <filename1> <filename2> sort= fchela,exeela,prsela sys=no

Where

PARAMETER MEANING
<filename 1> The input trace file or a consolidated file of traces produced by the trcsess utility example ora123.trc
<filename2> The file to which TKPROF writes its formatted output. example ora123.out
     sort Sorts traced SQL statements in descending order of specified sort option before listing them in the output file. If multiple options are specified, then the output is sorted in descending order by the sum of the values specified in the sort options. If you omit this parameter, then TKPROF lists statements into the output file in order of first use.

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).

OTHER TKPROF OPTIONS

Some other useful TKPROF parameter options are listed below

PARAMETER MEANING
SYS Sometimes, in order to execute a SQL statement issued by a user, Oracle must issue additional statements. Such statements are called recursive calls or recursive SQL statements. This parameter will enable and disable the listing of SQL statements issued by the user SYS, or recursive SQL statements, into the output file. The default value of YES causes TKPROF to list these statements. The value of NO causes TKPROF to omit them.

It is good idea many times to skip the SYS executed statements so that you can focus on the actual issue.
PRINT Lists only the first integer sorted SQL statements from the output file. If you omit this parameter, then TKPROF lists all traced SQL statements.

 
TABLE table parameter is used only together with the explain argument. Usually you can avoid specifying it because TKPROF automatically creates and drops a plan table named prof$plan_table in the schema used for the analysis.

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.
EXPLAIN EXAMPLE: explain=user/password@connect_string and explain=user/password.

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.
RECORD Creates a SQL script with the specified filename with all of the nonrecursive SQL in the trace file. You can use this script to replay the user events from the trace file
WIDTH  An integer that controls the output line width of some TKPROF output, such as the explain plan. This parameter is useful for post-processing of TKPROF output.

Please note that the table, record, width arguments are not usually needed and can be omitted.

EXAMPLES OF USING TKPROF

NORMAL TKPROF

tkprof tracefile outputfile

 

TKPROF WITH SORTING

If you are processing a large trace file using a combination of SORT parameters and the PRINT parameter, then you can produce a TKPROF output file containing only the highest resource-intensive statements. For example, the following statement prints the 10 statements in the trace file that have generated the most physical I/O:

TKPROF oracle_123.trc oracle_123.out SORT=PRSDSK, EXEDSK, FCHDSK PRINT = 10

 

TKPROF WITH SORTING AND EXPLAIN OPTION

TKPROF <trace-file> <output-file> explain=user/password@service table=sys.plan_table

 

TKPROF WITH SORTING AND EXPLAIN OPTION BUT NO “SYS” COMPONENT

TKPROF <trace-file> <output-file> explain=user/password@service table=sys.plan_table SYS=NO

 

FEW KEY POINTS TO REMEMBER

  • In order to create the tkprof file you must have write access in the directory that you are creating the file. If you do not have write access in the trace directory, then specify a directory where you do have write access.
  • Please use the TKPROF under the RDBMS Oracle Home /bin directory. The TKPROF under the Applications Oracle Home will not yield accurate results.

This is one of the post in Performance Tuning Fundamentals Series. Click on below links to read more posts from the series:

 

Brijesh Gogia
Latest posts by Brijesh Gogia (see all)
Print 🖨
Post Views: 2,372

Related posts:

  1. Performance Tuning Basics 7 : Trace and TKPROF – Part 3: Analyzing TKPROF Files
  2. Performance Tuning Basics 5 : Trace and TKPROF – Part 1: Trace
  3. Performance Tuning Basics 12 : Dynamic Performance Views
  4. Performance Tuning Basics 8 : Trace File Analyzer (TRCA)
  5. Performance Tuning Basics 11 : Steps to analyze a performance problem
  6. Performance Tuning Basics 1 : Selectivity and Cardinality
  7. Performance Tuning Basics 2 : Parsing
  8. Performance Tuning Basics 4 : Bind Variables
  9. Performance Tuning Basics 9 : Optimizer Mode
  10. Performance Tuning Basics 10 : Histograms

Categories:DATABASE Database Basics Performance Tuning Series TUNING Tuning Basics

2 Comments

  1. satish satish

    Thanks for all your work for sharing these wonderful performance tips

    February 5, 2018 Reply
  2. Smita S. Smita S.

    Can we use this in scenario when there is PL/SQL packcage is executed and I want to find the sqls in them and where it is stucked. ????

    September 9, 2018 Reply
Leave a Reply to satish Cancel reply