An easier way to call dbms_xplan.display_cursor
Similar to xplan.sql, but calls
dbms_xplan.display_cursor instead of
(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),
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 statement: save xplanx.buf repl column sid new_value sid select sys_context('userenv','sid') as sid from dual; ho del xplanx_`sid..buf sqlplus_settings_`sid..tmp 2>NUL 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 = 200 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 appinfo off flush 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: @xplanx_`sid..buf col plan_table_output clear col plan_table_output format a`terminal_width set term on select p.plan_table_output from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +OUTLINE +NOTE -OUTLINE')) p; timing stop get xplanx_`sid..buf nolist ho ren sqlplus_settings_`sid..tmp sqlplus_settings.tmp ho del xplanx_`sid..buf @sqlplus_settings.tmp -- Note "del" command assumes Windows. Change to "rm" for Unix/Linux/Mac: ho del sqlplus_settings.tmp undef terminal_width set term on