This page contains materials that were copied from Oracle's on-line documents.

Oracle's SQL-related performance tools let you examine the execution plan for a SQL statement, and thereby determine whether the statement can be better optimized. A statement's execution plan is the sequence of operations Oracle performs to run the statement. The row source tree is the core of the execution plan. It shows the following information:

bulletAn ordering of the tables referenced by the statement
bulletAn access method for each table mentioned in the statement
bulletA join method for tables affected by join operations in the statement
bulletData operations like filter, sort, or aggregation

In addition to the row source tree, the plan table contains information about the following:

bulletOptimization, such as the cost and cardinality of each operation
bulletPartitioning, such as the set of accessed partitions
bulletParallel execution, such as the distribution method of join inputs

Use the SQL script UTLXPLAN.SQL to create a sample output table called PLAN_TABLE in your schema. The exact name and location of this script depends on your operating system. For example, it is located under ORACLE_HOME\rdbms\admin on Windows systems. If you want an output table with a different name, you can rename it with the RENAME SQL statement. AFTER THAT, you can get the execution plan through the EXPLAIN PLAN SQL statement, from querying V$SQL_PLAN, or from SQL trace.

To explain a SQL statement, you can enter the following:

EXPLAIN PLAN FOR
<<SQL Statement>>

With multiple statements, you can specify a unique statement identifier and use that to identify your specific execution plan. 

EXPLAIN PLAN
    SET STATEMENT_ID = <<identifier>> FOR
<<SQL Statement>>

After you have explained the plan, you can use UTLXPLS.SQL provided by Oracle to display the most recent plan table output.

You can also control the report by setting the AUTOTRACE system variable. To use this feature, you must have the PLUSTRACE role granted to you and a PLAN_TABLE table created in your schema.

SET AUTOTRACE OFF

No AUTOTRACE report is generated. This is the default.

 
SET AUTOTRACE ON EXPLAIN

The AUTOTRACE report shows only the optimizer execution path.

 
SET AUTOTRACE ON STATISTICS

The AUTOTRACE report shows only the SQL statement execution statistics.

 
SET AUTOTRACE ON

The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.

 
SET AUTOTRACE TRACEONLY

Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any.

The execution plan consists of four columns displayed in the following order:
Column Name  Description  
ID_PLUS_EXP

Shows the line number of each execution step. 

 
PARENT_ID_PLUS_EXP

Shows the relationship between each step and its parent. This column is useful for large reports. 

 
PLAN_PLUS_EXP

Shows each step of the report. 

 
OBJECT_NODE_PLUS_EXP 

Shows the database links or parallel query servers used. 

For more details of the PLAN_TABLE columns, see Oracle reference.

Back to CS643 schedule