Oracle Real Application Clusterの検証 その１２
＜Oracle Real Application Clusterの検証 ～その１２～＞
ペンネーム ダーリン
— 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”の影響もそれほど多くは無いのではないでしょうか。
これはかなり有効な手段に思えてきました。是非皆さんも実際の環境で試し
ていただきたいとおもいます。もしすでに実装している環境がありましたら、
その効果の程を教えていただきたいとおもいます。
インデックス、ああインデックス、インデックス（字余り） 茅ヶ崎にて