• Arun Kumar

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

337 views

Recent Posts

See All

Reclaim Unused Space in Oracle

Over a period of time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data. This type of empty space i

SQL Data-Type conversion functions

SQL conversion functions are single row functions designed to alter the nature of the data type of a column value, expression, or literal. TO_CHAR, TO_NUMBER, and TO_DATE are the three most widely use

Automatic SQL Tuning in Oracle

Database optimizer runs very fast and must select the best execution plan for a query within fraction of seconds. Due to time constraint (under normal query execution), sometimes optimizer will choose