2017年12月02日

[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さんです。
posted by charade at 00:00| Comment(0) | 日記 | このブログの読者になる | 更新情報をチェックする
この記事へのコメント
コメントを書く
お名前:

メールアドレス:

ホームページアドレス:

コメント: [必須入力]

認証コード: [必須入力]


※画像の中の文字を半角で入力してください。
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。