Reading time: 4 - 7 minutes
What is the use of TKPROF utility
--------------------------------------
The TKPROF is a utility used to convert the Oracle trace file generated during SQL Trace in to a readable format.
TKPROF is a program that you invoke at the operating system command prompt in order to reformat the trace file into a format that is much easier to comprehend. Each SQL statement is displayed in the report, along with counts of how many times it was parsed, executed, and fetched. CPU time, elapsed time, logical reads, physical reads, and rows processed are also reported, along with information about recursion level and misses in the library cache. TKPROF can also optionally include the execution plan for each SQL statement in the report, along with counts of how many rows were processed at each step of the execution plan.
The SQL statements can be listed in a TKPROF report in the order of how much resource they used, if desired. Also, recursive SQL statements issued by the SYS user to manage the data dictionary can be included or excluded, and TKPROF can write SQL statements from the traced session into a spool file.
what Levels the SQL Trace file for TKPROF would have been generated
---------------------------------------------------------------------
Either it would have been done at Instance level by setting SQL_TRACE=TRUE or setting at session level the same.At session level it can be set back to false and at instance level bouncing db back twice At initial and at final after finishing trace.
Where is SQL Trace file located used in the TKPROF
-----------------------------------------------------------
This is Located in the UDUMP directory in oracle database file structure or the path specified in the USER_DUMP_DEST
What should be take care before Using TKPROF utility
--------------------------------------------------------------
Without fail try to close the SQL_TRACE=False before converting the trace file to redable format.Other wise it may not reflect a proper result.
TIMED_STATISTICS=True
-------------------------------
Set this parameter in init.ra or for a particular session level if you require the CPU time calculations for each phase.
How to find the respective trace file from UDUMP directory
-------------------------------------------------------------------
The latest trace file could be found by the latest time of creation of the file.Dont output the old files.Better clear the UDUMP directory before using the SQL Trace.
How to start of with TKPROF utility
---------------------------------------
Simply type from command prompt TKPROF and the options as mentioned below.
A general Syntax of TKPROF
---------------------------------
TKPROF trace_file output_file
[SORT = parameter]
[PRINT = number]
[EXPLAIN = username/password]
[INSERT = stat_file_name]
[SYS = yes/no]
[AGGREGATE=[Y|N]]
[RECORD = sql_file_name]
[TABLE = schema.table_name]
A sample syntax for formating a trace file using TKPROF
-----------------------------------------------------------------
>tkprof explain=user_name/pwd
Under Standing various Options used in TKPROF Utility
--------------------------------------------------------------
Tracefile==>The name of the Trace output file
Outputfile==>The name of the formatted file
Sort=option==>The order in which to sort the statements
Print=n==>Print the first n statements
Explain=Username/pwd==>Run the Explain Plan in the specified user name
Insert=filename==>Generate INSERT statements
Sys=no==>Ignore recursive SQL statements run as sys user.
Aggregate=[Y|N]==>If specified as NO then TKPROF does not aggregate multiple users of the same SQL text
Record=filename==>Record statements found int he trace file
Table=schema.tablename==>Putting execution plan in to specified table(rather than the default PLAN_TABLE).
Under Standing trace file Outputs returned by TKPROF Utility
---------------------------------------------------------------------
After all the sql statement you may find the following values
Call==>PARSE,EXCUTE,FETCH
COUNT==>How many times the statement was parsed or executed and the no of fetch calls issued for the part stat.
CPU==>Process time for each phase in seconds. if the stat was found in the shared pool it is 0 for the parse phase.
ELAPSED==>This is not much useful since other process also affect ellapsed time.
QUERY==>Logical Buffers retrieved for Consistent read normally for a select stat.
CURRENT==>Logical buffers retry in current mode.
ROWS==>Rows processed by the outer stat.Select stat this is shown for the fetch phase and for DML stat it is shown in the execute phase.
DISK==>Phy data blocks read from the DB files. This stst may be very low if the data was buffered.
Libr cache misses==>this states that no of times the stat was not found in the Lib Cache in the parse and execute phase.if there was no miss then this statistcis wont appear.
User==>Id of the last user to parse the statement.
Execution Plan==> when you specify the EXPLAIN parameter with TKPROF it then works out the access path for each sql statement traced and included in the output.
Optimizer Hint==>used to indicate the Optim hint which was used during the execution of the stat.If there was no hint it will show optimizer mode was used.
Recursive SQL==>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.
For example, if you insert a row into a table that does not have enough space to hold that row, Oracle makes recursive calls to allocate the space dynamically. Recursive calls are also generated when data dictionary information is not available in the data dictionary cache and must be retrieved from disk.
If recursive calls occur while the SQL trace facility is enabled,
TKPROF produces statistics for the recursive SQL statements and marks them clearly as recursive SQL statements in the output file. You can suppress the listing of recursive calls
in the output file by setting the SYS statement-line parameter to NO. The statistics for a recursive SQL statement are included in the listing for that statement, not in the listing for
the SQL statement that caused the recursive call. So when you are calculating the total resources required to process a SQL statement, you should consider the statistics for that statement as well as those for recursive calls caused by that statement.
