V$SQL_PLAN は Oracle Database の実行計画に関する情報が格納されていますが、該当SQLに関連する情報が格納されています。

SET LONG 1000000;
SET LONGC 1000000;
SET LINESIZE 100;
COLUMN OTHER_XML_VALUE FORMAT A100;
SELECT OTHER_XML FROM V$SQL_PLAN S
 WHERE S.SQL_ID       = 'f9r2y6xdz6t84'
   AND S.CHILD_NUMBER = 4;

OTHER_XML
----------------------------------------------------------------------------------------------------
<other_xml><info type="cardinality_feedback" note="y">yes</info><info type="db_version">12.2.0.1</in
fo><info type="parse_schema"><![CDATA["AYSHIBAT"]]></info><info type="dynamic_sampling" note="y">11<
/info><info type="plan_hash_full">247910413</info><info type="plan_hash">5458159</info><info type="p
lan_hash_2">247910413</info><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint>
<![CDATA[OPTIMIZER_FEATURES_ENABLE('12.2.0.1')]]></hint><hint><![CDATA[DB_VERSION('12.2.0.1')]]></hi
nt><hint><![CDATA[OPT_PARAM('optimizer_dynamic_sampling' 11)]]></hint><hint><![CDATA[ALL_ROWS]]></hi
nt><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[FULL(@"SEL$1" "B"@"SEL$1")]]></hint
><hint><![CDATA[FULL(@"SEL$1" "A"@"SEL$1")]]></hint><hint><![CDATA[LEADING(@"SEL$1" "B"@"SEL$1" "A"@
"SEL$1")]]></hint><hint><![CDATA[USE_HASH(@"SEL$1" "A"@"SEL$1")]]></hint></outline_data></other_xml>

このXMLデータを整形して出力してみますやで彡(ﾟ)(ﾟ)

1. テストデータ

DBMS_XPLAN の以下の実行計画(SQL_ID f9r2y6xdz6t84, child number 4)の OTHER_XML列を使用します。

SQL> SET AUTOTRACE OFF;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('f9r2y6xdz6t84', NULL, 'ADVANCED ALLSTATS LAST ADAPTIVE'));
：
：
SQL_ID  f9r2y6xdz6t84, child number 4
-------------------------------------
SELECT /*+ MONITOR */        B.ITEM_NAME      , TRUNC(A.ORDER_DATE,
'DD') AS ORDER_DAILY      , COUNT(*)   FROM ORDER_TBL A      , ITEM_TBL
B  WHERE A.ITEM_NO  = B.ITEM_NO --   AND B.ITEM_NO BETWEEN 1 AND 100
AND TO_CHAR(B.REGIST_DATE, 'YYYYMMDD') = '20120801'  GROUP BY
B.ITEM_NAME, TRUNC(A.ORDER_DATE, 'DD')  ORDER BY 1

Plan hash value: 5458159

------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |       |  2435 (100)|          |    261 |00:00:00.51 |    8922 |       |       |          |
|   1 |  SORT GROUP BY      |           |      1 |    261 |  6786 |  2435   (1)| 00:00:01 |    261 |00:00:00.51 |    8922 | 18432 | 18432 |16384  (0)|
|*  2 |   HASH JOIN         |           |      1 |  33601 |   853K|  2433   (1)| 00:00:01 |  26000 |00:00:00.50 |    8922 |  2171K|  2171K| 1135K (0)|
|*  3 |    TABLE ACCESS FULL| ITEM_TBL  |      1 |     10 |   150 |    26   (4)| 00:00:01 |     10 |00:00:00.01 |      91 |       |       |          |
|   4 |    TABLE ACCESS FULL| ORDER_TBL |      1 |   2600K|    27M|  2399   (1)| 00:00:01 |   2600K|00:00:00.14 |    8831 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / B@SEL$1
   4 - SEL$1 / A@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('optimizer_dynamic_sampling' 11)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "B"@"SEL$1")
      FULL(@"SEL$1" "A"@"SEL$1")
      LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
      USE_HASH(@"SEL$1" "A"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."ITEM_NO"="B"."ITEM_NO")
   3 - filter(TO_CHAR(INTERNAL_FUNCTION("B"."REGIST_DATE"),'YYYYMMDD')='20120801')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=2) "B"."ITEM_NAME"[VARCHAR2,100], TRUNC(INTERNAL_FUNCTION("A"."ORDER_DATE"),'fmdd')[8], COUNT(*)[22]
   2 - (#keys=1) "B"."ITEM_NAME"[VARCHAR2,100], "A"."ORDER_DATE"[DATE,7]
   3 - (rowset=256) "B"."ITEM_NO"[NUMBER,22], "B"."ITEM_NAME"[VARCHAR2,100]
   4 - "A"."ITEM_NO"[NUMBER,22], "A"."ORDER_DATE"[DATE,7]

Note
-----
   - dynamic statistics used: dynamic sampling (level=AUTO)
   - statistics feedback used for this statement

2. 一階層目と二階層目のXMLデータを出力してみる。

まず一階層目のXMLデータを出力してみます。

SET LONG 1000000;
SET LONGC 1000000;
SET LINESIZE 100;
COLUMN XML_VALUE FORMAT A100;
SELECT INFO.XML_VALUE
  FROM (
    SELECT EXTRACT(VALUE(p), '/*') AS XML_VALUE
      FROM V$SQL_PLAN S, 
           TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE.CREATEXML(OTHER_XML), '/other_xml/*'))) p
     WHERE S.SQL_ID       = 'f9r2y6xdz6t84'
       AND S.CHILD_NUMBER = 4
) INFO;

XML_VALUE
----------------------------------------------------------------------------------------------------
<info type="cardinality_feedback" note="y">yes</info>
<info type="db_version">12.2.0.1</info>
<info type="parse_schema"><![CDATA["AYSHIBAT"]]></info>
<info type="dynamic_sampling" note="y">11</info>
<info type="plan_hash_full">247910413</info>
<info type="plan_hash">5458159</info>
<info type="plan_hash_2">247910413</info>
<outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_
ENABLE('12.2.0.1')]]></hint><hint><![CDATA[DB_VERSION('12.2.0.1')]]></hint><hint><![CDATA[OPT_PARAM(
'optimizer_dynamic_sampling' 11)]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LE
AF(@"SEL$1")]]></hint><hint><![CDATA[FULL(@"SEL$1" "B"@"SEL$1")]]></hint><hint><![CDATA[FULL(@"SEL$1
" "A"@"SEL$1")]]></hint><hint><![CDATA[LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")]]></hint><hint><![C
DATA[USE_HASH(@"SEL$1" "A"@"SEL$1")]]></hint></outline_data>

SQL を少し編集して二階層目の情報を出力してみます。

SET LONG 1000000;
SET LONGC 1000000;
SET LINESIZE 100;
COLUMN INFO.XML_VALUE FORMAT A100;
SELECT INFO.XML_VALUE
  FROM (
    SELECT EXTRACT(VALUE(p), '/*') AS XML_VALUE
      FROM V$SQL_PLAN S, 
           TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE.CREATEXML(OTHER_XML), '/other_xml/*/*'))) p
     WHERE S.SQL_ID       = 'f9r2y6xdz6t84'
       AND S.CHILD_NUMBER = 4
) INFO;

XML_VALUE
----------------------------------------------------------------------------------------------------
<hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint>
<hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('12.2.0.1')]]></hint>
<hint><![CDATA[DB_VERSION('12.2.0.1')]]></hint>
<hint><![CDATA[OPT_PARAM('optimizer_dynamic_sampling' 11)]]></hint>
<hint><![CDATA[ALL_ROWS]]></hint>
<hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint>
<hint><![CDATA[FULL(@"SEL$1" "B"@"SEL$1")]]></hint>
<hint><![CDATA[FULL(@"SEL$1" "A"@"SEL$1")]]></hint>
<hint><![CDATA[LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")]]></hint>
<hint><![CDATA[USE_HASH(@"SEL$1" "A"@"SEL$1")]]></hint>

3. OUTLINEヒントを抽出してみる。

下記のような SQL にすると DBMS_XPLAN の Outline Dataセクションに相当する情報(OUTLINEヒント)を出力できます。

SELECT HINT.HINT_VALUE
  FROM (
    SELECT EXTRACTVALUE(VALUE(p), '/hint') AS HINT_VALUE
      FROM V$SQL_PLAN S, 
           TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE.CREATEXML(OTHER_XML), '/other_xml/outline_data/hint'))) p
     WHERE S.SQL_ID       = 'f9r2y6xdz6t84'
       AND S.CHILD_NUMBER = 4
) HINT;

HINT_VALUE
----------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
OPT_PARAM('optimizer_dynamic_sampling' 11)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "B"@"SEL$1")
FULL(@"SEL$1" "A"@"SEL$1")
LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
USE_HASH(@"SEL$1" "A"@"SEL$1")

4. まとめ

XML DB は難解……しかも今回使用したファンクションは非推奨となっています。 OTHER_XML列に格納されているデータも含めて自己責任で使用して下さいませ。

EXTRACT (XML) https://docs.oracle.com/cd/F19136_01/sqlrf/EXTRACT-XML.html#GUID-593295AA-4F46-4D75-B8DC-E7BCEDB1D4D7

EXTRACTVALUE https://docs.oracle.com/cd/F19136_01/sqlrf/EXTRACTVALUE.html#GUID-20AB974B-7544-4F44-B539-787FB6145680

まあ今時は JSON だとは思いますが。彡(ﾟ)(ﾟ)

ねら～ITエンジニア雑記

やきうのお兄ちゃんが綴るOracle Databaseメインのブログ

V$SQL_PLAN の OTHER_XML列に格納された XMLデータを整形して出力してみる(Oracle Database)

1. テストデータ

2. 一階層目と二階層目のXMLデータを出力してみる。

3. OUTLINEヒントを抽出してみる。

4. まとめ