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