Oracle SQL TRACE & TKPROF utility

Oracle SQL TRACE utility to measure timing statistics for a given query.
SQL TRACE records many vital information regarding actual query execution into a trace file.
SQL TRACE helps developers analyze every section of a query.
It writes query execution statistics like number of logical I/O, physical I/O, the  CPU and elapsed timings, number of rows processed, query plans with row counts at each levels,information in wait events etc.
Its  bit difficult  to read the trace file .Using TKPROF we can generate readable report from trace file.

Steps for SQL TRACE 

---

1. We need to set the below parameters.

ALTER SESSION SET TIMED_STATISTICS=TRUE;

This enables and disables the collection of timed statistics , such as CPU  and elapsed time etc. The value  can be TRUE or FALSE.

Below is the default destination of trace file.

USER_DUMP_DEST = \oracle\product\10.2.0\admin\tsm\udump

2. Enable the SQL TRACE for a session.

ALTER SESSION SET SQL_TRACE=TRUE;

3. Run the query(run your SQL).

SELECT ENAME,EMPNO,DEPTNO,SAL FROM EMP WHERE EMPNO=10;

4. Disable the SQL TRACE .

ALTER SESSION SET SQL_TRACE=FALSE;

Steps for TKPROF

---

Once your trace file is ready then we need to run the TKPROF at the command line to generate the report.TKPROF accepts input as a trace file and  it produces a formatted output file(report).

Syntax:

tkprof tracefile output_file [sort = parameters] [print=number]
[explain=username/password] [waits=yes|no] [aggregate=yes|no] [insert=filename]
[sys=yes|no] [table=schema.table] [record=filename]

Details of important parameters:
Tracefile: This is the name of the SQL TRACE file containing the statistics by SQL_TRACE.
Output_file: This is the name of the file where TKPROF writes its output.
sort = parameters: A multiple number of sorting options are available.

• FCHCPU (CPU time of fetch); 
• FCHDSK (disk reads for fetch); 
• FCHCU and FCHQRY (memory reads forfetch); 
• FCHROW (number of rows fetched); 
• EXEDSK (disk reads during execute); 
• EXECU and EXEQRY (memory reads during execute); 
• EXEROW (rows processed during execute); 
• EXECPU (execute CPU time); PRSCPU (parse CPU); and 
• PRSCNT (times parsed).

print = number: This is the number of statements to include in the output. If this statement is not included, TKPROF will list all statements in the output.

Explain = username/password: Run the EXPLAIN PLAN on the user’s SQL statements in the TRACE file. This option creates a plan_table of its own,so the user will need to have privileges to create the table and space in which to create it. When TKPROF is finished,this table is dropped. Ensure that you use the username/password of the user that parsed the cursor (ran the query) to ensure the explain is by the correct user.

waits=yes/no: Record summary for any wait events.

aggregate=yes|no: If no, then tkprof does not combine multiple users of the same SQL text.

insert=filename: This option creates a script to create a table and store the TRACE file statistics for each SQL statement traced.

table=schema.table: The table in which tkprof temporarily put execution plans before writing them to the output file.

Once your trace file is ready then we need to run the below command:

5. Go by command line to (cmd) .

oracle\product\10.2.0\admin\oracle\udump

find your trace file with your SPID

6. Run TKPROF command .

 TKPROF <trace_file> <output_report.prf>