Home  >  Oracle Database Administration (DBA)  >  Database Tuning  >  TKPROF Utility

     TKPROF Utility is not a tuning utility is just an utility to read the trace file. So, one time we have the trace file of one session we can use the TKPROF to read the trace file and to see what happen in that session. Here are the steps in using TKPROF utility:

1. Enable Oracle database to gather statistics (on session or system level)

ALTER SYSTEM SET  timed_statistics = true; 
ALTER SESSION SET  timed_statistics = true;

2. Find the SID, SERIAL# for a specific session you want to monitor  

SELECT username, sid, serial#, program FROM v$session WHERE username = <User_Name>;

3. Enable the tracing for this session (  must be logged as SYSDBA )

EXECUTE dbms_system.set_sql_trace_in_session (<sid>, <serial#>, true);

4. Identify the directory where the trace file is generated

SELECT value FROM v$parameter WHERE name='user_dump_dest';

5. Identify the name of the trace file which is generated

SELECT s.username, s.SID, s.serial#, s.PROGRAM, p.spid 
FROM   v$session s, 

              v$process p 
WHERE p.addr = s.paddr and s.username = <User_Name>;

The name of the trace file is  <oracle_sid>_ora_<p.spid>.trc

6. Disable the tracing for this session (  must be logged as SYSDBA )

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

More information about  this subject ( TKPROF Utility ) you can get from  www.in-oracle.com

TKPROF is available on the database server in the $ORACLE_HOME\rdbms\admin directory. To examine TKPROF utility syntax type TKPROF or TKPROF HELP=Y at the command line and hit the return key, as with many other Oracle Database utilities. TKPROF accepts an input trace file and outputs a formatted file.

Execution plans can also be generated using TKPROF from trace files for all SQL code in the trace input file. Additionally TKPROF can generate a historical record of both all recursive and nonrecursive SQL executed by a session. This is the TKPROF command syntax.
TKPROF tracefile formatted
[ SORT = { option | ( option list ) } ]
[ PRINT = n ]
[ AGGREGATE = { YES | NO } ]
[ INSERT = scriptfile ]
[ SYS = { YES | NO } ]
[ [ TABLE = [schema.]table ] EXPLAIN = username/
password ]
[ RECORD = scriptfile ]

SORT. Sorts by specified options, there are a multitude of them.

PRINT. Sorts the first n SQL statements in the trace file.

AGGREGATE. Groups multiple users using the same SQL text.

INSERT. Writes out an SQL script to generate statistics into the database.

TABLE. The name of the table to which query plans are written.

EXPLAIN. Creates SQL statement query plans.

RECORD. Writes out an SQL script usable to replay all nonre-cursive SQL events.

SYS. SYS user SQL statements and recursive SQL. A recursive SQL statement occurs when, for instance, an SQL statement requires a new extent and an extent must be created prior to completion of the SQL statement.

More information you can get from here.