Explain Plan Utility

A handy Explain Plan script for SQL*Plus. Gets the plan for the current contents of the SQL*Plus buffer, i.e. whatever statement you've most recently run or listed out (you don't have to run the SQL to see the plan). After displaying the plan, it sets the buffer back how it was so you can continue working. Just type:

@xplan

-- xplan.sql
-- William Robertson - www.williamrobertson.net
-- Transparent "Explain Plan" utility for SQL*Plus.
--
-- Usage:
-- @xplan
-- Reports the execution plan of the current SQL buffer (i.e. the most recent SQL
-- statement to be run, edited, fetched etc (not necessarily run), and then places it
-- back in the buffer, as far as possible leaving everything the way it was.
--
-- Notes:
-- 2004/02/29: Changed to use DBMS_XPLAN.
-- 2007/03/08: Uses SYS.PLAN_TABLE$ if available (sometimes an old plan_table is still present)
-- 2007/03/15: Derives OS-specific OS commands by checking SQL*Plus executable extension in v$session.
-- 2008/01/31: Refined OS checks so don't need access to v$ tables;
--             Also now specify ".lst" extension for SPOOL commands as Windows defaults to uppercase .LST and DEL is case-sensitive.
-- 2008/02/12  Accepts optional "format" parameter for DBMS_SQL.DISPLAY_CURSOR(), e.g. @xplan all

set term off
store set sqlplus_settings.sql replace
def PLAN_TABLE = sys.plan_table$

ttitle off
set pause off
set feed off
set verify off
set timing off
set pages 999
set trimout on
set trimspool on
set long 2000
set autotrace off
set lines 150
set flagger off
set tab off
set serverout on size 10000

col QUERY_PATH format a70 hea "Query Path"
col STATEMENT_ID new_value STATEMENT_ID
col 1 new_value 1
col FORMAT_OPTIONS new_value FORMAT_OPTIONS
col DELETE_COMMAND new_value DELETE_COMMAND
col LIST_COMMAND new_value LIST_COMMAND
col OPTIMIZER format a9

break on report
comp sum label '' of cost on report

0 explain plan set statement_id = '&STATEMENT_ID' into &PLAN_TABLE for

save xplan.buf repl

-- "_O_VERSION" is predefined in SQL*Plus from around 10.1 and gives more information than v$version etc
-- But just in case this is an old version of SQL*Plus, initialise it to null if it is undefined:
col dbversion new_value _o_version
select '' as dbversion from dual where 1=2;

-- Now give _o_version a value from v$version if it's empty (i.e. if we created it empty above):
select banner as dbversion from V$version where '&_O_VERSION' is null and banner like 'Oracle Database %';

-- savepoint xplan;

-- Initialise "&1" in case no options were specified (&1 => "format" option of DBMS_XPLAN.DISPLAY_CURSOR):
select dummy as "1" from dual where 1 = 2;

-- Generate unique statement_id for plan:
select user||to_char(sysdate,'DDMMYYHH24MISS') statement_id
     , coalesce(trim('&1'), 'all -projection') as format_options
from   dual;

undef 1

-- Define OS commands for showing and deleting files:
select case os when 'MSWIN' then 'type' else 'cat' end as list_command
     , case os when 'MSWIN' then 'del'  else 'rm'  end as delete_command
from   ( select case
                    when lower(sys_context('userenv','client_program_name')) like '%.exe'
                    or   sys_context('userenv','host') like '%\%'
                        then 'MSWIN'
                end as os
         from   dual );

delete &PLAN_TABLE where statement_id = '&STATEMENT_ID';

get xplan.buf nolist

spool xplan_errors.lst
@xplan.buf
spool off

set term on

spool xplan.lst

prompt &_o_version

declare
    dbversion varchar2(20);
    dbcompatibility varchar2(20);
begin
    dbms_utility.db_version(dbversion, dbcompatibility);
    -- dbms_output.put_line('Oracle database version ' || dbversion);
    if dbcompatibility <> dbversion then
        dbms_output.put_line('Compatibility is set to ' || dbcompatibility);
    end if;
end;
/
   
set hea off

select * from table(dbms_xplan.display('&PLAN_TABLE','&STATEMENT_ID', '&format_options'));

set doc off

host &LIST_COMMAND xplan_errors.lst
host &DELETE_COMMAND xplan_errors.lst

spool off

set term off

set feed on hea on 
--rollback to xplan;

get xplan.buf nolist 
l1 
del 

clear breaks
undef STATEMENT_ID 
-- undef format_options
undef 1
@sqlplus_settings.sql
ho &DELETE_COMMAND sqlplus_settings.sql
ho &DELETE_COMMAND xplan.lst

set term on

Source code: //www.williamrobertson.net/code/xplan.sql