An Insight about TKPROF

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.

TKPROF output can be generated from a raw SQL Trace. It formats and summarizes the diagnostic information from the raw SQL Trace.TKPROF allows you to analyse a trace file in easy way to determine where time is being spent and what query plans are being used on SQL statements.

What does a TKPROF output file contains?

TKPROF contains :

SQL ID along with SQL Text that were executed for the traced session.

• Timing information for the execution count, CPU Time, Elapsed Time, Physical Reads, Logical Reads and the total number of rows returned.
• Wait Information such as the times waited, maximum wait and total waited for each database wait event, both for each SQL and for the whole period of the trace.
• Execution Plan -  The execution plan will also contain the actual tables accessed if the SQL uses views or synonyms.

Syntax of TKPROF:

tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]

explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.

table=schema.tablename   Use 'schema.tablename' with 'explain=' option.

print=integer    List only the first 'integer' SQL statements.

insert=filename  List SQL statements and data inside INSERT statements.

sys=no           TKPROF does not list SQL statements run as user SYS.

sort=option      Set of zero or more of the sort options

Out of all available options, most widely used options for performance tuning are exeela, fchela, prsela.

How to enable trace for a database session

This article lists the commands required to trace SQL statements executed by a user or an application.

Tracing a SQL session

Start session trace

To start a SQL trace for the current session, execute:

ALTER SESSION SET sql_trace = true;

You can also add an identifier to the trace file name for later identification:

ALTER SESSION SET sql_trace = true;
ALTER SESSION SET tracefile_identifier = sampletrace;

Stop session trace

To stop SQL tracing for the current session, execute:

ALTER SESSION SET sql_trace = false;

Tracing other user's sessions

We can use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to trace problematic database sessions.

In order to enable trace for other session, first thing we need is to identify their session sid and serial#.

Enable tracing for your selected process:

ALTER SYSTEM SET timed_statistics = true;
execute dbms_system.set_sql_trace_in_session(<sid>, <serial#>, true);

Now ask the user to reproduce the problem

Disable tracing for your selected process:

execute dbms_system.set_sql_trace_in_session(<sid>, <serial#>, false);

Location of Trace File:

We can identify the trace file for a specific session using the V$SESSION and V$PROCESS views.

SELECT p.tracefile
FROM   v$session s
       JOIN v$process p ON s.paddr = p.addr
WHERE  s.sid = <sid>;