連載
» 2004年12月22日 00時00分 公開

Oracle SQLチューニング講座(7):索引を作成したのにパフォーマンスが悪いケース (3/3)

[亀田明裕,株式会社アゲハ]
前のページへ 1|2|3       

索引の格納効率の悪化

 索引の格納効率は作成直後が最も良い状態であり、表データの更新/挿入/削除により、徐々に格納効率が悪化していきます。「初めはパフォーマンスに問題はなかったのに、しばらくしてから徐々にパフォーマンスが劣化してきた」という問題の最も多い原因の1つといえます。格納効率が悪化した索引では、索引スキャンによるアクセスブロック数が増加するため、索引作成直後に比べてパフォーマンスが劣化してしまいます。

 B*Tree索引が作成されている列値を更新/削除すると、既存の索引エントリは削除されますが、削除により空いた領域は、該当ブロックが完全に空になるか、削除された値と同じ値、もしくは前後のエントリの中間となる値を挿入した場合にのみ再利用されます。このため、索引列値の更新、削除などが頻発する索引の格納効率は、次第に悪化していくことになります。

図9 索引ブロックの分割 図9 索引ブロックの分割

 図9は、UPDATE文を実行した際に、索引ブロックが分割される様子を示しています。(2)のUPDATE文の実行によって、更新対象となった「100」のレコードが削除され、ブロック2に「550」のレコードが挿入されます。しかし、このブロックに(PCTFREE注3 の領域も含め)空き領域がない場合には新規ブロック(ブロック3)が作成され、ブロック2内の「550」以降のレコード(ここでは「600」)をブロック3に挿入します。

注3索引のPCTFREEパラメータについて

索引作成時に指定するPCTFREEパラメータは、表のPCTFREEパラメータとは意味が異なります。表のPCTFREEパラメータは、更新処理によって列長が長くなった場合に備えて各ブロックに空けておく領域の割合を指定しますが、索引のPCTFREEパラメータは、既存の索引エントリ間に新たな索引エントリが挿入されたときに備えて各ブロックに空けておく領域を指定するものです。索引ブロックに空き領域を残しておくことで、索引に対するINSERT/DELETE処理により、索引ブロックが分割(SPLIT)されにくくなります。ブロックの分割を抑止することで、索引列に対するINSERT/UPDATE処理速度の向上、および索引エントリの格納効率低下(索引走査のパフォーマンス劣化)を防ぐことができます。


 索引の格納効率が若干悪化した場合でも、プライマリ・キーを条件に指定した一意検索などにはほとんど影響はありませんが、範囲検索などで多くの索引ブロックを読み込む必要がある処理のパフォーマンスに影響します。図10、図11は、作成直後の状態と格納効率が悪化した状態で、索引構造とパフォーマンスを調べた結果になります。

図10 索引作成直後の結果 図10 索引作成直後の結果
図11 索引の格納効率が悪化した場合の結果 図11 索引の格納効率が悪化した場合の結果

 索引の構造を比較すると、図11では索引が使用しているブロック数(BLOCKS)、および索引の階層の高さ(HEIGHT)が増えており、索引の格納効率が悪化していることが分かります。また、索引の範囲スキャンを実施した場合の実行結果からは、「disk」「query」の値に大きな違いが発生していることが確認できます。

 このように、更新頻度の激しい表の索引は、定期的な再作成(Rebuild)を検討する必要があります。索引を再作成することでブロックの格納効率を最適化し、パフォーマンスを回復させることができます。

索引の構造を確認する手順

 以下に索引の構造を確認するための手順例を示します。

1.ANALYZEコマンドで索引の構造を分析する

SQL >ANALYZE INDEX ix_info_txt VALIDATE STRUCTURE;
索引が分析されました。

2.INDEX_STATSビューから分析結果を取得

 INDEX_STATSビューからは直前に実行されたANALYZEコマンドの分析結果のみ取得できます。別のセッションからは参照できません(分析結果をデータベース内に保存しておく場合はテーブルを作成し、データを新規表にコピーします)。

SQL >SELECT name,blocks,height FROM index_stats;
NAME                               BLOCKS     HEIGHT
------------------------------ ---------- ----------
IX_INFO_TXT                           256          2

 今回は、索引を使用するためのSQLと、使用している索引に問題がないかの確認方法を説明しました。次回も引き続き索引に関するテクニックを説明していきます。


前のページへ 1|2|3       

Copyright © ITmedia, Inc. All Rights Reserved.

編集部からのお知らせ

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。