- 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