exp.sql: using explain plan

Author: Robert Lawson
Environment: Oracle database, HPUX
Description: This code is easy way to see plan SQL executes.
Code Listing:

rem File   : exp.sql
rem Purpose: show explain plan for sql statement
rem Author : robert lawson Nov 8, 1995 (from Oracle Performance Tuning)
rem   EXPLAIN PLANT SET statement_id = 'exp' FOR ..your select..;
rem   change userid to your id, eg roblaw, as plan_table is common

set heading off
set linesize 132
-- set wrap on
set linesize 10000
set pagesize 50000
set long 500
set echo on
set termout on

DELETE from plan_table
WHERE statement_id = 'roblaw_exp';
commit;

EXPLAIN PLAN SET statement_id = 'roblaw_exp' FOR
SELECT
SUM(decode(order_type,'ORD',(nvl(qm.ext_qty,0) * od.qty_shipped),0)),
SUM(decode(order_type,'CM',(nvl(qm.ext_qty,0) * od.qty_shipped),
 'CN',(nvl(qm.ext_qty,0) * od.qty_shipped),0))
FROM oedetl od, oehead oh, quote_model_detail qm
WHERE
qm.plant =  '&o_plant' and
qm.component_no = '&o_item_no' and
oh.order_no = qm.quote_no and
oh.plant = qm.plant and
oh.order_type <> 'DM' and
trunc(oh.ship_dt) >=
 decode('&b_date',null,trunc(SYSDATE),to_date('&b_date','MM-DD-YYYY')) and
trunc(oh.ship_dt) <=  decode('&e_date',null,trunc(SYSDATE),to_date('&e_date','MM-DD-YYYY')) and od.order_no = qm.quote_no and od.line_no = qm.oedetl_line_no and od.plant = qm.plant;  -- Report out select  lpad(' ',2*(level-1)) operation' '         options' 'object_name         ' 'object_type' 'object_instance' '         decode(id,0,'Explain Plan: roblaw_exp'position) "Query Plan" from    plan_table         start   with id = 0         connect by prior id = parent_id and     statement_id = 'roblaw_exp';