| Lesson 3 | Oracle PLAN table |
| Objective | Create 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.
- EXPLAIN PLAN: This is an Oracle utility to show the access path.
- 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