xplanx

An easier way to call dbms_xplan.display_cursor

Similar to xplan.sql, but calls dbms_xplan.display_cursor instead of Explain Plan. (I added the 'x' for 'extended', rather unimaginatively.)

How often have you wanted to get the extended plan using dbms_xplan.display_cursor and had to type out the whole thing laboriously? And then after doing that, realised you had forgotten to set serveroutput off, so SQL*Plus calls dbms_output.get_lines right after your query, ruining your test? And you don't really want all the query results scrolling up the screen either, so ideally you would set termout off as well - except that only works from a script, and not from the interactive prompt.

xplanx.sql saves your current settings, makes the settings it needs to, executes the contents of the current SQL buffer (i.e. the most recent SQL statement to be run, edited, fetched etc), invokes dbms_xplan.display_cursor and then places the statement back in the buffer so you can carry on working on it, as far as possible leaving everything the way it was.

-- xplanx.sql
-- William Robertson - www.williamrobertson.net
-- Transparent "dbms_xplan.display_cursor" utility for SQL*Plus.
--
-- Usage:
-- @xplanx
-- Executes the contents of the current SQL buffer (i.e. the most recent SQL statement to be run, edited, fetched etc,
-- invokes dbms_xplan.display_cursor() and then places the statement back in the buffer, as far as possible leaving everything
-- the way it was, including any serveroutput setting (which it disables before executing the statement).

set term off
store set sqlplus_settings.tmp replace
set define `

-- Capture the current SQL buffer (use generic filename because we can't look up the session ID without losing the current SQL):
save xplanx.buf repl

-- Now we can capture session ID and use it to rename xplan.buf and sqlplus_settings.tmp to xplanx_.buf and sqlplus_settings_.tmp,
-- so that other sessions can call xplanx without overwriting this session's saved details:
col sid new_value sid
select sys_context('userenv','sid') as sid from dual;
-- Delete any old tempfiles:
ho prompt > xplanx_`sid..buf
ho prompt > sqlplus_settings_`sid..tmp
ho del xplanx_`sid..buf
ho del sqlplus_settings_`sid..tmp
-- Give generic tempfiles session-specific names to allow this to be used by multiple sessions:
ho ren xplanx.buf xplanx_`sid..buf
ho ren sqlplus_settings.tmp sqlplus_settings_`sid..tmp

-- Adjust terminal window size to match linesize below
define terminal_width = 210

set linesize `terminal_width
set pagesize 0 trimspool on heading off pause off feed off verify off timing off autotrace off flagger off tab off trimout on serverout off arraysize 200 flush off appinfo off

ttitle off

-- To do: detect the current setting before altering, then restore this at the end:
alter session set statistics_level = all;

timing start "Query execution time"
-- Execute the SQL buffer that was captured earlier:
-- set feed on timi on head on autotrace off
-- spool xplanx.log

prompt Calling xplanx_`sid..buf
@xplanx_`sid..buf
-- ho type xplan.buf
-- spool off
-- set feed off timi off head on autotrace off

col plan_table_output format a`terminal_width
set term on

timing stop

prompt All rows fetched. Calling dbms_xplan.display_cursor:
select p.plan_table_output
from   table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +OUTLINE +ADAPTIVE +PARTITION +NOTE')) p;

get xplanx_`sid..buf nolist

@sqlplus_settings_`sid..tmp
set term on
-- Note "del" command assumes Windows. Change to "rm" for Unix/Linux/Mac or see xplan.sql for elaborate dynamic approach:
host del sqlplus_settings_&sid..tmp xplanx_&sid..buf
undef terminal_width

-- host type xplanx.log