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