• Arun Kumar

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

189 views

Recent Posts

See All

Oracle 12c to 19c Database Upgrade

In this article we will be looking at two most commonly used methods of upgrading Oracle database from 12c to 19c Pre-upgrade tasks Upgrade using DBUA Upgrade using Manual Method You can perform a dir