Explain Plan Oracle

Oracle's explain plan
Whenever you read or write data in Oracle, you do so by issuing an SQL statement. One of Oracle's task when it receives such a statement is to build a query execution plan. An execution plan defines how Oracle finds or writes the data. For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these isused. All this is contained in an execution plan.
If one wants to explore such an execution plan, Oracle provides the SQL statement EXPLAIN PLAN to determine this.
The general syntax of EXPLAIN PLAN is:
explain plan for your-precious-sql-statement;
If you do an EXPLAIN PLAN, Oracle will analyze the statment and fill a special table with the Execution plan for that statement. You can indicatewhich table has to be filled with the following SQL command:
explain plan into table_name for your-precious-sql-statement;
If you omit the INTO TABLE_NAME clause, Oracle fills a table named PLAN_TABLE by default.

The Plan Table

The plan table is the table that Oracle fills when you have it explain an execution plan for an SQL statement. You must make sure such a plan table exists. Oracleships with the script UTLXPLAN.SQL which creates this table, named PLAN_TABLE (which is the default name used by EXPLAIN PLAN). If you like, however, you can choose any other name for the plan table, as long as you have been granted insert on it and it has all the fields as here.

The fields (attributes) within the plan table

Arguably, the most important fields within the plan table areoperation, option, object_name, id, and parent_id. The pair operation and object_name define what operation would be done on (or with) object_name. If an operation has an id which other operations have as parent_id, it means the other operations feed their result to the parent.
Possible values for operation are:
• DELETE STATEMENT
• INSERT STATEMENT
• SELECT STATEMENT
• UPDATESTATEMENT
• AND-EQUAL
• CONNECT BY
• CONCATENATION
• COUNT
• DOMAIN INDEX
• FILTER
• FIRST ROW
• FOR UPDATE
• HASH JOIN
• INDEX
• INLIST ITERATOR
• INTERSECTION
• MERGE JOIN
• MINUS
• NESTED LOOPS
• PARTITION,
• REMOTE
• SEQUENCE
• SORT
• TABLE ACCESS
• UNION
• VIEW
Option tells more about how anoperation would be done. For example, the operation TABLE ACCESS can have the options: FULL or BY ROWID or many others. Full in this case means, that the entire table is accessed (takes a long time if table is huge) whereas BY ROWID means, Oracle knows where (from which block) the rows are to be retrieved, which makes the time to access the table shorter.

dbms_xplan

As of 9i, dbms_xplan canbe used to format the plan table.

Operations

The following table is used to demonstrate EXPLAIN PLAN:
create table test_for_ep (a number, b varchar2(100));
Now, let's explain the plan for selecting everything on that table:
delete plan_table;
explain plan for select /*+ rule */ * from test_for_ep where a = 5;

Displaying the execution plan

In order to view the explained plan, wehave to query the plan table:
select
substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",
object_name "Object"
from
plan_table
start with id = 0
connect by prior id=parent_id;
This statement is a simplified version of utlxpls.sql. utlxpls.sql is a script that Oracle ships.
Here's the outputof the explain plan:
SELECT STATEMENT ()
TABLE ACCESS (FULL) TEST_FOR_EP
First, take a look at the indention: TABLE ACCESS is indented right. In an explain plan output, the more indented an operation is, the earlier it is executed. And the result of this operation (or operations, if more than one have are equally indented AND have the same parent) is then feeded to the parent...