rem *********************************************************************
rem * Copyright © Oracle-Consultant.co.uk 2001, all rights reserved     *
rem *                                                                   *
rem * Name     : explain.sql                                            *
rem * Synopsis : Script shows explain plan without running statement.   *
rem *            Note that you must have a PLAN_TABLE to run this.      *
rem * Source   : http://www.oracle-consultant.co.uk                     *
rem *                                                                   *
rem * Oracle-Consultant.co.uk are not responsible for any liability     *
rem * that may arise from the use of this code. Support can be obtained *
rem * by emailing script_support@oracle-consultant.co.uk                *
rem * Note: This script is best viewed in a fixed-width font.           *
rem *********************************************************************

save buffer_temp repl

truncate table plan_table
/

get buffer_temp nolist

1
c//explain plan set statement_id = 'XPLAIN' for /
/

select     lpad(' ', 2*(level - 1))||
           operation||' '||
           options||' '||
           object_name||' '||
           object_type||' '||
           object_instance||' '||
           decode(id, 0, 'Cost = '||position) "Query Plan"
from       plan_table
start with id = 0
connect by prior id = parent_id;

get buffer_temp nolist