EXPLAIN PLAN

View All Scripts Login to Run Script

Script Name EXPLAIN PLAN
Description Use the EXPLAIN PLAN statement to determine the execution plan Oracle Database follows to execute a specified SQL statement. This statement inserts a row describing each step of the execution plan into a specified table. It should be noted that Explain Plan has been deprecated and it is recommended that dbms_xplan be used.
Area SQL General
Contributor Mike Hichwa (Oracle)
Created Monday October 05, 2015

Statement 1

create table 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, 
        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), 
        other_xml          clob 
)

Table created.

Statement 2
remove old plans
```
delete from plan_table
```
```
0 row(s) deleted.
```

Statement 3

Create a named plan for a SQL SELECT statement

EXPLAIN PLAN   
    SET STATEMENT_ID = 'Tokyo'  
    FOR  
    select *   
    from hr.employees   
    WHERE department_id =    
           (SELECT department_id FROM hr.departments  
               WHERE location_id = 1200)

Statement processed.

Statement 4

Query plan table to show access paths and options.

SELECT LPAD('............................',2*(LEVEL-1))||operation operation, options,   
object_name, position   
    FROM plan_table   
    START WITH id = 0 AND statement_id = 'Tokyo'  
    CONNECT BY PRIOR id = parent_id AND statement_id = 'Tokyo'


OPERATION OPTIONS OBJECT_NAME POSITION
SELECT STATEMENT  -  - 2
..TABLE ACCESS BY INDEX ROWID BATCHED EMPLOYEES 1
....INDEX RANGE SCAN EMP_DEPARTMENT_IX 1
......TABLE ACCESS BY INDEX ROWID BATCHED DEPARTMENTS 1
........INDEX RANGE SCAN DEPT_LOCATION_IX 1

5 rows selected.

OPERATION	OPTIONS	OBJECT_NAME	POSITION
SELECT STATEMENT	-	-	2
..TABLE ACCESS	BY INDEX ROWID BATCHED	EMPLOYEES	1
....INDEX	RANGE SCAN	EMP_DEPARTMENT_IX	1
......TABLE ACCESS	BY INDEX ROWID BATCHED	DEPARTMENTS	1
........INDEX	RANGE SCAN	DEPT_LOCATION_IX	1

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database