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