Oracle Real Application Clusterの検証 その12

<Oracle Real Application Clusterの検証 ~その12~>
ペンネーム ダーリン

— RAC環境でのインデックスチューニングの検証 —

今回の環境では、インデックスに関する処理がボトルネックになり、スケーラ
ビリティを”1″未満にまで下げると言う事態を、引き起こしていました。

そこで、このインデックスをどうにかしてチューニングしてやろうと言うこ
とで、皆様からのアイデアをお持ちしていました。
と言うわけで、今回はいただいたアイデアを検証していきます。
ターゲットはめるまがでご紹介しなかった方法で最も多かった「インデック
スのパーティショニング」にしたいとおもいます。

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Indexキー列の先頭にNODE_NO列を加えるのではなく、キー列はそのままで
IndexをNODE_NOでパーティション化してしまってもよいのではないかと思います。

そうすればINSERT時のアクセスはインスタンス間で競合しませんし、SELECTの
Range Scanも働くはずです。
(パーティションの数と同じだけB*Treeがあり、そのそれぞれに対してRange
Scanが動くイメージ)

お時間ありましたらぜひとも検証していただきたいと思います。

今後も各種検証を楽しみにしています。
それでは。
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

select * from index_stats where name = 'IDX_LOGTBL1_1_1';

  HEIGHT BLOCKS NAME            LF_ROWS LF_BLKS(1) ・・
-------- ------ --------------- ------- ------- 
       3    512 IDX_LOGTBL1_1_1  200000     478 ・・

【TEST2.】

SQL> select * from index_stats where name = 'IDX_LOGTBL1_2_1';

  HEIGHT BLOCKS NAME            LF_ROWS LF_BLKS(2) ・・
-------- ------ --------------- ------- ------- 
       3    896 IDX_LOGTBL1_2_1  200000     814 ・・

【TEST3.】

SQL> select * from index_stats where name = 'IDX_LOGTBL1_3_1';

  HEIGHT BLOCKS NAME            PARTITION_NAME       LF_ROWS LF_BLKS(3-1) ・・
-------- ------ --------------- -------------------- ------- ------- 
       2    256 IDX_LOGTBL1_3_1 IDX_LOGTBL1_3_1_PT00  100000     239 ・・

SQL> select * from index_stats where name = 'IDX_LOGTBL1_3_1';

  HEIGHT BLOCKS NAME            PARTITION_NAME       LF_ROWS LF_BLKS(3-2) ・・
-------- ------ --------------- -------------------- ------- ------- 
       2    256 IDX_LOGTBL1_3_1 IDX_LOGTBL1_3_1_PT01  100000     239 ・・

んー、ブロック数そのものの数が違いますね。
それはそうですね、【TEST2.】ではインデックスにNODE_NOのカラムを追加し
たので、その分インデックスを格納するブロック数が増えるのも止むをえな
いでしょう。(2)
今回、その影響で”RANGE SCAN”の対象ブロック数が増えてしまうことも考え
られます。しかしそれにしても、まだ、多いような気がします。これについ
ては、別の機会にもう少し調査してみます。

一方【TEST3.】では、各パーティションのリーフブロックの合計数(3-1)(3-2)
は、【TEST1.】と同じです(1)。”RANGE SCAN”により読み込みブロック数が増
えることもないはずです。

では、実際にそれぞれの環境で検索してみましょう。
実は今検証用に使用しているLinuxのRAC環境では、実はEPS評価値としては、
あまり差が見えませんでした。
そこで、”SQL TRACE”で、詳細な違いがどのくらいあるかを見てみることにし
ます。

では、”SQL TRACE”の結果を見ていきましょう。

【TEST1.】

select action_cd from logtbl1_1 where ins_date > sysdate -1 and ins_date  sysdate -1 and ins_date  sysdate -1 and ins_date <= sysdate

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      448      0.05       0.04          0       1026          0        6666

Rows     Row Source Operation
-------  ---------------------------------------------------
   6666  FILTER  (cr=1026 r=0 w=0 time=34667 us)
   6666   PARTITION RANGE ALL PARTITION: 1 3 (cr=1026 r=0 w=0 time=28877 us)
   6666    TABLE ACCESS BY LOCAL INDEX ROWID LOGTBL1_3 PARTITION: 1 3
                                                  (cr=1026 r=0 w=0 time=23322 us)
   6666     INDEX RANGE SCAN IDX_LOGTBL1_3 PARTITION: 1 3
                                   (cr=450 r=0 w=0 time=10312 us)(object id 9762)

いかがでしょうか。"total"の行にある、"query"の列の値を見ると一目瞭然
ですね。「TEST3.」でもっともブロック読み込みが少ないことがわかります。
また、"cpu"や、"elaps"も少なくなっています。

さてこの違いはどこにあるのでしょうか?
アクセスパスの"cr"の値をみると、各ステップで取得しているブロック数が
わかります。これを並べてみると、、

***************************************************
          TEST1.    TEST2.     TEST3.
 --------------------------------------------
INDEX      464(4)    1377(5)     450(6)
TABLE      578        577        576
TOTAL     1042       1954       1026

CPU       0.04       0.11       0.05
ELAPS     0.03       0.07       0.04
***************************************************

今回は、「集計をするためにログテーブルを検索する。」と言う目的がある
ので、"RANGE SCAN"を前提としています。そのため、今回の検証ではいずれ
の環境においても"INDEX RANGE SCAN"が行われています。
そして、それぞれの環境でもっとも違いが現れたのは、インデックスの取得
ブロック数でした(4)(5)(6)。これがUNIQUE検索であれば話は違ってくるの
ですが。

先にも述べましたが、【TEST2.】でインデックスの取得ブロックが多くなっ
ている原因の一つは、インデックスのブロック数自体が多いこと以外にも要
因はあるようにおもわれます。ひょっとすると"インデックス・スキップ・
スキャン"の動作の影響によるものでしょうか。このあたりは更なる調査が
必要です。

さて、いずれにしても、どうやらインデックスパーティショニングを実施す
ることで、シングルインスタンス環境と同等の検索性能が実現できそうです。
当日のログでなければ、更新もかかっていないはずなので、RACの"GCS"や
"GES"の影響もそれほど多くは無いのではないでしょうか。
これはかなり有効な手段に思えてきました。是非皆さんも実際の環境で試し
ていただきたいとおもいます。もしすでに実装している環境がありましたら、
その効果の程を教えていただきたいとおもいます。

インデックス、ああインデックス、インデックス(字余り) 茅ヶ崎にて