| 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 2019
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
Without a tool to show the execution plan for a SQL statement, the DBA or programmer cannot hope to tune the SQL statement. It is rarely obvious by looking at raw SQL to infer how the optimizer will execute the statement, and so the DBA must make frequent use of EXPLAIN PLAN to show how Oracle is actually servicing the query. This is especially a problem with SQL hints where many programmers add a hint with a syntax error, and re-run the statement without checking to see if the execution plan has changed. In short, you should always get in the habit of
double-checking the execution plan for each and every SQL statement.
Cost Based Optimizer
When an SQL statement is passed to the server the Cost Based Optimizer (CBO) uses database statistics to create an execution plan which it uses to navigate through the data. Once you have highlighted a problem query the first thing you should do is EXPLAIN the statement to check the execution plan that the CBO has created. This will often reveal that the query is not using the relevant indexes, or indexes to support the
query are missing. Interpretation of the execution plan is beyond the scope of this article.
To ensure that the PLAN table was created by running the utlxplan.sql script, we issue the DESCRIBE statement.
SQL> desc plan_table;
| Name |
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;
Ad Advanced Oracle SQL Tuning