Overview Reference for SQL_TRACE, TKProf and Explain Plan

Purpose
This document gives an overview of some of the tools that can be used to discover more about SQL execution.
The article provides information covering SQL_TRACE, TKProf, Explain Plan and Autotrace.

Scope & Application

For all DBAs

Overview Reference for SQL_TRACE, TKProf and Explain Plan

Oracle provides extensive tracing and interpretation tools to assist analysts diagnose and resolve sub-optimal performance issues. This article covers the most important of these and points to detailed reference articles.

Tracing Related Initialization Parameters

Prior to tracing, there are a number of parameters that need to be set so that the trace information is complete.
These parameter should be set up in the "init.ora" file for the particular instance (SID) where you wish to use SQL Trace although they can also be set individually at the session level.


Enable/Disable the collection of timed statistics, such as CPU and elapsed times.

TRUE - Enable timing
FALSE - Disable timing (Default value).
Note that much of the tracing information that can be gathered is rendered useless if TIMED_STATISTICS is set to False and timings are not collected.

Specifies the maximum size of trace files in operating system blocks. The default value for this was 10000 OS blocks in 8i version, and
limited only by the space available in 9i version. If your trace file is truncated then you will see a message similar to:

*** DUMP FILE SIZE IS LIMITED TO 12345 BYTES***
and the size of this parameter should be increased.

Specifies the destination for the trace file. The default value for this parameter is the default destination for oracle dumps on your operating system. These parameters can be dynamically altered using alter system/alter session commands (Note that USER_DUMP_DEST can only be modified at the system level). For example TIMED_STATISTICS can be enabled/disabled dynamically by using the following SQL statement:

ALTER SYSTEM/SESSION SET TIMED_STATISTICS = TRUE/FALSE;
SQL_TRACE

SQL_TRACE is the main method for collecting SQL Execution information in Oracle collecting a wide range of information and statistics that can be used to tune SQL operations.

Enabling SQL_TRACE

The SQL Trace facility can be enabled/disabled for an individual session or at the instance level. If the initialisation Parameter SQL_TRACE is set to TRUE in the init.ora of an instance, then all sessions will be traced.

SQL_TRACE can be set at the instance level by using the initialisation parameter SQL_TRACE:

: Enable/Disable SQL Trace instance wide.
TRUE - Enable statistics to be collected for all sessions.
FALSE - Disable statistics to be collected for all sessions.
SQL_TRACE can also be enabled/disabled at the system/session by issuing the following SQL statement:

ALTER SYSTEM/SESSION SET SQL_TRACE = TRUE/FALSE;
Trace can also be enabled/disabled on other sessions (as well as your own) using the DBMS_SUPPORT package. See Note 62160.1 Tracing Sessions in Oracle7/8 for details.
For more information on how to setup tracing, refer to:

Note 15160.1 Setting SQL Trace in the Oracle Tools.
Note 21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits).


There is also extensive information in the
Oracle9i Database Performance Tuning Guide and Reference Chapter 10: Using SQL Trace and TKProf
Trace Files
Oracle will generate trace (.trc) files for every session where the value of SQL_TRACE=TRUE and write them to the USER_DUMP_DEST destination. If tracing has been enabled for the instance then individual trace files will be generated for each session, unless otherwise disabled. Note that the generated files may be owned by an operating system user other than your own so the necessary privileges will need to be put in place before they can be formatted with TKProf.

Explain Plan


The Explain Plan command generates information that details the execution plan that will be used on a particular query.
It uses a precreated table (PLAN_TABLE) in the current shema to store information about the execution plan chosen by the optimizer.

Creating the Plan Table

The plan table is created using the script utlxplan.sql. This script is typically found under the Oracle Home in the rdbms/admin directory.

On Unix its location will be:

$ORACLE_HOME/rdbms/admin

On WindowsNT/2000:
%ORACLE_HOME%\rdbms\admin

This script creates the output table, called PLAN_TABLE, for holding the output of the Explain plan Command.
Note that the exact structure of the plan table can change with different release as new features are introduced.

Populating the Plan Table

The plan table is populated using the explain plan command:

SQL> EXPLAIN PLAN for select * from emp where empno=1000;
This command inserts the execution plan of the SQL statement into the plan table. It is also possible to adds the name tag to the explain information by using the set statement_id clause.
Displaying the Execution Plan

Once the table has been populated, the explain information needs to be retrieved and formatted. There are a large number of scripts available to format the plan table data. Some of the most popular are noted below:

Supplied Scripts:

$ORACLE_HOME/rdbms/admin/utlxpls.sql: script to format serial explain plans
$ORACLE_HOME/rdbms/admin/utlxplp.sql: script to format parallel explain plans
Articles:
Note 31101.1 Obtaining Formatted Explain Plan Output
Note 39294.1 Formatted Select of PLAN_TABLE for EXPLAIN PLAN command
Note 39341.1 Automatic Explain Plan
Note 1019631.6 SCRIPT: SCRIPT TO SIMPLIFY THE USE OF EXPLAIN PLAN
Interpretation of Explain Plan
This is a complex topic and is covered in detail in the following article:

Note 46234.1 Interpreting Explain Plan
There is also extensive information in the
Oracle9i Database Performance Tuning Guide and Reference Chapter 9: Using EXPLAIN PLAN
AUTOTRACE

The autotrace facility in SQL*Plus allows analysts to view the execution plan and some useful statistics for a SQL statement within a SQL*Plus session.
This option was introduced with 7.3 version of Oracle.

Autotrace needs to be initiated in the SQL*Plus session prior to executing the statement.
The Autotrace command is:

SET AUTOTRACE [OPTIONS] [EXPLAIN/STATISTICS]
For a detailed explanation of AUTOTRACE functions see:
Note 43214.1 AUTOTRACE option in 7.3
As with the EXPLAIN PLAN command, to obtain an execution plan the PLAN_TABLE must be created in the user's schema prior to autotracing.
Example

SQL> set autotrace traceonly explain
SQL> select * from dual;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'
To enable viewing of STATISTICS data, the autotracing user must have access to dynamic performance tables. To achieve this, grant PLUSTRACE role to the user. The PLUSTRACE role is created by the plustrce.sql script.
On Unix the location is:

$ORACLE_HOME/sqlplus/admin

On WindowsNT/2000:

%ORACLE_HOME%\sqlplus\admin

This script must be run by the SYS user. A DBA user can then grant the role to the users who wish to use the the AUTOTRACE option.

Refer to:

Note 1055431.6 ORA-01919 usingAUTOTRACE in SQL*Plus
Extensive reference information regarding the autotrace facility can be found in the
SQL*Plus Users Guide and Reference Release 9.0.1
TKProf

The TKProf facility accepts as input a SQL trace file and produces a formatted output file. For the full syntax of TKProf see the Oracle Server Tuning Manual. If TKProf is invoked with no arguments, an online help is displayed.

Basic Syntax of TKProf

TKPROF filename_source filename_output EXPLAIN=[username/password] SYS=[YES/No] TABLE=[tablename]
filename_source The trace file that needs to be formatted
filename_output The output file where the formatted file will be written
'EXPLAIN' This option causes TKProf to generate an execution plan for each SQL statement in the trace file as though it was been parsed at the time the TKProf is being run. TKProf connects as the user [username] and uses the EXPLAIN PLAN command to generate an execution plan for each traced SQL statement. These explain plans are written to the output file. Note that in later releases, the raw trace file automatically records the explain plan that was used at execution time and TKProf will automatically output this plan. If the EXPLAIN option is selected then two explain plans appear in the TKProf output. The first explain plan is the plan that was used at execution time. The second plan the plan that was generated dynamically at the time the TKProf runs. If session settings etc have changed between the execution time and when the trace file is TKProffed then the first and second plans may differ.
'TABLE' Specifies the schema and name of the table into which TKPROF temporarily places execution plans before writing them to the output file. If the specified table already exists, TKProf deletes its rows then uses it for the EXPLAIN PLAN command and then deletes its rows. If this table does not exist, TKProf creates, uses, then drops it. This parameter is ignored if the EXPLAIN parameter isn't used.
'SYS' Enables and disables the listing of SQL statements issued by the user SYS (recursive SQL statements) into the output file. The default value is YES.


For more information on using TKProf see:

Note 32951.1 TKPROF Interpretation
Note 29012.1 Quick Reference TKPROF usage
Note 41634.1 TKPROF and Problem Solving
There is also extensive information in the
Oracle9i Database Performance Tuning Guide and Reference Chapter 10: Using SQL Trace and TKProf