Working With Trace File and TKPROF

Lets start with basics.

What is Trace File:

Its a dump file which will created by Oracle database to help us to diagnose and resolve any performance related issues.

Each server and background process  (eg: check point , log writer etc) writes to trace file,When a process detects an internal error, it writes information about the error to its trace file.

and it will have extension as ".trc".


Trace file wont be in human readable format so we use TKPROF to convert into text file to read.

Query to identify trace file, Click Here


What is TKProf:

TKProf is an Oracle database utility used to format SQL Trace output into human readable format. The TKProf executable is located in the ORACLE HOME/bin directory.

File format of trace file:

The file name format of a trace file is sid_processname_unixpid.trc, 

SID is Instance System identifier,
Process name is three or four-character abbreviated process name identifying the Oracle Database process that generated the file (for example: pmon, dbwr, ora, or reco).
unixpid is process Id number.


eg: venky_ora_9964.trc

Here venky is instance name, ora is process name, 9964 is process Id

It will create trace file, We can check trace file using below query.

SQL> SELECT s.sid,

  2         s.serial#,

  3         pa.value || '/' || LOWER(SYS_CONTEXT('userenv', 'instance_name')) ||

  4         '_ora_' || p.spid || '.trc' AS trace_file

  5    FROM v$session s, v$process p, v$parameter pa

  6   WHERE pa.name = 'user_dump_dest'

  7     AND s.paddr = p.addr

  8     AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

  SID    SERIAL# TRACE_FILE

---------- ---------- --------------------------------------------------------------------

  6      121   c:\app\venky\diag\rdbms\venky\venky\trace/venky_ora_9964.trc

C:\Users\Venky>tkprof C:\app\venky\diag\rdbms\venky\venky\trace\venky_ora_9964.trc my_tkprof_file.txt explain=scott/tiger waits=yes

TKPROF: Release 11.2.0.1.0 - Development on Tue Jun 19 15:16:58 2018


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Using tkprof utility we are converting trace file to human readable format as above.



The resulting output file contains the following type of information for all SQL statements processed.

****************************************************************
count     = number of times OCI procedure was executed
cpu        = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk       = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows      = number of rows processed by the fetch or execute call