In Oracle : TKPROF Utility in Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Performance Tuning

-> TKPROF Utility in Oracle

 

 TKPROF Utility in Oracle

 

 

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);

 

7. CONVERT the trace file into a readable format

 

cd $ORACLE_HOME/admin/<oracle_sid>/udump

tkprof   trace_file.trc   Readable_File.txt

 

Now the Readable_File.txt contains information like:

 

 

The execution plan also is included in the trace file. 

 

 

In-Oracle.com  -> Oracle DBA -> Oracle Performance Tuning

-> TKPROF Utility in Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.