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

Was this article helpful?

Related Articles

Leave a Comment