1. 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.
2. 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.
3. 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
4. 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.
5. 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.
6. 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]
7. 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).
8. 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.
A Question for a Practice
-----------------------------
When TKPROFING, I set the argument sys=n so that system sql statement
statisticts would not be shown.
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.
2. 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.
3. 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
4. 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.
5. 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.
6. 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]
7. 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).
8. 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.
A Question for a Practice
-----------------------------
When TKPROFING, I set the argument sys=n so that system sql statement statisticts would not be shown.
Trace Files and TKPROF in Oracle Apps
- Enable the SQL Trace facility for the session by using one of the following DBMS_SESSION.SET_SQL_TRACE procedure
- Diagnostics Link > "Set Trace Level" > Go button > Choose the Trace Level.
- In situations where you cannot invoke an ALTER SESSION command from the session you wish to trace—as with prepackaged applications, for example—you can connect to the database as a DBA user and invoke the dbms_system built-in package in order to turn on or off SQL trace in another session. You do this by querying v$session to find the SID and serial number of the session you wish to trace and then invoking the dbms_system package with a command of the form:EXECUTE SYS.dbms_system.set_sql_trace_in_session (, , TRUE|FALSE);
select name,value from V$PARAMETER where name = 'user_dump_dest';
TKPROF
Trace a Concurrent Request And Generate TKPROF File
Enable Tracing For The Concurrent Manager ProgramResponsibility: System Administrator
Navigate: Concurrent > Program > Define
Query Concurrent Program
Select the Enable Trace Checkbox
Turn On Tracing
Responsibility: System Administrator
Navigate: Profiles > System
Query Profile Option Concurrent: Allow Debugging
Set profile to Yes
Run Concurrent Program With Tracing Turned On
Logon to the Responsibility that runs the Concurrent Program
In the Submit Request Screen click on Debug Options (B)
Select the Checkbox for SQL Trace
Find Trace File Name
Run the following SQL to find out the Raw trace name and location for the concurrent program. The SQL prompts the user for the request id
SELECT ’Request id: ’||request_id , ‘Trace id: ’||oracle_Process_id, ‘Trace Flag: ’||req.enable_trace, ‘Trace Name: ‘||dest.value||’/’||lower(dbnm.value)||’_ora_’||oracle_process_id||’.trc’,
‘Prog. Name: ’||prog.user_concurrent_program_name, ‘File Name: ’||execname.execution_file_name|| execname.subroutine_name , ‘Status : ’||decode(phase_code,’R’,’Running’) ||’-’||decode(status_code,’R’,’Normal’), ‘SID Serial: ’||ses.sid||’,’|| ses.serial#, ‘Module : ’||ses.module from fnd_concurrent_requests req, v$session ses, v$process proc, v$parameter dest,
v$parameter dbnm, fnd_concurrent_programs_vl prog, fnd_executables execname where req.request_id = &request and req.oracle_process_id=proc.spid(+) and proc.addr = ses.paddr(+)
and dest.name=’user_dump_dest’ and dbnm.name=’db_name’ and req.concurrent_program_id = prog.concurrent_program_id and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id and prog.executable_id=execname.executable_id;
TKPROF Trace File
Once you have obtained the Raw trace file you need to format the file using TKPROF.
$tkprof raw_trace_file.trc output_file explain=apps/ sort=(exeela,fchela) sys=no
Where: raw_trace_file.trc: Name of trace file
output_file: tkprof out file
explain: This option provides the explain plan for the sql statements
sort: his provides the sort criteria in which all sql statements will be sorted. This will bring the bad sql at the top of the outputfile.
sys=no:Disables sql statements issued by user SYS
Another example: To get (TKPROF) sorted by longest running queries first and limits the results to the “Top 10″ long running queries
$ tkprof raw_trace_file.trc output_file sys=no explain=apps/ sort=’(prsela,exeela,fchela)’ print=10
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use ‘schema.tablename’ with ‘explain=’ option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first ‘integer’ SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor
TKPROF Example#1
----------------------
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 ora53269.trc ora53269.prf SORT = (PRSDSK, EXEDSK, FCHDSK) PRINT = 10
TKPROF Example#2
----------------------
This example runs TKPROF, accepts a trace file named dlsun12_jane_fg_sqlplus_007.trc, and writes a formatted output file named outputa.prf:
TKPROF dlsun12_jane_fg_sqlplus_007.trc OUTPUTA.PRF
EXPLAIN=scott/tiger TABLE=scott.temp_plan_table_a INSERT=STOREA.SQL SYS=NO
SORT=(EXECPU,FCHCPU)
