Plan Table

Plan Table is a table which will be created to see the execution method of a SQL script. Using the execution method we can optimize the query. This is mostly used during performance tunning.

EXPLAIN PLAN is the utility used by oracle to know the execution path of the SQL query before actually executing it.

There are many third party to tool to know the performance of the query and the execution plan.
TOAD is one of such tool

Let us see how to view the explain plan in TOAD which is used while performance tunning

1. We should create a plan table to view the explain plan of the query
The script for creating a plan table will be in the following path
$ORACLE_HOME/rdbms/admin/ and the file name is utlxplan.sql
This script is used to create the plan table ( PLAN_TABLE)

2. Change the setting in your TOAD
Open the TOAD and go to View -> Options


Then go to Oracle -> General



In that give the schema and plan table name.
Schema : APPS
Table Name : PLAN_TABLE

3 .To view the explain plan
Create a query and click the explain plan tab in TOAD. Now you can see the execution method of the table which is used to optimize and tune the query


Errors while viewing Explain Plan
We may get two types of errors while viewing explain plan
1. ORA-02404: specified plan table not found
To resolve this error create the PLAN_TABLE in APPS schema

2.ORA-00904: "PARTITION_STOP": invalid identifier.
This indicates that the plan table was not properly created properly. To create the plan table execute the following query from the specified path
$ORACLE_HOME/rdbms/admin/utlxplan.sql

Use this code to create PLAN_TABLE if nothing works

CREATE TABLE PLAN_TABLE
(
  STATEMENT_ID       VARCHAR2(30 BYTE),
  PLAN_ID            NUMBER,
  TIMESTAMP          DATE,
  REMARKS            VARCHAR2(4000 BYTE),
  OPERATION          VARCHAR2(30 BYTE),
  OPTIONS            VARCHAR2(255 BYTE),
  OBJECT_NODE        VARCHAR2(128 BYTE),
  OBJECT_OWNER       VARCHAR2(30 BYTE),
  OBJECT_NAME        VARCHAR2(30 BYTE),
  OBJECT_ALIAS       VARCHAR2(65 BYTE),
  OBJECT_INSTANCE    INTEGER,
  OBJECT_TYPE        VARCHAR2(30 BYTE),
  OPTIMIZER          VARCHAR2(255 BYTE),
  SEARCH_COLUMNS     NUMBER,
  ID                 INTEGER,
  PARENT_ID          INTEGER,
  DEPTH              INTEGER,
  POSITION           INTEGER,
  COST               INTEGER,
  CARDINALITY        INTEGER,
  BYTES              INTEGER,
  OTHER_TAG          VARCHAR2(255 BYTE),
  PARTITION_START    VARCHAR2(255 BYTE),
  PARTITION_STOP     VARCHAR2(255 BYTE),
  PARTITION_ID       INTEGER,
  OTHER              LONG,
  DISTRIBUTION       VARCHAR2(30 BYTE),
  CPU_COST           INTEGER,
  IO_COST            INTEGER,
  TEMP_SPACE         INTEGER,
  ACCESS_PREDICATES  VARCHAR2(4000 BYTE),
  FILTER_PREDICATES  VARCHAR2(4000 BYTE),
  PROJECTION         VARCHAR2(4000 BYTE),
  TIME               INTEGER,
  QBLOCK_NAME        VARCHAR2(30 BYTE),
  OTHER_XML          CLOB
)