EXPLAIN PLAN

EXPLAIN PLAN

** Creating a PLAN_TABLE

CONNECT HR/your_password 
@$ORACLE_HOME/rdbms/admin/utlxplan.sql 

Table created.

CREATE TABLE 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,
  object_type                VARCHAR2(30),
  optimizer                  VARCHAR2(255),
  search_columns             NUMBER,
  id                         NUMBER,
  parent_id                  NUMBER,
  position                   NUMBER,
  other                      LONG
 )

** If you want an output table with a different name

RENAME PLAN_TABLE TO my_plan_table;

** Next, you can run the following script to get a list
of the steps that Oracle will perform in order to execute
your query:

set echo on

delete from plan_table
 where statement_id = 'MINE';
commit;
COL operation   FORMAT A30
COL options     FORMAT A15
COL object_name FORMAT A20
EXPLAIN PLAN set statement_id = 'MINE' for

/* ------ Your SQL here ------*/
select * from scott.salgrade
/*----------------------------*/

/

set echo off

select operation, options, object_name
from plan_table
where statement_id = 'MINE'
start with id = 0
connect by prior id=parent_id
and prior statement_id = statement_id;
set echo on

** Displaying PLAN_TABLE Output with 
DBMS_XPLAN.DISPLAY procedure

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

SELECT PLAN_TABLE_OUTPUT 
FROM TABLE
(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));

To learn more:Tutorial