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.
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
|<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.
PRSCNT – Number of times parsed
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
|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.
|Lists only the first integer sorted SQL statements from the output file. If you omit this parameter, then
|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
b) The EXPLAIN parameter without the TABLE parameter
c) The TABLE parameter without the EXPLAIN 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
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
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:
- Performance Tuning Basics 1 : Selectivity and Cardinality
- Performance Tuning Basics 2 : Parsing
- Performance Tuning Basics 3 : Parent and Child Cursors
- Performance Tuning Basics 4 : Bind Variables
- Performance Tuning Basics 5 : Trace and TKPROF – Part 1: Trace
- Performance Tuning Basics 6 : Trace and TKPROF – Part 2: Generating TKPROF
- Performance Tuning Basics 7 : Trace and TKPROF – Part 3: Analyzing TKPROF Files
- Performance Tuning Basics 8 : Trace File Analyzer (TRCA)
- Performance Tuning Basics 9 : Optimizer Mode
- Performance Tuning Basics 10 : Histograms
- Performance Tuning Basics 11 : Steps to analyze a performance problem
- Performance Tuning Basics 12 : Dynamic Performance Views
- Performance Tuning Basics 13 : Automatic Workload Repository (AWR) Basics
- Performance Tuning Basics 14 : Active Sessions History (ASH) Basics
- Performance Tuning Basics 15 : AWR Report Analysis
- Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC)
[Post Views: 125]
You can connect with me on LinkedIn.
Latest posts by Brijesh Gogia (see all)
- Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC) - April 1, 2018
- Physical and Logical Block Corruption in Oracle Database - March 10, 2018
- Performance Tuning Basics 15 : AWR Report Analysis - February 6, 2018