Explain Plan

To Explain Plan

EXPLAIN PLAN FOR <Any Statement>

Eg:

EXPLAIN PLAN FOR

SELECT * FROM Emp

You can see the plan by selecting the PLAN_TABLE

SELECT * FROM PLAN_TABLE;

Or you can also use

SELECT lpad(' ',level-1)||operation||' '||options||' '||

        object_name "Plan"

   FROM plan_table

CONNECT BY prior id = parent_id

        AND prior statement_id = statement_id

  START WITH id = 0 AND statement_id = '&1'

  ORDER BY id;

You can also assign a name for the statement by

EXPLAIN PLAN SET statement_id = <statement Name> FOR <Statement>;

Example

EXPLAIN PLAN SET statement_id = 'example_plan1' FOR

SELECT full_name FROM per_all_people_f

 WHERE UPPER(full_name) LIKE 'Pe%' ;