Oracle : Explain Plan

To Create Plan Table

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(38),
OBJECT_TYPE VARCHAR2(30),
OPTIMIZER VARCHAR2(255),
SEARCH_COLUMNS NUMBER,
ID NUMBER(38),
PARENT_ID NUMBER(38),
POSITION NUMBER(38),
COST NUMBER(38),
CARDINALITY NUMBER(38),
BYTES NUMBER(38),
OTHER_TAG VARCHAR2(255),
PARTITION_START VARCHAR2(255),
PARTITION_STOP VARCHAR2(255),
PARTITION_ID NUMBER(38),
OTHER LONG,
DISTRIBUTION VARCHAR2(30)
);


explain.sql :-

To do explain plan on a Table :-
-----------------------------------
1) Truncate the Plan Table or Delete the records

SQL>
delete from plan_table where statement_id = 'PLN_SARAN';


2) Run the Explain Plan
SQL>
explain plan set statement_id = 'PLN_SARAN'
for
SELECT * FROM TAB;


3) View the Output
SQL>
select substr (lpad(' ', level-1)||operation||' (' || options || ')',1,30 ) "Operation",
object_name "Object" from plan_table
start with id = 0 and statement_id = 'PLN_SARAN'
connect by prior id=parent_id and statement_id = 'PLN_SARAN';
or
SQL>
select * from table(DBMS_XPLAN.DISPLAY('PLAN_TABLE','PLN_SARAN','ALL'));
this will work for Oracle 9i version or later