Thursday, September 1, 2016

Simple script to help you get the execution plan




This little script can be handy to get the execution plan from any DB from 10.1 or higher.  10.1 was the first version to have the DISPLAY_CURSOR function in DBMS_XPLAN.  In 9 there was just DISPLAY for the explain plan.  I’ve tested it on 10.1 and 12, for 10.2 to 11.2 the options are pretty much the same so I believe that it will work fine.  The basics of the script are to get the current version of the instance then set the format options to be appropriate for the version.  Of course you may want to change the options to fit your needs, I put in what I believe are likely the common options most folks would be using. 



All you have to do is give it a SQL_ID and a CHILD_NUMBER.  If you don’t give a child number you will get all of the children for that SQL_ID, which of course maybe form that you can figure out which child you really want. 

I hope you find this useful.  It is a SQL*Plus script, I did test it in SQL*Developer (version 4.1.0.18) and it worked fine there.

-- File name getexeplan.sql
-- RVD August 2016
-- setting tab off generally makes output look better
set tab off
set termout off
set verify off
set echo off
set lines 200
set pages 1000
-- getting the current version to get the right format options
COLUMN FORMAT_OPTIONS NEW_VALUE FORMATOP
select
case substr(version,1,instr(version,'.',1,2)-1)
     when '10.1' then 'RUNSTATS_LAST'
     when '10.2' then 'TYPICAL ALLSTATS LAST PEEKED_BINDS'
     when '11.1' then 'TYPICAL ALLSTATS LAST PEEKED_BINDS'
     when '11.2' then 'TYPICAL ALLSTATS LAST PEEKED_BINDS'
     when '12.1' then 'TYPICAL ALLSTATS LAST PEEKED_BINDS ADAPTIVE'
     end FORMAT_OPTIONS
from v$instance;
set termout on
prompt ************************************************************
prompt Get Execution plan based on entered SQL_ID and CHILD NUMBER
prompt Format option is version based edit script to change options
prompt No entry for CHILD NUMBER will return all Children
prompt ************************************************************
accept psqlid   prompt 'Enter the SQL_ID       :'
accept pchildno prompt 'Enter the CHILD NUMBER : '
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR ('&psqlid','&PCHILDNO','&FORMATOP'))
/