oracle：インデックスの断片化確認の罠

oracleにおけるインデックス（索引）の断片化確認方法について

インデックスもテーブル同様に断片化していく。

テーブルの場合は行連鎖・行移行で１レコードのブロックが複数跨ることで断片化していくが、インデックスの場合は削除などでBツリーのリーフブロックが歯抜けになっていくイメージだろうか。

下記のコマンドでインデックスの分析が可能、INDEX_STATS表に情報が出力されて、アクセス効率が悪いか確認できる。

analyze index indexname validate structure ;

一般的には

HEIGHT（Bツリーの高さ）が4以上で、DEL_LF_ROW/LF_ROWS（削除されたエントリーの割合）が0.2を超える場合

は効率が悪いとされる。

指標が「Bツリーの高さ」と「削除されたエントリーの割合」の2つがあるが、基本的には2つの指標がどちらも超えた時に効率が悪いと判断だろうか。

「Bツリーの高さ」はレコード数（索引のエントリー数）が多ければ必然的に高くなっていくので、高い＝効率悪い、とは一概には言えないだろうし、「削除されたエントリーの割合」も考慮しながら判断していくのが良いと思う。

下記にoracleのドキュメントがあるため、詳しくはこっちを参照。

津島博士のパフォーマンス講座　第6回　パフォーマンスの基礎である索引について 2011.04.20公開

さて、本題の「インデックスの断片化確認の罠」だが、

上述の断片化確認のコマンド（ANALYZE）は

表ロックを取得するため、他処理（DML）と並走できない。

稼働中・業務中のシステムで性能調査などは良くあることだと思うが、その調査が他処理と並走できないというのは致命的。

なんとかならんもんかと思い、ANALYZEコマンドのマニュアルを見ると、ONLINE句を付けられることを発見。

検証環境で実行してみたところ、コマンド自体は正常終了するがINDEX_STATS表に何もレコードが入らない。。。

INDEX_STAT表のリファレンスを確認してみると、OFFLINEで実行する必要があるとのこと。

（だったら最初からエラーにしといてくれよと思うが…）

注意:

統計情報を収集するには、ANALYZE INDEX ... VALIDATE STRUCTURE OFFLINE文を使用する必要があります。

結論、

ANALYZEコマンドを用いたインデックス断片化の確認はオンライン業務中には不可能

実施したい場合はメンテナンスの時間を設けて、他処理が入らない状況で実施する必要がある。

ちなみにこのINDEX_STATS表、揮発するらしく、情報を確認するためにはANALYZEコマンドを打ったセッションでSELECTする必要がある。

したがって複数のインデックスに対してANALYZEコマンドを打ち、あとでまとめて確認～みたいなことが出来ない。インデックス数が少なければ良いが、多ければ多いほどオーバヘッドが多くなる。

システムによるだろうが、メンテナンス時間も無限に用意できないし、その中で断片化確認だけに何時間も使えないことを考えると、商用システムではインデックス断片化確認するのは現実的ではない気がする。

そもそもハードの性能が上がっていることを考えると、従来に比べて断片化確認の必要性は薄くなっていると思うが、じゃあやらなくても良いのか？っていうとoracleのアーキテクチャ上では断片化は発生しうるし、放っておくわけにもいかない。

特に稼働が長いシステムでは「昔からやっていたから、リプレースによってハード性能が向上した後も継続」みたいなことが多いのではなかろうか。

ハードの性能が上がっても、放っておいても大丈夫とは誰も、oracle社さえも言い切れないだろうから、oralceに断片化を自動で解消するような革新的な機能が導入されるまでは何とな～く残っていく作業になっていくのではないか。

性能劣化をoracleが自動的に解消する機能が、世間一般で使われるようになった時にはＤＢＡという職業が無くなっている気もするが…

※ちなみにテーブルの行連鎖を確認するコマンドは表ロックを取らないらしい、他処理と並走可能。

ANALYZE TABLE tablename LIST CHAINED ROWS ;

データベース備忘録