Bookmark and Share
SQL Trace and TKPROF
(Publish Date: 2008-8-4 1:10pm, Total Visits: 1209, Today: 1, This Week: 2, This Month: 3)

1. If your application has a lot of dynamically generated SQL, the SQL Trace utility is ideal for tuning the SQL statements.

2. SQL Trace will enable you to track the following variables:

* CPU and elapsed times
* Parsed and executed counts for each SQL statement
* Number of physical and logical reads
* Execution plan for all the SQL statements
* Library cache hit ratios

3. There are three parameters related to SQL Trace: Statistics_Level, Timed_Statistics, User_Dump_Dest

Timed_Statistics

To collect performance statistics such as CPU and execution time, set the value of the timed_statistics parameter to true in the init.ora file or SPFILE, or use the alter system set timed_statistics = true statement to turn timed statistics on instancewide. You can also do this at the session level by using the alter session statement as follows:

SQL> alter session set timed_statistics = true;
Session altered

4. Enabling SQL Trace

You can do at the instance level by using the alter session statement or the DBMS_SESSION package.

1) including the line sql_trace=true in your init.ora file or SPFILE or by using the alter system command to set sql_trace to true.

2) SQL> alter session set sql_trace=true; Session altered.

3) DBMS_SESSION.set_sql_trace


5. Interpreting the Trace Files with TKPROF

$ tkprof trace_file.trc trace.prf sys=no explain =y

TKPROF Command-Line Arguments

PARAMETER DESCRIPTION
==========================================================
Filename -- The input trace file produced by SQL Trace

Explain -- The explain plan for the SQL statements

Record -- Creates a SQL script with all the nonrecursive SQL statements

Waits -- Records a summary of wait events

Sorts Sorts the statements in descending order

Table -- The name of the tables into which the TKPROF utility temporarily puts the execution plans

Sys -- Enables and disables listing of SQL statements issued by SYS

Print -- Lists only a specified number of SQL statements instead of all statements

Insert -- Creates a script that stores the trace information in the database

6. Examining the Formatted Output File

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