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

2015年12月 4日 (金)

OTHER_XMLの中身

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

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

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


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

例えば、V$SQL_PLANDBA_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 さんです。どんな変態ネタが飛び出すのか、乞うご期待!!

|

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/106341/62795944

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

コメント

コメントを書く