[JPOUG Advent Calendar] Oracle on Hyper-V 2017

このエントリは「JPOUG Advent Calendar 2017」の2日目です。
昨日は吉川 和宏さんの「 Oracle DatabaseのテーブルをCSV出力する6つの方法」でした。
charade_oo4oと申します。昨年に引き続き、今年も参加致します。

　＜2017年＞

今年のOracleは、オンプレミス向け12cR2リリース、18c発表、大きな動きがありました。
今は期待と不安が入り混じった感じです。

Microsoftについては期待が空回りした印象でした。
Windows ServerのInsider Programには喜び勇んで参加しましたが、出てきたものは半期チャネルのServer Core。
当面はLTSCのまま使う事になりそうです。

個人のPC環境では、Windows 10のアップデートの遅さに耐えかねて、遂にSSDを導入しました。
DISKはそこそこ快適になりましたが、Hyper-Vでの12.2.0.1RAC検証ではメモリ不足が露呈しました。
1ノード分の最低メモリが8GBなので、物理メモリ16GBでは2ノード納まり切れません。
ゲストOSの動的メモリを上限6GBで検証した所、スワップが多発して不安定でした。
PC自体の見直しもそろそろ必要かも知れませんが、最新CPUではWindows 10より前のOSでのVHDブートが出来なくなってしまうので悩ましいです。

　＜32bitOS再検証＞

本ブログの2012年のネタでは、2008R2より2012の方がゲストOSのFree System Page Table Entries(FSPTE)が不足するという検証結果が出ました。
今年は2008R2、2012、2012R2、2016のHyper-Vで、ゲストOSのFSPTEを再検証しました。
ゲストOSは2003と2008の32bitOSを使用し、2003では/3GBと/USERVAスイッチ、2008では bcdedit /set IncreaseUserVa でユーザーモード領域を増やしました。

https://support.microsoft.com/ja-jp/help/316739/how-to-use-the-userva-switch-with-the-3gb-switch-to-tune-the-user-mode
/userva スイッチと /3GB スイッチを使用してユーザー モード領域を 2 〜 3 GB の間でチューニングする方法

各ホストOSをVHDブートで切り替え、同一VHDのゲストOSをインポートしました。
各OSでメニューの出し方が異なるので、マウスカーソルで右か左かと悩む事も多かったです。
右ハンドルの欧州車で、ウィンカーとワイパーを間違えるのと似た様な感じです。

ゲストOS:2003

ホストOS	ゲストOSスイッチ	FSPTE	DB_CACHE_SIZE	備考
2008R2	/3GB	34412	2048M
2012	/3GB	33468	2048M
2012R2	/3GB	33314	2048M
2016 (サポート対象外)	/3GB	11852	2048M
	/3GB /USERVA=3008	28316	2048M	接続時にエラー発生「ORA-12500: TNS: リスナーが専用サーバー・プロセスの起動に失敗しました。」
		28232	1984M

ゲストOS:2008

ホストOS	ゲストOSスイッチ	FSPTE	SGA_TARGET	備考
2008R2	IncreaseUserVa 3072	68763	2048M
2012	IncreaseUserVa 3072	68199	2048M
2012R2	IncreaseUserVa 3072	64165	2048M
2016	IncreaseUserVa 3072	72369	2048M

ゲストOS=2008はホストOS=2008,2008R2,2012,2012R2,2016ではFSPTEはほぼ同一でした。
ゲストOS=2003はホストOS=2008,2008R2,2012,2012R2ではFSPTEはほぼ同一、サポート対象外のホストOS=2016ではFSPTEが不足して/USERVA=3008へ減らす必要がありました。
/USERVAを減らした場合、OracleのSGA割当も見直す必要がありました。

2012年の頃の検証結果(2012は大飯喰らい)とは異なりますが、原因は不明です。
その頃から変えた部品は前述したSSD化位なので、後はWindows Updateでのパッチの差異が原因かも知れません。
リソース使用量が増えるパッチが落ちて来る事もあるかも知れません。

　＜遅い12c＞

度々、旧バージョンでは速かったSQLが、新バージョンでは遅くなるという性能問題が起こります。
特に12cでは、サブクエリにGROUP BYを使用したSQLが遅くなる事が多い様です。
12.2.0.1でも同様の傾向があります。
店舗・得意先・商品のマスタをベースに、1日分の売上金額をサマリするSELECT文で検証してみました。

2017/12/02分の店舗・得意先・商品別売上金額一覧

SELECT C.DT ,N.TENCD ,N.TENNM ,T.TOKCD ,T.TOKNM ,I.HINCD ,I.HINNM ,G.GKING_SUM ,G.BKING_SUM FROM ( SELECT '20171202' DT FROM DUAL ) C ,MTKH K ,MTOK T ,MTEN N ,MHIN I ,( SELECT H.TENCD ,H.DENDT ,H.TOKCD ,D.HINCD ,SUM(D.GKING) GKING_SUM ,SUM(D.BKING) BKING_SUM FROM DENH H ,DEND D WHERE H.TENCD = D.TENCD AND H.DENDT = D.DENDT AND H.DENNO = D.DENNO GROUP BY H.TENCD,H.DENDT,H.TOKCD,D.HINCD ) G WHERE C.DT >= K.STADT AND C.DT <= K.ENDDT AND K.TOKCD = T.TOKCD AND C.DT >= T.STADT AND C.DT <= T.ENDDT AND T.TENCD = N.TENCD AND C.DT >= N.STADT AND C.DT <= N.ENDDT AND K.HINCD = I.HINCD AND C.DT >= I.STADT AND C.DT <= I.ENDDT AND N.TENCD = G.TENCD (+) AND C.DT = G.DENDT (+) AND T.TOKCD = G.TOKCD (+) AND I.HINCD = G.HINCD (+) ORDER BY C.DT,N.TENCD,T.TOKCD,I.HINCD ;

12.2.0.1実行計画(ヒント無)

Plan hash value: 3836866724 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 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 | | | 79 (100)| | 10000 |00:00:05.42 | 678K| | | | | 1 | SORT ORDER BY | | 1 | 1 | 283 | 79 (3)| 00:00:01 | 10000 |00:00:05.42 | 678K| 1966K| 666K| 1747K (0)| | 2 | NESTED LOOPS OUTER | | 1 | 1 | 283 | 78 (2)| 00:00:01 | 10000 |00:00:06.08 | 678K| | | | | 3 | NESTED LOOPS | | 1 | 1 | 240 | 16 (0)| 00:00:01 | 10000 |00:00:00.24 | 20184 | | | | |* 4 | HASH JOIN | | 1 | 1 | 162 | 14 (0)| 00:00:01 | 10000 |00:00:00.06 | 144 | 913K| 913K| 1353K (0)| |* 5 | HASH JOIN | | 1 | 1 | 123 | 12 (0)| 00:00:01 | 1000 |00:00:00.01 | 30 | 1106K| 1106K| 1364K (0)| | 6 | NESTED LOOPS | | 1 | 1 | 60 | 5 (0)| 00:00:01 | 100 |00:00:00.01 | 7 | | | | | 7 | VIEW | | 1 | 1 | 10 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 0 | | | | | 8 | FAST DUAL | | 1 | 1 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 0 | | | | |* 9 | TABLE ACCESS FULL | MTEN | 1 | 1 | 50 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 7 | | | | | 10 | TABLE ACCESS FULL | MTOK | 1 | 1000 | 63000 | 7 (0)| 00:00:01 | 1000 |00:00:00.01 | 23 | | | | | 11 | TABLE ACCESS FULL | MTKH | 1 | 1 | 39 | 2 (0)| 00:00:01 | 10000 |00:00:00.02 | 114 | | | | |* 12 | TABLE ACCESS BY INDEX ROWID BATCHED | MHIN | 10000 | 1 | 78 | 2 (0)| 00:00:01 | 10000 |00:00:00.21 | 20040 | | | | |* 13 | INDEX RANGE SCAN | PK_MHIN | 10000 | 1 | | 1 (0)| 00:00:01 | 10000 |00:00:00.14 | 10043 | | | | |* 14 | VIEW PUSHED PREDICATE | | 10000 | 1 | 43 | 62 (2)| 00:00:01 | 7362 |00:00:05.07 | 657K| | | | | 15 | SORT GROUP BY | | 10000 | 73 | 5621 | 62 (2)| 00:00:01 | 736K|00:00:05.32 | 657K| 15360 | 15360 |14336 (0)| |* 16 | HASH JOIN | | 10000 | 73 | 5621 | 61 (0)| 00:00:01 | 736K|00:00:04.21 | 657K| 1209K| 1209K| 1687K (0)| | 17 | TABLE ACCESS BY INDEX ROWID BATCHED| DENH | 10000 | 73 | 2190 | 6 (0)| 00:00:01 | 736K|00:00:01.95 | 58648 | | | | |* 18 | INDEX RANGE SCAN | PK_DENH | 10000 | 73 | | 3 (0)| 00:00:01 | 736K|00:00:00.64 | 24548 | | | | | 19 | TABLE ACCESS BY INDEX ROWID BATCHED| DEND | 10000 | 73 | 3431 | 55 (0)| 00:00:01 | 736K|00:00:02.04 | 599K| | | | |* 20 | INDEX RANGE SCAN | PK_DEND | 10000 | 73 | | 3 (0)| 00:00:01 | 736K|00:00:00.67 | 25268 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------

12.2.0.1でヒント無のSELECT文を実行すると、実行計画では「VIEW PUSHED PREDICATE」が行われました。
マスタ側の予測件数がE-Rows=1なので、売上明細のサブクエリへ「VIEW PUSHED PREDICATE」した方が効率が良いとCBOは考えた様です。
サブクエリのGROUP BYの先頭2項目はP-KEYのINDEXと一致していたので、売上伝票側では「INDEX RANGE SCAN」が行われました。
しかし実際の行数はA-Rows=10000なので、A-Time=5.42秒かかりました。

対策としてヒント/*+ NO_PUSH_PRED(G) */を追加しました。

12.2.0.1実行計画(/*+ NO_PUSH_PRED(G) */)

Plan hash value: 433594900 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | | 5701 (100)| | 10000 |00:00:00.39 | 29301 | | | | | 1 | SORT ORDER BY | | 1 | 1 | 303 | | 5701 (1)| 00:00:01 | 10000 |00:00:00.39 | 29301 | 1966K| 666K| 1747K (0)| |* 2 | HASH JOIN OUTER | | 1 | 1 | 303 | | 5700 (1)| 00:00:01 | 10000 |00:00:00.37 | 29301 | 2526K| 1093K| 2708K (0)| | 3 | JOIN FILTER CREATE | :BF0000 | 1 | 1 | 240 | | 16 (0)| 00:00:01 | 10000 |00:00:00.12 | 20184 | | | | | 4 | NESTED LOOPS | | 1 | 1 | 240 | | 16 (0)| 00:00:01 | 10000 |00:00:00.11 | 20184 | | | | | 5 | NESTED LOOPS | | 1 | 1 | 240 | | 16 (0)| 00:00:01 | 10000 |00:00:00.08 | 10187 | | | | |* 6 | HASH JOIN | | 1 | 1 | 162 | | 14 (0)| 00:00:01 | 10000 |00:00:00.02 | 144 | 913K| 913K| 1380K (0)| |* 7 | HASH JOIN | | 1 | 1 | 123 | | 12 (0)| 00:00:01 | 1000 |00:00:00.01 | 30 | 1106K| 1106K| 1360K (0)| | 8 | NESTED LOOPS | | 1 | 1 | 60 | | 5 (0)| 00:00:01 | 100 |00:00:00.01 | 7 | | | | | 9 | VIEW | | 1 | 1 | 10 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 0 | | | | | 10 | FAST DUAL | | 1 | 1 | | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 0 | | | | |* 11 | TABLE ACCESS FULL | MTEN | 1 | 1 | 50 | | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 7 | | | | | 12 | TABLE ACCESS FULL | MTOK | 1 | 1000 | 63000 | | 7 (0)| 00:00:01 | 1000 |00:00:00.01 | 23 | | | | | 13 | TABLE ACCESS FULL | MTKH | 1 | 1 | 39 | | 2 (0)| 00:00:01 | 10000 |00:00:00.01 | 114 | | | | |* 14 | INDEX RANGE SCAN | PK_MHIN | 10000 | 1 | | | 1 (0)| 00:00:01 | 10000 |00:00:00.04 | 10043 | | | | |* 15 | TABLE ACCESS BY INDEX ROWID| MHIN | 10000 | 1 | 78 | | 2 (0)| 00:00:01 | 10000 |00:00:00.03 | 9997 | | | | | 16 | VIEW | | 1 | 41135 | 2530K| | 5683 (1)| 00:00:01 | 41135 |00:00:00.25 | 9117 | | | | | 17 | HASH GROUP BY | | 1 | 41135 | 3093K| 22M| 5683 (1)| 00:00:01 | 41135 |00:00:00.25 | 9117 | 6351K| 2338K| 4987K (0)| | 18 | JOIN FILTER USE | :BF0000 | 1 | 242K| 17M| | 3667 (1)| 00:00:01 | 41135 |00:00:00.23 | 9117 | | | | |* 19 | HASH JOIN | | 1 | 242K| 17M| 9952K| 3667 (1)| 00:00:01 | 242K|00:00:00.22 | 9117 | 19M| 3257K| 20M (0)| | 20 | TABLE ACCESS FULL | DENH | 1 | 242K| 7104K| | 1241 (1)| 00:00:01 | 242K|00:00:00.04 | 4515 | | | | | 21 | TABLE ACCESS FULL | DEND | 1 | 242K| 10M| | 1265 (1)| 00:00:01 | 242K|00:00:00.05 | 4602 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ヒント/*+ NO_PUSH_PRED(G) */を追加すると、「VIEW PUSHED PREDICATE」は無くなり、A-Time=0.39秒まで改善しました。
今回のデータ量では、売上伝票側を1回「TABLE ACCESS FULL」した方が、10000回「INDEX RANGE SCAN」するより速かった様です。

「VIEW PUSHED PREDICATE」が悪い訳ではなく、いくつか試したSQLではCBOが選択した「VIEW PUSHED PREDICATE」の方が速い事が多かったです。
出来れば旧バージョンでは速く新バージョンでは遅いSQLを作りたかったのですが、このSQLは12.2.0.1でしか「VIEW PUSHED PREDICATE」となりませんでした。
11.2.0.4では「ORA-01417:表が少なくとも1つの他の表に外部結合されている可能性があります。」のエラーが発生して実行不可でした。
12.1.0.2ではSql Plan Directiveが働いて、何度か実行すると速い実行計画に収束しました。
12.2.0.1でもoptimizer_adaptive_statistics=TRUEを設定すると、12.1.0.2と同様に何度か実行すると速い実行計画に収束しました。

ヒントで実行計画を制御する対策も有効ですが、Oracleの機能を活かして対策する方法もあります。
18c Autonomous Databaseでは更に改善されるのかも知れません。

　＜まとめ＞

・パッチ注意。
・新バージョンでの性能対策はまだ必要。

明日はdenzowさんです。