Using Oracle’s explain plan

Use the EXPLAIN PLAN statement to determine the execution plan Oracle follows to execute a specified SQL statement. This statement inserts a row describing each step of the execution plan into a specified table. You can also issue the EXPLAIN PLAN statement as part of the SQL trace facility.

If you are using cost-based optimization, then this statement also determines the cost of executing the statement. If any domain indexes are defined on the table, then user-defined CPU and I/O costs will also be inserted.

The definition of a sample output table PLAN_TABLE is available in a SQL script on your distribution media. Your output table must have the same column names and datatypes as this table. The common name of this script is UTLXPLAN.SQL. The exact name and location depend on your operating system.

The DBMS_XPLAN package provides an easy way to format the output of the EXPLAIN PLAN command. Below is an sample SQL which will generate an explain plan and store in the plan table:

EXPLAIN PLAN FOR

SELECT *

FROM emp e, dept d

WHERE e.deptno = d.deptno

  AND e.ename='su';

Below is example of using the DBMS_XPLAN procedure and the output produced:

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

--------------------------------------------------------------------

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

--------------------------------------------------------------------

|   0 | SELECT STATEMENT     |             |     1 |    50 |     3 |

|*  1 | HASH JOIN            |             |     1 |    50 |     3 |

|*  2 | TABLE ACCESS FULL    | EMP         |     1 |    32 |     1 |

|   3 | TABLE ACCESS FULL    | DEPT        |     4 |    72 |     1 |

--------------------------------------------------------------------

Predicate Information (identified by operation id)

1 - access("E1"."DEPTNO"="D1"."DEPTNO")

2 - filter("E1"."ENAME"='su')