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'))
/