rem -----------------------------------------------------------------------
rem Filename:   x_plan.sql
rem Purpose:    Explain SQL Execution plan
rem Date:       12-Apr-1998
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

DELETE FROM plan_table WHERE statement_id = 'XXX';
COMMIT;

EXPLAIN PLAN
  SET STATEMENT_ID = 'XXX'
  FOR
select * from dual            -----< Insert your SQL statement here <-----
/
 
-- Oracle 9.2 and above:
set linesize 132
SELECT * FROM TABLE(dbms_xplan.DISPLAY('PLAN_TABLE','XXX'));

set doc off
/* Earlier Oracle versions (9.0 and below):
column operation format a16
column options format a15
column object_name  format a20
column id  format 99
select lpad(' ',2*(level-1))||operation||' '||options||' '||object_name||' '||
       -- decode(partition_id, NULL, '',
       --         'PART:'||lower(partition_start||'-'||partition_stop))||
       decode(optimizer, NULL, '', ' ['||optimizer||']')||
       decode(id,0,' Cost=' ||nvl(position,   0)||
                   ' Rows=' ||nvl(cardinality,0)||
                   ' Bytes='||nvl(bytes,      0)) "Query Plan"
from   plan_table
where                                statement_id = 'XXX'
start   with     id = 0          and statement_id = 'XXX'
connect by prior id = parent_id  and statement_id = 'XXX'
/
*/