Nov 27, 2018
Oracle Adaptive Plan info in OTHER_XML
DBMS_XPLAN displays the operation ID with no gap, even for Adaptive Plans where the inactive operations are skipped. Did you ever wonder where the information of skipped rows is stored?
Here is a simple query (but please, remember that natural join is bad ;)
SQL> set feedback on sql_id
SQL> select * from dept natural join emp natural join bonus;no rows selectedSQL_ID: 3q7fbwk91v4ra
The execution plan shows that the plan is adaptive:
SQL> select * from dbms_xplan.display_cursor(format=>'BASIC +note');PLAN_TABLE_OUTPUT
------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from dept natural join emp natural join bonusPlan hash value: 1315453310------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | HASH JOIN | |
| 4 | TABLE ACCESS FULL | BONUS |
| 5 | TABLE ACCESS FULL | EMP |
| 6 | INDEX UNIQUE SCAN | PK_DEPT |
| 7 | TABLE ACCESS BY INDEX ROWID| DEPT |
------------------------------------------------Note
-----
- this is an adaptive plan
With the ‘+adaptive format’ we see the active and inactive branches:
SQL> select * from dbms_xplan.display_cursor( sql_id=>'3q7fbwk91v4ra', format=>'BASIC +adaptive +note');PLAN_TABLE_OUTPUT
------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from dept natural join emp natural join bonusPlan hash value: 1315453310-------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------
| 0 | SELECT STATEMENT | |
|- 1 | HASH JOIN | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
|- 4 | STATISTICS COLLECTOR | |
| 5 | HASH JOIN | |
| 6 | TABLE ACCESS FULL | BONUS |
| 7 | TABLE ACCESS FULL | EMP |
| 8 | INDEX UNIQUE SCAN | PK_DEPT |
| 9 | TABLE ACCESS BY INDEX ROWID| DEPT |
|- 10 | TABLE ACCESS FULL | DEPT |
-------------------------------------------------Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
You can see that the numbering has changed: now 1–10 and this is what we actually have in PLAN_TABLE:
SQL> column "LPAD('',DEPTH,'')||OPERATION" format a30
SQL> select id,depth,parent_id,lpad(' ',depth,' ')||operation from v$sql_plan where sql_id like '3q7fbwk91v4ra';ID DEPTH PARENT_ID LPAD('',DEPTH,'')||OPERATION
---------- ---------- ---------- ------------------------------
0 0 SELECT STATEMENT
1 1 0 HASH JOIN
2 2 1 NESTED LOOPS
3 3 2 NESTED LOOPS
4 4 3 STATISTICS COLLECTOR
5 5 4 HASH JOIN
6 6 5 TABLE ACCESS
7 6 5 TABLE ACCESS
8 4 3 INDEX
9 3 2 TABLE ACCESS
10 2 1 TABLE ACCESS
The ID here is unique. Adaptive Plan does not break this. So this means that it was mapped to different numbers when the inactive lines were skipped. DBMS_XPLAN does this for us but if we have our own tool we need to know where this mapping comes from.
/other_xml/display_map
As most of the additional information that we can see in the notes, they come from OTHER_XML
SQL> set long 100000 linesize 68
SQL> select other_xml from v$sql_plan where sql_id like '3q7fbwk91v4ra' and id=1;OTHER_XML
--------------------------------------------------------------------
<other_xml><info type="db_version">18.0.0.0</info><info type="parse_
schema"><![CDATA["SCOTT"]]></info><info type="plan_hash_full">176307
9200</info><info type="plan_hash">1315453310</info><info type="plan_
hash_2">4080194477</info><info type="adaptive_plan" note="y">yes</in
fo><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint
><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('18.1.0')]]></hint><hint><
![CDATA[DB_VERSION('18.1.0')]]></hint><hint><![CDATA[ALL_ROWS]]></hi
nt><hint><![CDATA[OUTLINE_LEAF(@"SEL$9E43CB6E")]]></hint><hint><![CD
ATA[MERGE(@"SEL$58A6D7F6" >"SEL$3")]]></hint><hint><![CDATA[OUTLINE(
@"SEL$3")]]></hint><hint><![CDATA[OUTLINE(@"SEL$58A6D7F6")]]></hint>
<hint><![CDATA[MERGE(@"SEL$1" >"SEL$2")]]></hint><hint><![CDATA[OUTL
INE(@"SEL$2")]]></hint><hint><![CDATA[OUTLINE(@"SEL$1")]]></hint><hi
nt><![CDATA[FULL(@"SEL$9E43CB6E" "BONUS"@"SEL$2")]]></hint><hint><![
CDATA[FULL(@"SEL$9E43CB6E" "EMP"@"SEL$1")]]></hint><hint><![CDATA[IN
DEX(@"SEL$9E43CB6E" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))]]></hint><hint
><![CDATA[LEADING(@"SEL$9E43CB6E" "BONUS"@"SEL$2" "EMP"@"SEL$1" "DEP
T"@"SEL$1")]]></hint><hint><![CDATA[USE_HASH(@"SEL$9E43CB6E" "EMP"@"
SEL$1")]]></hint><hint><![CDATA[USE_NL(@"SEL$9E43CB6E" "DEPT"@"SEL$1
")]]></hint><hint><![CDATA[NLJ_BATCHING(@"SEL$9E43CB6E" "DEPT"@"SEL$
1")]]></hint></outline_data><display_map><row op="1" dis="0" par="0"
prt="0" dep="0" skp="1"/><row op="2" dis="1" par="0" prt="0" dep="1
" skp="0"/><row op="3" dis="2" par="1" prt="0" dep="2" skp="0"/><row
op="4" dis="2" par="2" prt="0" dep="2" skp="1"/><row op="5" dis="3"
par="2" prt="0" dep="3" skp="0"/><row op="6" dis="4" par="3" prt="0
" dep="4" skp="0"/><row op="7" dis="5" par="3" prt="0" dep="4" skp="
0"/><row op="8" dis="6" par="2" prt="0" dep="3" skp="0"/><row op="9"
dis="7" par="1" prt="0" dep="2" skp="0"/><row op="10" dis="7" par="
0" prt="0" dep="0" skp="1"/></display_map></other_xml>
Here is what we have in /other_xml/display_map:
SQL> select column_value from v$sql_plan , table(xmlsequence(extract(xmltype(other_xml),'/*/display_map/row'))) where sql_id like '3q7fbwk91v4ra' and id=1;COLUMN_VALUE
--------------------------------------------------------------------
<row op="1" dis="0" par="0" prt="0" dep="0" skp="1"/>
<row op="2" dis="1" par="0" prt="0" dep="1" skp="0"/>
<row op="3" dis="2" par="1" prt="0" dep="2" skp="0"/>
<row op="4" dis="2" par="2" prt="0" dep="2" skp="1"/>
<row op="5" dis="3" par="2" prt="0" dep="3" skp="0"/>
<row op="6" dis="4" par="3" prt="0" dep="4" skp="0"/>
<row op="7" dis="5" par="3" prt="0" dep="4" skp="0"/>
<row op="8" dis="6" par="2" prt="0" dep="3" skp="0"/>
<row op="9" dis="7" par="1" prt="0" dep="2" skp="0"/>
<row op="10" dis="7" par="0" prt="0" dep="0" skp="1"/>
I can see one row per execution plan operation, referenced by ‘op’, the operation ID, the ‘par’, the PARENT_ID, and ‘dep’ the DEPTH. The ‘skp’ flags with 1 the inactive branches that must be skipped in the default format, and ‘dis’ is the display ID to map to in order to have no gap. I don’t think that the ‘par’ — PART_ID — is currently used by dbms_xplan.
When is this information translated from internal representation to XML? We can see some internal structure copy during Execution such as qesdpCopySharedToExeDisplayMap() and qesdpCopyExeToSharedDisplayMap() but the conversion to XML is done when querying V$SQL_PLAN.OTHER_XML:
#0 0x000000000916a390 in qesdpWriteDisplayMap ()
#1 0x00000000098c447d in qksxaCompactToCustomXml ()
#2 0x00000000098c489f in qksxaCompactToXml ()
#3 0x000000000befe794 in xplCompactToOtherXml ()
#4 0x000000000beff2ee in xplNodeToRow ()
#5 0x000000000bf0045d in xplMakeRow ()
#6 0x00000000121606aa in xplFetchRow ()
#7 0x000000000b0143e8 in kqlfgx ()
#8 0x0000000010d87767 in kglic_cbk ()
#9 0x0000000010d87035 in kglic0 ()
#10 0x0000000010d86d1c in kglic ()
#11 0x000000000b01095b in kqlfxp ()
#12 0x00000000040b1cf7 in qerfxFetch ()
#13 0x00000000120661af in opifch2 ()It is interesting to see that even when this information could have been displayed as columns in V$SQL_PLAN, the CBO developers chose the ‘NoSQL’ way of putting everything new in XML. We see the limit when we try to build our own tools. I must admit that I have some awk scripts which parse the dbms_xplan.display_cursor() text output rather than trying to get information from V$SQL_PLAN…
About tools other than DBMS_XPLAN, SQL Monitor has the information in the XML. The Flash version differentiate active and inactive operations, but it looks like the SQLDev HTML5 does not (yet)grey the inactive one.
