How to Identify Active Lines in an Adaptive Execution Plan in DBA_HIST_SQL_PLAN

When investigating performance problems I spend a lot of my time profiling ASH and AWR data. I sometimes want to join back to captured plans in DBA_HIST_SQL_PLAN to see how an object was access, or which index was used, or how many rows the optimizer thought it would get.
From Oracle 12c, we can get adaptive execution plans where the optimizer considers different plans at runtime. Mostly it is a choice between either a nested loop or a hash join of tables.
For example, in the below execution plan, DBMS_XPLAN includes a note to say that inactive plans with a dash.  Thus we can see that the nested loop lookup of PS_LEDGER using the PSCLEDGER index was rejected in favour of a Bloom filter on a full scan of the ledger table.

SQL_ID bk26thygs9c8n

Plan hash value: 38031479
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                    |       |       | 18275 (100)|       |          |       |
|   1 |  SORT GROUP BY                         |                    |     1 |   135 | 18275   (1)| 00:00:01 |       |       |
|-  2 |   HASH JOIN                            |                    |     1 |   135 | 18274   (1)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                        |                    |     1 |   135 | 18274   (1)| 00:00:01 |       |       |
|-  4 |     STATISTICS COLLECTOR               |                    |       |       |            |       |          |       |
|   5 |      TABLE ACCESS STORAGE FULL         | PS_ELIM_CF_SEL2002 |     1 |    59 |    25   (0)| 00:00:01 |       |       |
|   6 |     PARTITION RANGE OR                 |                    |     1 |    76 | 18249   (1)| 00:00:01 |KEY(OR)|KEY(OR)|
|   7 |      PARTITION LIST ITERATOR           |                    |     1 |    76 | 18249   (1)| 00:00:01 |   KEY |   KEY |
|   8 |       TABLE ACCESS BY LOCAL INDEX ROWID| PS_LEDGER          |     1 |    76 | 18249   (1)| 00:00:01 |   KEY |   KEY |
|   9 |        INDEX RANGE SCAN                | PSCLEDGER          |     1 |       | 18228   (1)| 00:00:01 |   KEY |   KEY |
|- 10 |    PARTITION RANGE OR                  |                    |     1 |    76 | 18249   (1)| 00:00:01 |KEY(OR)|KEY(OR)|
|- 11 |     PARTITION LIST JOIN-FILTER         |                    |     1 |    76 | 18249   (1)| 00:00:01 |:BF0000|:BF0000|
|- 12 |      TABLE ACCESS STORAGE FULL         | PS_LEDGER          |     1 |    76 | 18249   (1)| 00:00:01 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------------------------------------
…
Note
-----
…
   - this is an adaptive plan (rows marked '-' are inactive)

When I come to look at this plan in DBA_HIST_SQL_PLAN, I will find all 14 lines but there is no column in the view that indicates whether the line is active in the adaptive plan.

select id, operation, options, object_name, cardinality, cost from dba_hist_sql_plan 
where sql_id = 'bk26thygs9c8n' and plan_hash_value = 38031479

        ID OPERATION                      OPTIONS                        OBJECT_NAME        CARDINALITY       COST
---------- ------------------------------ ------------------------------ ------------------ ----------- ----------
         0 SELECT STATEMENT                                                                                1529564
         1 SORT                           GROUP BY                                                    1    1529564
         2 HASH JOIN                                                                                359    1529563
         3 PART JOIN FILTER               CREATE                         :BF0000                    359    1529563
         4 NESTED LOOPS                                                                             359    1529563
         5 STATISTICS COLLECTOR
         6 TABLE ACCESS                   STORAGE FULL                   PS_ELIM_CF_SEL2002         360        172
         7 PARTITION RANGE                OR                                                          1    1529289
         8 PARTITION LIST                 ITERATOR                                                    1    1529289
         9 TABLE ACCESS                   BY LOCAL INDEX ROWID           PS_LEDGER                    1    1529289
        10 INDEX                          RANGE SCAN                     PSCLEDGER
        11 PARTITION RANGE                OR                                                   16845834    1529289
        12 PARTITION LIST                 JOIN-FILTER                                          16845834    1529289
        13 TABLE ACCESS                   STORAGE FULL                   PS_LEDGER             16845834    1529289

The information about whether a line in the execution plan is active and whether it should be displayed by DBMS_XPLAN is encoded in the display map inside an XML structure stored the OTHER_XML column on line 1 of the plan.

<other_xml>
  <info type="nodeid/pflags">000000000001</info>
…
  <info type="adaptive_plan" note="y">yes</info>
  <outline_data>
    <hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint>
    <hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('12.1.0.2')]]></hint>
    <hint><![CDATA[DB_VERSION('12.1.0.2')]]></hint>
    <hint><![CDATA[OPT_PARAM('_unnest_subquery' 'false')]]></hint>
…
  </outline_data>
  <display_map>
    <row op="1" dis="1" par="0" prt="0" dep="1" skp="0"/>
    <row op="2" dis="2" par="1" prt="0" dep="2" skp="0"/>
    <row op="3" dis="3" par="2" prt="0" dep="3" skp="0"/>
    <row op="4" dis="3" par="3" prt="0" dep="3" skp="1"/>
    <row op="5" dis="3" par="3" prt="0" dep="3" skp="1"/>
    <row op="6" dis="4" par="3" prt="0" dep="4" skp="0"/>
    <row op="7" dis="4" par="3" prt="4" dep="3" skp="1"/>
    <row op="8" dis="4" par="3" prt="4" dep="3" skp="1"/>
    <row op="9" dis="4" par="3" prt="3" dep="3" skp="1"/>
    <row op="10" dis="4" par="3" prt="3" dep="3" skp="1"/>
    <row op="11" dis="5" par="2" prt="5" dep="3" skp="0"/>
    <row op="12" dis="6" par="5" prt="6" dep="4" skp="0"/>
    <row op="13" dis="7" par="6" prt="6" dep="5" skp="0"/>
  </display_map>
</other_xml>

OP is the plan line ID. SKP=0 indicates an active line. This data can be extracted as a set of rows and columns with the XMLTABLE function.
My thanks to Tim Hall. I was only able to work this out with the aid of his excellent article XMLTABLE: Convert XML Data into Rows and Columns using SQL and associated video.
(Update 5.3.2018): Since publishing this I have also found Martin Bach's Blog Adaptive plans and v$sql_plan and related views.

column plan_hash_value format 9999999999 heading 'PHV'
column op          format 999
column dis         format 999
column par         format 999
column dep         format 999
column skp         format 999
column object_name format a20
column operation   format a20
column options     format a20
column partition_start format a10 heading 'PStart'

with x as (
select  sql_id, plan_hash_value
,  xmltype(other_xml) xml_data
from dba_hist_sql_plan p
where sql_id IN ('bk26thygs9c8n')
and id = 1
and other_xml IS NOT NULL
), y as (
select sql_id, plan_hash_value
, xt.*
from x
,    xmltable('/other_xml/display_map/row' PASSING x.xml_data
     COLUMNS 
       "OP" NUMBER path '@op',
       "DIS" NUMBER path '@dis',
       "PAR" NUMBER path '@par',
       "DEP" NUMBER path '@dep',
       "SKP" NUMBER path '@skp'
     ) xt
where xt.skp = 0
)
select y.* 
, p.object_name, p.operation, p.options, p.partition_start
from y
, dba_hist_sql_plan p
where p.sql_id = y.sql_id
and p.plan_hash_value = y.plan_hash_value
and y.op = p.id
/

• OTHER_XML is a CLOB, so it needs to be converted to an XMLTYPE before being passed to XML table.
• We effectively make a Cartesian product between the row source of SQL plans in sub-query x, and XMLTABLE().
• The XML structure is attributed rather than tagged, so you need the '@' in the path in the columns clause.
• Inactive plan lines are filtered out with a criterion on SKP.

Now I have a SQL query that returns only the active rows in the execution plan. I can use this technique in other places to profile statements by aspects of the execution plan.

SQL_ID                PHV   OP  DIS  PAR  DEP  SKP OBJECT_NAME          OPERATION            OPTIONS              PStart
------------- ----------- ---- ---- ---- ---- ---- -------------------- -------------------- -------------------- ----------
bk26thygs9c8n    38031479    1    1    0    1    0                      SORT                 GROUP BY
bk26thygs9c8n    38031479    2    2    1    2    0                      HASH JOIN
bk26thygs9c8n    38031479    3    3    2    3    0 :BF0000              PART JOIN FILTER     CREATE
bk26thygs9c8n    38031479    6    4    3    4    0 PS_ELIM_CF_SEL2002   TABLE ACCESS         STORAGE FULL
bk26thygs9c8n    38031479   11    5    2    3    0                      PARTITION RANGE      OR                   KEY(OR)
bk26thygs9c8n    38031479   12    6    5    4    0                      PARTITION LIST       JOIN-FILTER          :BF0000
bk26thygs9c8n    38031479   13    7    6    5    0 PS_LEDGER            TABLE ACCESS         STORAGE FULL         KEY

I demonstrate a practical use of this technique in my next blog.