EXPLAIN PLAN   «Prev  Next»

Lesson 3Oracle PLAN table
ObjectiveCreate a PLAN table in Oracle.

Oracle PLAN Table and EXPLAIN PLAN utility

What is the PLAN table?

While there are many tools that can be used to display the access path to Oracle tables, the oldest and most important is the Oracle EXPLAIN PLAN utility. However, to run EXPLAIN PLAN, we must first execute an Oracle script, utlxplan.sql, to create a table called the PLAN table. The utlxplan.sql script always exists in the $ORACLE_HOME/rdbms/admin directory. Once we have created the PLAN table, we can then issue an EXPLAIN PLAN statement to populate the PLAN table and then issue SQL queries against the PLAN table to see the access path to the data.
  1. EXPLAIN PLAN: This is an Oracle utility to show the access path.
  2. PLAN TABLE: This is an internal table that contains the display of the execution plan.
View the Code below to see how to execute the utlxplan.sql script.
SQL*Plus: Release 3.3.4.0.0 - Production on Mon Oct 18 14:20:15 1999

Copyright (c) Oracle Corporation 1979, 1996.  All rights reserved.

Connected to: Oracle7 Server Release 7.3.4.3.0 - Production
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.4.3.0 - Production

SQL> @$ORACLE_HOME/rdbms/admin/utlxplan                                 

Access Path SQL Statement
To ensure that the PLAN table was created by running the utlxplan.sql script, we issue the DESCRIBE statement.
SQL> desc plan_table;
 Name                            Null?    Type
 ------------------------------- -------- ----
 STATEMENT_ID                             VARCHAR2(30)
 TIMESTAMP                                DATE
 REMARKS                                  VARCHAR2(80)
 OPERATION                                VARCHAR2(30)
 OPTIONS                                  VARCHAR2(30)
 OBJECT_NODE                              VARCHAR2(128)
 OBJECT_OWNER                             VARCHAR2(30)
 OBJECT_NAME                              VARCHAR2(30)
 OBJECT_INSTANCE                          NUMBER(38)
 OBJECT_TYPE                              VARCHAR2(30)
 OPTIMIZER                                VARCHAR2(255)
 SEARCH_COLUMNS                           NUMBER(38)
 ID                                       NUMBER(38)
 PARENT_ID                                NUMBER(38)
 POSITION                                 NUMBER(38)
 COST                                     NUMBER(38)
 CARDINALITY                              NUMBER(38)
 BYTES                                    NUMBER(38)
 OTHER_TAG                                VARCHAR2(255)
 OTHER                                    LONG

In most cases, you will be using a GUI tool or a pre-written query to access rows from the PLAN table, but now that you know how to create a PLAN table, we are ready to run the EXPLAIN PLAN utility with SQL. The next lesson looks at how we use the EXPLAIN PLAN statement in conjunction with the PLAN table.

PLAN_TABLE Output Table

The PLAN_TABLE is automatically created as a global temporary table to hold the output of an EXPLAIN PLAN statement for all users. PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans. While a PLAN_TABLE table is automatically set up for each user, you can use the SQL script utlxplan.sql to manually create a local PLAN_TABLE in your schema. The exact name and location of this script depends on your operating system. On Unix, it is located in the $ORACLE_HOME/rdbms/admin directory. For example, run the commands in Example 5-3 from a SQL*Plus session to create the PLAN_TABLE in the HR schema.
Example 5-3: Creating a PLAN_TABLE
CONNECT HR/your_password
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created.

Oracle Corporation recommends that you drop and rebuild your local PLAN_TABLE table after upgrading the version of the database because the columns might change. This can cause scripts to fail or cause TKPROF to fail, if you are specifying the table. If you want an output table with a different name, first create PLAN_TABLE manually with the utlxplan.sql script and then rename the table with the RENAME SQL statement. For example:
RENAME PLAN_TABLE TO my_plan_table;

Advanced Oracle SQL Tuning