• Arun Kumar

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.


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

1,279 views

Recent Posts

See All