Oracle Real Application Clusterの検証　その７

＜Oracle Real Application Clusterの検証　～その７～＞
ペンネーム　ダーリン

— RAC環境でのチューニング —

さてさて、先週はスケラービリティ向上を目指すためのチューニングを行う
ために、インデックスにターゲットを絞りました。

で、具体的にどのような方法を考えたかと言うと、
・各インスタンス間での競合を防ぐために、インスタンス毎でインデックス
のブロックが使用されるようにする。
・大量のトランザクションにおける、インデックスリーフブロックの競合自
体を防ぐ。
という感じです。

<>
ログ系のインデックスのカラムには、先週お話したとおりログの登録日時が
使用されていました。これは、トランザクションの状況を確認する検索系の
システムの要求仕様を満たすために必要なものでした。いつ、どれくらいの
新規登録や、更新などが行われたか、どの時間帯に誰が処理しているかと言
った情報を集計するためです。
このため、同じようなことをしたがる人はどこにでも居るので、どうしても
ログレコードをインサートすると、インデックスの最新ブロックが競合を起
こしてしまいます。

まずは簡単なところに目をつけました。
日時カラムの前に、各インスタンスのインスタンス番号を追加してあげるこ
とで、これを解消することが出来ると考えられます。
なぜか？B-TREEインデックスは、キー項目を範囲によってブランチ、リーフ
と言ったブロックに分割して管理します。よって、インデックスを構成する
カラムの先頭にインスタンスを識別するカラムがあると、それらのインデッ
クスブロック（ブランチ、リーフ）は、それぞれのインスタンスにのみ存在
する可能性が高くなっています。
もちろん、各インスタンスで検索した場合は、インスタンス間を飛び交うこ
とになりますが、インサート時の(例えば”ITL waits”のような)待ちは解消
できると考えられます。

カラムの構成はこんな感じです。

SQL> desc logtbl1
名前                    NULL?    型
----------------------- -------- -------------------
NODE_NO                          NUMBER(2)
INS_DATE                         TIMESTAMP(6)
CONNECT_ID                       CHAR(64)
ACTION_CD                        NUMBER(1)
USER_ID                          CHAR(50)
NOTE                             VARCHAR2(200)

SQL> select INDEX_NAME,TABLE_NAME,COLUMN_NAME,COLUMN_POSITION
2  from user_ind_columns where table_name = 'LOGTBL1';

INDEX_NAME   TABLE_NAME   COLUMN_NAME  COLUMN_POSITION
------------ ------------ ------------ ---------------
IDX_LOGTBL1  LOGTBL1      NODE_NO      1
IDX_LOGTBL1  LOGTBL1      INS_DATE     2

<>
もうひとつの案は、「インスタンス内での競合を抑えよう。」です。
これは、RACに限らず使える方法です。ずばり、リバースインデックスです。
昇順にインサートされるデータも、末尾の値は必ずしも昇順ではありません。
これを利用することで、インサート時の同一ブロックの競合を避けることが
出来ます。

さて、今回は、どちらを採用することにしましょうか。どちらを採用するに
しても、それほどアプリケーション側の変更には、影響がない様におもわれ
ます。皆さんはどちらを選ぶでしょうか。

大きなポイントは、”インデックスの目的”です。

今、データのインサート時におけるブロックの競合に着目していますが、も
ともとインデックスは検索時の効率化を計るための機能なので、これを最低
限満たしていないと、そもそも負荷をかけてインデックスを作る必要がなく
なります。

そこで、今回の対象となっている、ログ系のインデックスの目的を確認して
みます。
先にもあげましたが、
・いつ、どれくらいの新規登録が行われたか
・いつ、どれくらいの更新が行われたか
・どの時間帯に誰が処理しているか
などのように、時間帯での分析・集計が用途となります。
つまり、”レンジスキャン”です。

もうわかりますね。リバースインデックスは使えません。

どう転んでも、時刻データがばらばらにインデックスリーフブロックに収ま
るために、範囲検索が出来なくなるのです。
正確には、アプリケーションとして動くけれども「その負荷は察して恐るべ
し。」
というわけで、今回は、node_noというインスタンスを特定するカラムを追加
することで対応することにしました。これで、インスタンス間をまたがった
インサート時の同一ブロック競合は防げるはずです。

では早速レスポンスをみてみましょう。、、、、んん？
おっと、まったまった。うっかりして大事なことを忘れていました。

インデックスのカラムを変更したので、あわせて検索するアプリケーション
を変更する必要があるはずです。さて、どうしましょうか。まずはこのまま
検索して現状の実行計画を見てみましょう。

（node_noを追加したテーブル・インデックスで検索）

SQL> select * from logtbl1
2  where ins_date > sysdate -1 and ins_date <= sysdate;

6666行が選択されました。

実行計画
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE
1    0   TABLE ACCESS (FULL) OF 'LOGTBL1'

統計
----------------------------------------------------------
0  recursive calls
0  db block gets
4524  consistent gets
0  physical reads
0  redo size
926564  bytes sent via SQL*Net to client
5387  bytes received via SQL*Net from client
446  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
6666  rows processed

インデックスが使われていませんね。ちなみに、以下がこれまでのテーブル
・インデックスでの実行計画です。インデックスが使用されており、
consistent getsを比べると、その差は4倍以上です。

（従来のテーブル・インデックスで検索）

実行計画
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'LOGTBL1'
2    1     INDEX (RANGE SCAN) OF 'IDX_LOGTBL1' (NON-UNIQUE)

統計
----------------------------------------------------------
0  recursive calls
0  db block gets
1007  consistent gets
0  physical reads

で、どうするかと言うと、やはりインデックスを使ってもらうようにしない
といけません。アプリケーションとしてはヒント句を追加する方法が手っ取
り早いでしょう。
ためしに以下のようにしてみました。(検証環境の結果です。)

SQL> select /*+ index(logtbl1 idx_logtbl1) */ *
2  from logtbl1
3  where ins_date > sysdate -1 and ins_date <= sysdate;

6666行が選択されました。

実行計画
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=832 Bytes=215488)
1    0   FILTER
2    1     TABLE ACCESS (BY INDEX ROWID) OF 'LOGTBL1' (Cost=29 Card=832 Bytes=215488)
3    2       INDEX (FULL SCAN) OF 'IDX_LOGTBL1' (NON-UNIQUE) (Cost=26 Card=832)

統計
----------------------------------------------------------
0  recursive calls
0  db block gets
1624  consistent gets
0  physical reads
0  redo size
926564  bytes sent via SQL*Net to client
5387  bytes received via SQL*Net from client
446  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
6666  rows processed

consistent getsがかなり減りました。効果絶大ですね。

でも、皆さん何か気になりませんか。よーくみてください。
実行計画のところで、インデックスのFULL SCANが実行されています。つまり、
「複合インデックスで先頭カラムに条件以外のカラムがある場合、インデッ
クスの中をすべて見ないとデータの存在がわからない。」と言うことになって
いるんですね。
ただし、結果として、FULL SCANするよりも負荷は下がっているようなので、
この検証環境においては効果はあったようです。ただし、いつでも有効かど
うかは疑問ですね。

続きはまた来週。

梅雨の晴れ間に布団干し茅ヶ崎にて

Oracle Real Application Clusterの検証 その７

Oracle Real Application Clusterの検証　その７