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/
追記:
extractValueはDeprecatedです :)
Oracle XML DBの新機能 https://t.co/UoCcGh3Vdc
— Kazuhiro YOSHIKAWA (@yoshikaw) 2015, 12月 3
@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 さんです。どんな変態ネタが飛び出すのか、乞うご期待!!
| 固定リンク | 0 | コメント (0) | トラックバック (0)
最近のコメント