LoginSignup
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@ora_gonsuke777in日本オラクル株式会社

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

Posted at

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 だとは思いますが。
彡(ﾟ)(ﾟ)

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?