Search

Top 60 Oracle Blogs

Recent comments

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.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">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.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">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.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">
000000000001

yes








skp="0"/>
skp="0"/>
skp="0"/>


skp="0"/>




skp="0"/>
skp="0"/>
skp="0"/>


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.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">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.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">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.