Appendix B: Sample Scripts

---

Interpreting EXPLAIN PLAN

All of these scripts SELECT rows from the PLAN_TABLE containing Optimizer query plans generated by the EXPLAIN PLAN command.

explain plan set statement_id='TEST' for <SQL Statement; –a simple version COL Cost FORMAT 9990; COL Rows FORMAT 999990; COL Bytes FORMAT 99999990; COL Query FORMAT a64; SELECT cost "Cost", cardinality "Rows", bytes "Bytes"       ,operation||' '||options||' on '||object_name "Query" FROM plan_table ORDER BY id;     –for serial queries COL Query FORMAT a48; COL Pos FORMAT 990; COL Cost FORMAT 990; COL Rows FORMAT 999990; COL Bytes FORMAT 99999990; COL Sort FORMAT 99999990; COL IO FORMAT 9999990; COL CPU FORMAT 99999990; SELECT TRIM(LEVEL)||'. '||LPAD (' ', LEVEL – 1)||operation       ||' '||options||' on '||object_name "Query"       ,cost "Cost", cardinality "Rows", bytes "Bytes"       ,decode(level,1,0,position) "Pos", temp_space "Sort"       ,io_cost "IO", cpu_cost "CPU" FROM plan_table WHERE statement_id = 'TEST' CONNECT BY prior id = parent_id AND prior statement_id =    statement_id START WITH id = 0 AND statement_id = 'TEST' ORDER BY id; delete from plan_table where statement_id='TEST'; commit;     –a parallel query version COL Query FORMAT a60; COL Pos FORMAT 990; COL Cost FORMAT 990; COL Rows FORMAT 999990; COL Bytes FORMAT 99999990; COL Sort FORMAT 99999990; COL IO FORMAT 9999990; COL CPU FORMAT 99999990; SELECT TRIM(LEVEL)||'. '||LPAD (' ', LEVEL – 1)||operation||       ' '||options||' on '||object_name||' '||other_tag||'        '||partition_start||' '||partition_stop "Query"       ,cost "Cost", cardinality "Rows", bytes "Bytes"       ,decode(level,1,0,position) "Pos"       ,temp_space "Sort", io_cost "IO", cpu_cost "CPU" FROM plan_table WHERE statement_id = 'TEST' CONNECT BY prior id = parent_id AND prior statement_id =    statement_id START WITH id = 0 AND statement_id = 'TEST' ORDER BY id; delete from plan_table where statement_id='TEST'; commit;

Using the DBMS_XPLAN package.

–serial SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'serial'));     –parallel SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

---