GaragebandとiMovieで



検索

  • Google
フォト

Twitter

オンラインショッピング


2023年2月
日 月 火 水 木 金 土
      1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28        

気まぐれ写真館

  • spider lily - 白と赤の彼岸花（曼珠沙華）

カテゴリー

Oracle

Apple

その他

  • ブログ de ノベルティ
    ノベルティグッズをただ、ただ、記録していくブログ。Mac De Oracleでは出来ないネタなので、別ブログを立てた次第。

Googleの広告



RSS

ブログ:ココログ

当サイトについて

プライバシーポリシー
プライバシーポリシー

「個人情報の保護に関する法律」に基づき、「個人情報保護の基本方針（プライバシーポリシー）」を
定め個人情報の適切な管理・保護に努めることを表明します。

個人情報保護の基本方針（プライバシーポリシー）

1.　個人情報の利用目的をできるだけ特定し、特定された利用目的の達成に必要な範囲を超えて利用
　　することはありません。
2.　個人情報を偽りその他不正な手段による取得しません。取得したときは、本人に速やかに利用目的
　　を通知又は公表します。
3.　個人情報を利用目的の範囲内で正確かつ最新の内容に保つように努めます。
4.　個人情報の漏洩や滅失を防ぐために、必要かつ適切な安全管理措置を講じます。
5.　あらかじめ本人の同意を得ないで第三者に個人情報を提供することはありません。
　　ただし、法令に基づく場合、人の生命、身体又は財産の保護に必要な場合、公衆衛生・児童の健全育成に
　　特に必要な場合、国等に協力する場合は除きます。

« WITH clause in-line PL/SQL functionって.... | トップページ | Edgeも出たし、JetStreamも出たので久々にブラウザのベンチマーク »

2015年12月 4日 (金) / Author : Hiroshi Sekiguchi.

OTHER_XMLの中身

JPOUG Advent Calendar 2015 - 4日目のエントリーです。

役に立たないから、絶対最後まで読まないでね！！！　(^^)

さて、
みなさん、マニュアル読んでますか？　
最近は量が多いので、必要になってから、なりそうだから読むことが多いです。
Oracle® Databaseリファレンス 12cリリース1 (12.1) なんて特にそうです。
ただ、一度読み始めると、いろいろと気づくところもあるわけです...こんな情報が取れるのか！　とか。


あ〜、これは、禁断のw　マニュアルに記載されてないパラメータを指定すると見れるやつだ！！！　とか気づくこともあったり。

例えば、V$SQL_PLANやDBA_HIST_SQL_PLANのOTHER_XML列。

この列の説明には実に興味深いことが書かれています。以下の説明、読んでてワクワクしますw　
何が格納されているのでしょう？？？（もう、みんな知ってるくせに〜）

”アウトライン・データ(同じ計画の再作成に使用できる一連のオプティマイザ・ヒント)”

見たくないですか？　中身。

見たいですよね。私もそうです！


ただ、OTHER_XML列、列名の通り、CLOB型で中身はXMLです！！！
SQL*Plusで普通に表示しようとすると長すぎて読みにくいってのが難点w


ならばXMLにも対応しているSQL文で何とかしてみたいと思います。

ゴニョゴニョ、パタパタ。

できました。

dba_hist_sql_plan向けですが。(v$sql_planビューでも同様のことができます！）
（注：dba_hist_*ビューを参照するにはOracle Diagnostics Packが必要でっす。ですが、v$sql_planを参照するのならオプションはいらないですよね.）

こんな感じです

SYSTEM> !cat report_outline_hints.sql
col outline for a200
set linesize 300
set pagesize 10000
set veri off

SELECT '/*+' AS outline FROM DUAL
UNION ALL
SELECT '  '||'BEGIN_OUTLINE_DATA' FROM DUAL
UNION ALL
SELECT
    '  '||o.outlinehint
FROM
(
    SELECT
        EXTRACTVALUE(
            VALUE(x)
            , '/hint/text()'
        ) AS outlinehint
    FROM
        XMLTABLE(
            '/*/outline_data/hint'
            PASSING(
                SELECT
                    XMLTYPE(other_xml)
                FROM
                    dba_hist_sql_plan
                WHERE
                    other_xml IS NOT NULL
                    AND sql_id = '&1' 
                    AND plan_hash_value = &2
            )
        ) x
) o
UNION ALL
SELECT '  '||'END_OUTLINE_DATA' FROM DUAL
UNION ALL
SELECT '*/' FROM DUAL
/
set veri on

実行すると以下のような、:)　情報を取り出すことができます！！

SYSTEM> @report_outline_hints gdtyuqcyk8x1c 2236229349

OUTLINE
------------------------------------------------------------------------------------------
/*+
  BEGIN_OUTLINE_DATA
  IGNORE_OPTIM_EMBEDDED_HINTS
  OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
  DB_VERSION('12.1.0.2')
  ALL_ROWS
  OUTLINE_LEAF(@"SEL$2")
  OUTLINE_LEAF(@"SEL$1")
  INDEX(@"SEL$1" "B"@"SEL$1" ("ILM_EXECUTION$"."EXECUTION_ID"))
  NO_ACCESS(@"SEL$1" "A"@"SEL$1")
  LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
  USE_MERGE(@"SEL$1" "A"@"SEL$1")
  PQ_FILTER(@"SEL$1" SERIAL)
  INDEX(@"SEL$2" "A"@"SEL$2" ("ILM_EXECUTION$"."EXECUTION_ID"))
  FULL(@"SEL$2" "B"@"SEL$2")
  LEADING(@"SEL$2" "A"@"SEL$2" "B"@"SEL$2")
  USE_MERGE(@"SEL$2" "B"@"SEL$2")
  USE_HASH_AGGREGATION(@"SEL$2")
  END_OUTLINE_DATA
*/

表示された内容に、身に覚え、
いや、見覚えのある方も多いことと思います :) あれでね。そう、あれです。
マニュアルに記載されいないパラメータを利用しなくても取り出せるんです。

ついでなので、マニュアルに記載のないパラメータを使ったDBMS_XPLAN.DISPLAY_AWRで同じ情報をリストしてみました。DBMS_XPLAN.DISPLAY_AWR以外のDISPLAY*関数のformatパラメータでもできます:)
（注：　DISPLAY_AWRでAWRを参照するのでOracle Diagnostics Packが必要でっす。 ですが、DISPLAY_CURSOR(),DISPLAY()とDISPLAY_PLAN()ならAWRは参照しないのでオプションはいらないですよね。）

PROMPT
PROMPT ****** display_awr with outline option *********
SELECT 
	plan_table_output as outline 
FROM
	TABLE(DBMS_XPLAN.DISPLAY_AWR(sql_id=>'&1',plan_hash_value=>&2,format=>'OUTLINE'))
/


****** DBMS_XPLAN.DISPLAY_AWR with OUTLINE option *******

OUTLINE
------------------------------------------------------------------------------------------
SQL_ID gdtyuqcyk8x1c
--------------------
SELECT B.EXECUTION_ID, NVL(A.N_COUNT,0), A.COMP_TIME FROM ( SELECT
A.EXECUTION_ID, COUNT(*) N_COUNT, NVL(MAX(B.COMPLETION_TIME), SYSDATE)
COMP_TIME FROM SYS.ILM_EXECUTION$ A, SYS.ILM_RESULTS$ B WHERE
EXECUTION_STATE = :B7 AND A.EXECUTION_ID = B.EXECUTION_ID AND
B.JOB_STATUS NOT IN (:B6 , :B5 , :B4 , :B3 , :B2 , :B1 ) GROUP BY
A.EXECUTION_ID )A, ILM_EXECUTION$ B WHERE B.EXECUTION_ID =
A.EXECUTION_ID (+) AND EXECUTION_STATE = :B7 AND (ROWNUM <= :B9 OR :B9
= :B8 )

Plan hash value: 2236229349

-----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                |       |       |     4 (100)|          |
|   1 |  COUNT                             |                |       |       |            |          |
|   2 |   FILTER                           |                |       |       |            |          |
|   3 |    MERGE JOIN OUTER                |                |     1 |    65 |     4  (50)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID    | ILM_EXECUTION$ |     1 |    26 |     0   (0)|          |
|   5 |      INDEX FULL SCAN               | PK_TASKID      |     1 |       |     0   (0)|          |
|   6 |     SORT JOIN                      |                |     1 |    39 |     4  (50)| 00:00:01 |
|   7 |      VIEW                          |                |     1 |    39 |     3  (34)| 00:00:01 |
|   8 |       HASH GROUP BY                |                |     1 |    65 |     3  (34)| 00:00:01 |
|   9 |        MERGE JOIN                  |                |     1 |    65 |     3  (34)| 00:00:01 |
|  10 |         TABLE ACCESS BY INDEX ROWID| ILM_EXECUTION$ |     1 |    26 |     0   (0)|          |
|  11 |          INDEX FULL SCAN           | PK_TASKID      |     1 |       |     0   (0)|          |
|  12 |         SORT JOIN                  |                |     1 |    39 |     3  (34)| 00:00:01 |
|  13 |          TABLE ACCESS FULL         | ILM_RESULTS$   |     1 |    39 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "B"@"SEL$1" ("ILM_EXECUTION$"."EXECUTION_ID"))
      NO_ACCESS(@"SEL$1" "A"@"SEL$1")
      LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
      USE_MERGE(@"SEL$1" "A"@"SEL$1")
      PQ_FILTER(@"SEL$1" SERIAL)
      INDEX(@"SEL$2" "A"@"SEL$2" ("ILM_EXECUTION$"."EXECUTION_ID"))
      FULL(@"SEL$2" "B"@"SEL$2")
      LEADING(@"SEL$2" "A"@"SEL$2" "B"@"SEL$2")
      USE_MERGE(@"SEL$2" "B"@"SEL$2")
      USE_HASH_AGGREGATION(@"SEL$2")
      END_OUTLINE_DATA
  */

ちなみに、11g R2でも動作します！


どうでしたか？役に立たなかったですよね？
以上、OUTLINE HINTSより愛を込めてお送りしました!


参考：　(日本語で書かれたエントリーは見当たらない。初か、もしかして！w）

Oracle SQL Plan Stability
http://blog.tanelpoder.com/oracle/performance/sql/oracle-sql-plan-stability/


Plan stability in 10g - using existing cursors to create Stored Outlines and SQL profiles
http://oracle-randolf.blogspot.jp/2009/03/plan-stability-in-10g-using-existing.html


FORCE_MATCH for Stored Outlines and/or SQL Baselines????? – follow up
https://tonyhasler.wordpress.com/2011/12/

How to hint – 1
https://jonathanlewis.wordpress.com/2011/06/08/how-to-hint-1/

dbms_xplan(3)
https://jonathanlewis.wordpress.com/2008/03/06/dbms_xplan3/


追記：

@yoshikawさんに指摘され、EXTRACTVALUE()が11.2で非推奨になっていたとことに気づく orz.

@yoshikawさん、指摘ありがとうございます。　

XMLTABLE().... COLUMNSを使えばEXTRACTVALUE()はいらなかった！！！！　ということでEXTRACTVALUEなし版も作りました！！

ただ、CON_IDも見ないとマルチテナントに対応できず、エラーになると気づいたが、
11gにも対応しようとすると、もう一工夫必要なことに気づき、再び、orz.
つづきは、...いずれ...

変更したSQL文は以下のとおり。

SYSTEM> !cat report_outline_hints.sql
col outline for a200
set linesize 300
set pagesize 10000
set veri off

SELECT '/*+' AS outline FROM dual
UNION ALL
SELECT '  '||'BEGIN_OUTLINE_DATA' FROM DUAL
UNION ALL
SELECT
    '  '||x.outline_hint
FROM
    XMLTABLE(
        '/*/outline_data/hint/text()'
        PASSING(
            SELECT
                XMLTYPE(other_xml)
            FROM
                dba_hist_sql_plan
            WHERE
                other_xml IS NOT NULL
                AND sql_id = '&1' 
                AND plan_hash_value = &2
        )
        COLUMNS outline_hint PATH '/text()' 
    ) x
UNION ALL
SELECT '  '||'END_OUTLINE_DATA' FROM DUAL
UNION ALL
SELECT '*/' FROM DUAL
/

SYSTEM> @report_outline_hints gdtyuqcyk8x1c 2236229349

OUTLINE
--------------------------------------------------------------------------------
/*+
  BEGIN_OUTLINE_DATA
  IGNORE_OPTIM_EMBEDDED_HINTS
  OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
  DB_VERSION('12.1.0.2')
  ALL_ROWS
  OUTLINE_LEAF(@"SEL$2")
  OUTLINE_LEAF(@"SEL$1")
  INDEX(@"SEL$1" "B"@"SEL$1" ("ILM_EXECUTION$"."EXECUTION_ID"))
  NO_ACCESS(@"SEL$1" "A"@"SEL$1")
  LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
  USE_MERGE(@"SEL$1" "A"@"SEL$1")
  PQ_FILTER(@"SEL$1" SERIAL)
  INDEX(@"SEL$2" "A"@"SEL$2" ("ILM_EXECUTION$"."EXECUTION_ID"))
  FULL(@"SEL$2" "B"@"SEL$2")
  LEADING(@"SEL$2" "A"@"SEL$2" "B"@"SEL$2")
  USE_MERGE(@"SEL$2" "B"@"SEL$2")
  USE_HASH_AGGREGATION(@"SEL$2")
  END_OUTLINE_DATA
*/

JPOUG Advent Calendar 2015、12/5は、Yohei Azekatsu さんです。どんな変態ネタが飛び出すのか、乞うご期待！！

投稿日時 2015年12月 4日 (金) 00時30分 XML, チューニング, FAQ, SQL, イベント, JPOUG, Advent Calendar | |

トラックバック


この記事へのトラックバック一覧です： OTHER_XMLの中身:

コメント

コメントを書く