Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle EXPLAIN PLAN Statement      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: EXPLAIN_PLAN

Definition:
In Oracle PL/SQL, the term EXPLAIN PLAN is a statement that allows you to see the execution plan for a given SQL statement. This ability can be extremely useful in order to optimize queries for both speed and performance.

An execution plan (sometimes also called a query execution plan) is the sequence of operations that Oracle performs when it reads or writes data resulting from a SQL statement.

The execution plan defines or maps out how Oracle finds (retrieves) or writes the data. This may include decisions on whether or not to use indexes, the order that nested statements or sub-queries will be executed, and other factors. Where there are multiple indexes, the execution plan determines which one(s) are used.

Oracle provides the EXPLAIN PLAN function so you may see the details of the execution plan. To use EXPLAIN PLAN, you would issue a statement similar to this:

EXPLAIN PLAN FOR <sql statement to analyze>

When you do an EXPLAIN PLAN, Oracle analyzes the statment and populates a table named PLAN_TABLE with information on the execution plan for the statement. If desired, you can specify an alternate table to be used by issuing the following statement:

EXPLAIN PLAN INTO <table_name> FOR <sql statement to analyze>

To use an alternate table, the table must exist, you must have the INSERT privilege on it, and it must have all of the following fields:

Oracle 9i PLAN_TABLE

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,
ID NUMBER(38),
PARENT_ID NUMBER(38),
POSITION NUMBER(38),
COST NUMBER(38),
CARDINALITY NUMBER(38),
BYTES NUMBER(38),
OTHER_TAG VARCHAR2(255),
PARTITION_START VARCHAR2(255),
PARTITION_STOP VARCHAR2(255),
PARTITION_ID NUMBER(38),
OTHER LONG,
DISTRIBUTION VARCHAR2(30)


In Oracle 10g and above the PLAN_TABLE is a synonym, and contains the following fields:

Oracle 10g PLAN_TABLE

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


Generally, the most useful and informative fields in the plan table are operation, option, object_name, id, and parent_id.

Operation and object_name define what kind of operation would be performed on (or with) object_name.

If an operation has an id which other operations have as a parent_id, this indicates the other operations which feed their result to the parent.

Option explains more about how an operation would be performed when the query was executed. This can be a valuable source of information when optimizing queries.

For example, the operation TABLE ACCESS can include the options FULL, BY ROWID (among many others). In this case FULL indicates that the entire table would be accessed (which may impair performance if the table is large).

If it is of the type BY ROWID, that shows that Oracle knows where the rows are to be retrieved from (i.e. which block or blocks), which normally greatly reduces the time it takes to access the table and retrieve the data.

Related Code Snippets:
  • Explain Plan - EXPLAIN PLAN output shows how Oracle executes SQL statements and displays the execu...
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 208 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?