How to Obtain a Formatted Explain Plan

Version 10.2 and higher

Last Executed SQL

In 10.2 and above, you can pull execution plans from the library cache if the SQL has already been executed (in addition to the standard explain plan option from earlier releases). To get the plan of the last executed SQL issue the following:

set linesize 150
set pagesize 2000
select * from table(dbms_xplan.display_cursor(null,null, 'ALL'));

SQL_ID and child number of the SQL are known

A SQL_ID can have multiple children with different characteristics. You can identify the child number by selecting from V$SQL as follows:

SELECT sql_id, hash_value, child_number, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE '%&Some_Identifiable_String%'
/

The first child for a cursor will have a CHILD_NUMBER of zero.
Once you have this information, you can use dbms_xplan.display_cursor as follows:

set linesize 150
set pagesize 2000
select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD));

gather_plan_statistics hint

The gather_plan_statistics hint with some additional options may also provide run time statistics:e.g:

select /*+ gather_plan_statistics */ col1, col2 etc.....

set linesize 150
set pagesize 2000
select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD,'ALL IOSTATS LAST'))

Make sure the syntax is correct in the hint /*+ gather_plan_statistics */.  Do not use explain plan for:

 

EXPLAIN PLAN FOR
    SELECT /* +GATHER_PLAN_STATISTICS */ *  FROM tab1
    WHERE...

Otherwise following error will occur:

  

 - Warning: basic plan statistics not available. These are only collected when:
  * hint 'gather_plan_statistics' is used for the statement or
  * parameter 'statistics_level' is set to 'ALL', at session or system level

Correct syntax is following:

  

SELECT /*+ GATHER_PLAN_STATISTICS */ *  FROM tab1
    WHERE
..

select * from table(dbms_xplan.display_cursor(null,null,'advanced ALLSTATS LAST'));

Plan from Memory

For SQL ID :
select * from table(dbms_xplan.display_cursor('&sql_id'));
select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL'));

For SQL ID, Child Cursor :
select * from table(dbms_xplan.display_cursor('&sql_id', &child_number, 'ALL'));

For SQL Text :
select t.*
from v$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'ALL')) t
where s.sql_text like '%&querystring%';

From AWR

For SQL ID :
select * from table(dbms_xplan.display_awr('&sql_id')) ;
select * from table(dbms_xplan.display_awr('&sql_id', NULL, NULL, 'ALL')) ;

For SQL ID, Plan Hash Value in the current database :
select * from table(dbms_xplan.display_awr('&sql_id', '&plan_hash_value', NULL, 'ALL')) ;

For SQL ID, Plan Hash Value in a different database ID :
select * from table(dbms_xplan.display_awr('&sql_id', '&plan_hash_value', &dbid, 'ALL')) ;

For SQL ID that was executed during a certain period :
select t.*
from (select distinct sql_id, plan_hash_value, dbid
from dba_hist_sqlstat
where sql_id = '&sql_id'
and snap_id between &begin_snap and &end_snap) s,
table(dbms_xplan.display_awr(s.sql_id, s.plan_hash_value, s.dbid, 'ALL')) t;

For SQL Text :
select t.*
from (select distinct r.sql_id sql_id, r.plan_hash_value plan_hash_value, r.dbid dbid
from dba_hist_sqltext q, dba_hist_sqlstat r
where q.sql_id = r.sql_id
and q.sql_text like '%&querystring%') s,
table(dbms_xplan.display_awr(s.sql_id, s.plan_hash_value, s.dbid, 'ALL')) t;

From SQL Tuning Set (STS)

Note : STS owner is the current user by default.

For SQL ID in a STS :
select * from table (dbms_xplan.display_sqlset( '&sts_name','&sql_id'));

For All Statements in a STS :
select t.*
from dba_sqlset_statements s, table(dbms_xplan.display_sqlset(s.sqlset_name, s.sql_id, s.plan_hash_value, 'ALL', s.sqlset_owner)) t
where s.sqlset_name = '&sts_name';

For SQL ID, Plan Hash Value in a STS :
select * from table (dbms_xplan.display_sqlset( '&sts_name','&sql_id', '&plan_hash_value', 'ALL'));

For SQL ID, Plan Hash Value, STS Owner :
select * from table (dbms_xplan.display_sqlset( '&sts_name','&sql_id', '&plan_hash_value', 'ALL', '&sts_owner'));

For SQL Text in a STS :
select t.*
from dba_sqlset_statements s, table(dbms_xplan.display_sqlset(s.sqlset_name, s.sql_id, s.plan_hash_value, 'ALL', s.sqlset_owner)) t
where s.sqlset_name = '&sts_name'
and s.sql_text like '%&querystring%';

From SQL Plan Baseline

For SQL Handle :
select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle'));

For SQL Handle, Plan Name :
select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle', '&plan_name', 'ALL'));

For SQL Text :
select t.*
from (select distinct sql_handle, plan_name
from dba_sql_plan_baselines
where sql_text like '%&querystring%') s,
table(dbms_xplan.display_sql_plan_baseline(s.sql_handle, s.plan_name, 'ALL')) t;