Oracle TKPROF Utility

• TKPROF Stands for Transient Kernel Profiler

• It allows you to analyze a trace file to determine where time is being spent

• It converts SQL trace files into human readable format. To activate SQL trace for a particular session:

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;

Run TKPROF

# 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