Oracle TKPROF Utility

Tracing an SQL statement is the first step towards fixing a slow running SQL. Oracle allows you to enable SQL Tracing for a particular sessions. This will generate trace files which later can be converted into readable format using TKPROF utility.

• Enable SQL Trace

• Run TKPROF

• Reading TKPROF Report

TKPROF stand for Transient Kernel Profiler

Enable SQL Trace

We will be enabling SQL trace only for the problematic sessions

ALTER SESSION SET TIMED_STATISTICS=TRUE;
ALTER SESSION SET SQL_TRACE=TRUE;

Trace File Location: The name of the trace file is <Oracle_SID>_ora_<p.spid>.trc under UDUMP location

SHOW PARAMETER USER_DUMP;

Because running the SQL Trace facility increases system overhead, enable it only when tuning SQL statements, and disable it when you are finished

To disable SQL trace

ALTER SESSION SET SQL_TRACE=FALES;

Run TKPROF

Go to the user dump location and use tkprof to convert trace file into human readable format

tkprof <trace_file> <output_report>

Reading TKPROF Report

PARSE: Oracle finds query in shared pool or creates a new plan

EXECUTE: Does the work of query. It is empty for SELECT statement.

FETCH: Pulls the data back, empty for update, bulk of work for select

• Count: How many times each activity has been performed

• CPU: CPU time used by the query

• ELAPSED: Wall clock time spent

• DISK: Physical I/Os to disk

• Query: Blocks read from rollback / undo

• Current: Blocks as they exists now

• Rows: Number of rows affected