To enable Performance Trace TKPROF File for the concurrent program do the below steps,
a. Go to Sysadmin > Concurrent > Program > Define
Query the concurrent program
Check the trace box to enable trace
b. 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.
Once the program completes the concurrent program generates TRC trace file into the diagnostic_dest location on the database server.
For example, To generate the TKPROF output from the TRC file use the below command,
$ tkprof <filename.trc> <output_filename> sys=no explain=apps/<password> sort='(prsela,exeela,fchela)' print=10
Below are the list of all the options that can be used with TKPROF as per the requirement,
TKPROF Options
tkprof provides many useful command line options that provide additional functionality for the DBA.
• print – Lists only the first n SQL statements in the output file. If nothing is specified, all statements will be listed. Use this option when the list needs to be limited to the “Top n” statements. This is useful when combined with a sorting option to enable the top n statements by CPU, or disk reads, or parses, etc.
• aggregate – When “Yes”, tkprof will combine the statistics from multiple user executions of the same SQL statement. When “No”, the statistics will be listed each time the statement is executed.
• insert – Creates a file that will load the statistics into a table in the database for further processing. Choose this option if you want to perform any advanced analysis of the tkprof output.
• sys – Enables or disables the inclusion of SQL statements executed by the SYS user, including recursive SQL statements. The default is to enable.
• table – Used in the Explain Plan command (if specified) for Oracle to load data temporarily into an Oracle table. The user must specify the schema and table name for the plan table. If the table exists all rows will be deleted otherwise tkprof will create the table and use it.
• record - creates a SQL script with the specified filename that contains all non-recursive SQL statements from the trace file. For DBAs wanting to log the SQL statements in a separate file, this is the option to use. In the example earlier, the contents of the Allsql.sql file include:
alter session set sql_trace=true ;
select * from employee where emp_id = 87933 ;
alter session set sql_trace=false ;
• explain – Executes an Explain Plan for each statement in the trace file and displays the output. Explain Plan is less useful when used in conjunction with tkprof than it is when used alone. Explain Plan provides the predicted optimizer execution path without actually executing the statement. tkprof shows you the actual execution path and statistics after the statement is executed. In addition, running Explain Plan against SQL statements that were captured and saved is always problematic given dependencies and changes in the database environment.
• sort – Sorts the SQL statements in the trace file by the criteria deemed most important by the DBA. This option allows the DBA to view the SQL statements that consume the most resources at the top of the file, rather than searching the entire file contents for the poor performers. The following are the data elements available for sorting:
• prscnt – The number of times the SQL was parsed.
• prscpu – The CPU time spent parsing.
• prsela – The elapsed time spent parsing the SQL.
• prsdsk – The number of physical reads required for the parse.
• prsmis – The number of consistent block reads required for the parse.
• prscu - The number of current block reads required for the parse.
• execnt – The number of times the SQL statement was executed.
• execpu – The CPU time spent executing the SQL.
• exeela – The elapsed time spent executing the SQL.
• exedsk – The number of physical reads during execution.
• exeqry – The number of consistent block reads during execution.
• execu – The number of current block reads during execution.
• exerow – The number of rows processed during execution.
• exemis – The number of library cache misses during execution.
• fchcnt – The number of fetches performed.
• fchcpu – The CPU time spent fetching rows.
• fchela – The elapsed time spent fetching rows.
• fchdsk – The number of physical disk reads during the fetch.
• fchqry – The number of consistent block reads during the fetch.
• fchcu – The number of current block reads during the fetch.
• fchrow – The number of rows fetched for the query.
Many sort options exist, however some are more useful than others. Execnt, execpu, exedsk and prscnt are the most useful sort parameters when formatting trace output with tkprof because they are more indicative of most SQL performance issues. The execution counts are most indicative of performance issues and therefore should bubble to the top. In particular, this is true of the SQL statement that used the most CPU – execpu. The prscnt parameter is important because it shows the SQL statements that are parsed most, usually a result of not using bind variables
