• 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

4,915 views

Recent Posts

See All

Oracle Optimizer determines the cost of each execution plan based on database, schema, table and other statistics. The changes inside database result in stale statistics. As a DBA, you must gather sta

The first step is to understand how instance size is configured. There are two main configurations: One Instance Size Separate SGA and PGA One Instance Size Oracle MEMORY_TARGET parameter defines the