Oracle インデックスの確認・再構築：DBパフォーマンス最適化戦略

Oracleデータベースのパフォーマンス最適化において、インデックスは重要な役割を果たします。適切に管理されたインデックスは、SQLクエリの実行速度を大幅に向上させ、データベース全体の応答性を高めることができます。しかし、時間と共にインデックスは断片化したり、統計情報が古くなったり、不要なインデックスが残ったりすることで、パフォーマンスボトルネックとなる可能性があります。

本記事では、Oracleデータベースにおけるインデックスの確認、分析、再構築、および最適化に関する包括的なガイドを提供します。インデックスの状態を評価するための様々なテクニック、再構築の必要性を判断するための基準、最適な再構築方法の選択、そして、パフォーマンス維持のための継続的な監視について詳しく解説します。

1. インデックスの基礎：役割と種類

まず、インデックスの基本的な役割と種類について理解することは、パフォーマンス最適化の第一歩です。

1.1 インデックスの役割:

インデックスは、データベーステーブル内のデータへのアクセスを高速化するために使用される構造です。書籍の索引のように、インデックスは特定の列の値に基づいてデータを迅速に見つけることができます。インデックスを使用することで、データベースはテーブル全体をスキャンする必要がなくなり、クエリの応答時間を大幅に短縮できます。

主な利点:

高速なデータ検索: 特定の条件に一致するレコードを迅速に検索できます。
ソートされたデータへのアクセス: インデックスは、特定の列の値に基づいてデータをソートしてアクセスできます。
結合操作の高速化: 複数のテーブルを結合するクエリを高速化できます。
一意性の制約の強制: 一意のインデックスは、テーブル内の特定列に重複した値がないことを保証します。

1.2 インデックスの種類:

Oracleデータベースでは、様々な種類のインデックスが利用可能です。それぞれのインデックスは、異なる状況やデータ型に対して最適化されています。

B-Tree インデックス: 最も一般的なタイプのインデックスであり、数値、文字列、日付などの幅広いデータ型をサポートします。B-Treeインデックスは、バランスの取れたツリー構造を持ち、効率的な検索、挿入、削除を可能にします。
- 通常B-Treeインデックス: デフォルトで作成されるインデックスです。
- 関数ベースB-Treeインデックス: 列の値に関数を適用した結果に基づいて作成されるインデックスです。
- 降順B-Treeインデックス: 列の値を降順にソートして格納するインデックスです。
Bitmap インデックス: カーディナリティが低い（異なる値の数が少ない）列に適しています。例えば、性別、ステータス、都道府県などの列です。Bitmapインデックスは、複数の条件に基づく複雑なクエリを効率的に処理できます。
Hash インデックス: 等価条件による検索に非常に高速ですが、範囲検索には適していません。Oracle Database 12c Release 2 以降で利用可能です。
Index-Organized Table (IOT): テーブル自体がインデックス構造として格納される特殊なテーブルです。プライマリキーに基づいてデータを物理的にソートするため、プライマリキーによる検索に非常に適しています。
クラスタインデックス (Clustered Index): Oracleでは直接サポートされていませんが、IOTが同様の機能を提供します。

1.3 インデックスの選択:

適切なインデックスの種類を選択することは、パフォーマンスに大きな影響を与えます。以下の点を考慮してインデックスを選択する必要があります。

列のデータ型: B-Treeインデックスは幅広いデータ型をサポートしますが、Bitmapインデックスはカーディナリティの低い列に最適です。
クエリの種類: 等価条件による検索にはHashインデックスが適していますが、範囲検索にはB-Treeインデックスが適しています。
データの更新頻度: インデックスの追加や削除は、テーブルの更新パフォーマンスに影響を与える可能性があります。更新頻度の高いテーブルには、インデックスを慎重に選択する必要があります。
インデックスのサイズ: インデックスはディスクスペースを消費します。不要なインデックスは削除して、ディスクスペースを節約する必要があります。

2. インデックスの状態確認：診断と分析

インデックスの状態は時間とともに変化し、最適でない状態になることがあります。定期的にインデックスの状態を確認し、問題を特定することが重要です。

2.1 インデックスの断片化:

インデックスが断片化すると、論理的な順序と物理的な順序が一致しなくなり、データの検索効率が低下します。断片化は、データの挿入、削除、更新が頻繁に行われるテーブルで発生しやすくなります。

断片化の確認方法:

ANALYZE INDEX コマンド: このコマンドは、インデックスに関する統計情報を収集し、断片化の程度を推定します。
sql ANALYZE INDEX index_name VALIDATE STRUCTURE; SELECT name, del_lf_rows, lf_rows FROM index_stats WHERE name = 'index_name';
del_lf_rows は削除されたリーフノードの数、lf_rows はリーフノードの総数を示します。del_lf_rows が lf_rows に対して大きい場合、断片化が進んでいる可能性があります。
DBMS_STATS パッケージ: DBMS_STATS パッケージを使用して統計情報を収集することもできます。
sql EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname => 'schema_name', indname => 'index_name'); SELECT blevel, lf_rows, distinct_keys, clustering_factor FROM user_indexes WHERE index_name = 'index_name';
- blevel はインデックスの高さを示します。高いほど検索コストが高くなります。
- clustering_factor は、インデックスされた列の順序とテーブル内の物理的な順序との相関関係を示します。値が低いほど、データは物理的にソートされていることを意味します。値が高い場合は、断片化が進んでいる可能性があります。
DBA_INDEXES ビュー: DBA_INDEXES ビューから、インデックスに関する一般的な情報を確認できます。
sql SELECT index_name, table_name, uniqueness, status FROM DBA_INDEXES WHERE table_name = 'table_name';

2.2 インデックスの統計情報:

オプティマイザは、クエリの実行計画を決定するために、インデックスの統計情報を使用します。統計情報が古くなっていると、オプティマイザは最適な実行計画を選択できなくなり、パフォーマンスが低下する可能性があります。

統計情報の確認方法:

DBA_TAB_STATISTICS、DBA_IND_STATISTICS ビュー: これらのビューから、テーブルやインデックスの統計情報が最後に収集された日時を確認できます。
sql SELECT table_name, last_analyzed FROM DBA_TAB_STATISTICS WHERE table_name = 'table_name'; SELECT index_name, last_analyzed FROM DBA_IND_STATISTICS WHERE index_name = 'index_name';
DBMS_STATS パッケージ: DBMS_STATS パッケージを使用して、統計情報が古くなっているかどうかを判断できます。
sql EXEC DBMS_STATS.GET_STATS_HISTORY_RETENTION('schema_name', 'table_name', 'index_name', :retention);
retention 変数に、統計情報の保持期間が返されます。

2.3 不要なインデックス:

使用されていないインデックスは、ディスクスペースを無駄にし、データの更新パフォーマンスを低下させる可能性があります。不要なインデックスを特定し、削除することが重要です。

不要なインデックスの特定方法:

SQL*Plus の SET AUTOTRACE ON: クエリの実行計画を表示し、どのインデックスが使用されているかを確認できます。
V$SQL ビュー: このビューから、実際に使用されているSQL文の情報を取得し、どのインデックスが使用されているかを分析できます。
SQL Developer などのツール: これらのツールは、グラフィカルなインターフェースを提供し、インデックスの使用状況を分析するのに役立ちます。

2.4 無効なインデックス:

インデックスが無効になっている場合、クエリはインデックスを使用できなくなり、パフォーマンスが大幅に低下します。インデックスが無効になる原因としては、テーブルの構造変更、権限の問題、データベースの障害などが考えられます。

無効なインデックスの確認方法:

DBA_INDEXES ビュー: STATUS 列が INVALID であるインデックスを特定します。
sql SELECT index_name, table_name, status FROM DBA_INDEXES WHERE status = 'INVALID';

3. インデックスの再構築：戦略と手順

インデックスの状態を確認し、再構築が必要であると判断された場合、適切な再構築方法を選択し、実行する必要があります。

3.1 再構築の判断基準:

以下の基準に基づいて、インデックスの再構築が必要かどうかを判断します。

断片化の程度: ANALYZE INDEX コマンドまたは DBMS_STATS パッケージの結果に基づいて、断片化の程度を評価します。
統計情報の古さ: 統計情報が一定期間更新されていない場合、再構築を検討します。
パフォーマンスの問題: 特定のクエリの実行速度が遅い場合、関連するインデックスの再構築を検討します。
ディスクスペースの使用量: インデックスが過剰なディスクスペースを消費している場合、再構築を検討します。

一般的な閾値:

del_lf_rows / lf_rows > 0.2 (削除されたリーフノードの割合が20%を超える)
clustering_factor がテーブル内の行数に近い場合
統計情報が最後に収集されてから数週間または数ヶ月経過している場合

3.2 再構築の方法:

Oracleデータベースでは、いくつかの方法でインデックスを再構築できます。それぞれの方法には、メリットとデメリットがあります。

ALTER INDEX REBUILD: インデックスをオンラインまたはオフラインで再構築します。
- メリット: 簡単な構文で実行できます。
- デメリット: オフラインで実行すると、テーブルへのアクセスがロックされます。オンラインで実行すると、パフォーマンスに影響を与える可能性があります。
ALTER INDEX COALESCE: インデックスのリーフノードをマージし、断片化を解消します。
- メリット: 比較的短時間で実行できます。
- デメリット: 断片化を完全に解消することはできません。
DROP INDEX および CREATE INDEX: インデックスを削除してから再作成します。
- メリット: 最も効果的な再構築方法です。
- デメリット: テーブルへのアクセスがロックされます。

3.3 オンライン再構築とオフライン再構築:

オンライン再構築 (ALTER INDEX REBUILD ONLINE): テーブルへのアクセスをブロックせずに、インデックスを再構築します。
- メリット: ダウンタイムを最小限に抑えることができます。
- デメリット: オフライン再構築よりも時間がかかり、パフォーマンスに影響を与える可能性があります。
オフライン再構築 (ALTER INDEX REBUILD): テーブルへのアクセスをブロックしながら、インデックスを再構築します。
- メリット: オンライン再構築よりも高速に実行できます。
- デメリット: ダウンタイムが発生します。

3.4 インデックス再構築の手順:

再構築の対象となるインデックスを特定します。
再構築方法を決定します（オンラインまたはオフライン）。
再構築を実行する前に、テーブルとインデックスのバックアップを作成します（必要に応じて）。
ALTER INDEX REBUILD コマンドを実行します。
sql ALTER INDEX index_name REBUILD [ONLINE];
再構築が完了したら、インデックスの統計情報を更新します。
sql EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname => 'schema_name', indname => 'index_name');
再構築後のインデックスの状態を確認します。
必要に応じて、他のインデックスについても同様の手順を繰り返します。

3.5 インデックス再構築のベストプラクティス:

ピーク時以外の時間帯に再構築を実行します。
オンライン再構築を使用する場合は、リソースの使用状況を監視し、パフォーマンスへの影響を最小限に抑えるように調整します。
再構築前に、テーブルとインデックスのバックアップを作成します。
再構築後には、必ずインデックスの統計情報を更新します。
再構築後、関連するクエリのパフォーマンスをテストします。
大規模なテーブルのインデックスを再構築する場合は、パーティションごとに再構築することを検討します。

4. インデックスの最適化：パフォーマンスチューニング

インデックスの再構築は、パフォーマンスを改善するための1つのステップに過ぎません。より最適なパフォーマンスを得るためには、インデックスを継続的に監視し、調整する必要があります。

4.1 インデックスの作成と削除:

必要なインデックスのみを作成します。 不要なインデックスは、ディスクスペースを無駄にし、データの更新パフォーマンスを低下させる可能性があります。
複合インデックスを作成する場合は、列の順序を慎重に検討します。 クエリで最も頻繁に使用される列を最初に配置します。
インデックスを作成する前に、クエリの実行計画を分析します。 どの列にインデックスを作成すると効果的かを判断します。
不要になったインデックスは、削除します。

4.2 インデックスの統計情報の管理:

定期的にインデックスの統計情報を更新します。 オプティマイザは、クエリの実行計画を決定するために、インデックスの統計情報を使用します。統計情報が古くなっていると、オプティマイザは最適な実行計画を選択できなくなり、パフォーマンスが低下する可能性があります。
DBMS_STATS パッケージを使用して、統計情報を収集するタイミングと頻度を制御します。
- GATHER_TABLE_STATS: テーブルの統計情報を収集します。
- GATHER_INDEX_STATS: インデックスの統計情報を収集します。
- GATHER_SCHEMA_STATS: スキーマ内のすべてのオブジェクトの統計情報を収集します。
- GATHER_DATABASE_STATS: データベース全体の統計情報を収集します。
自動統計情報収集機能を有効にします。 Oracleデータベースは、自動的に統計情報を収集する機能をサポートしています。

4.3 SQLクエリのチューニング:

SQLクエリを最適化することで、インデックスの使用効率を高めることができます。
不要なテーブルスキャンを避け、インデックスを使用するようにクエリを書き換えます。
ヒントを使用して、オプティマイザに特定のインデックスを使用するように指示します。 ただし、ヒントの使用は慎重に行う必要があります。
EXPLAIN PLANを使用して、クエリの実行計画を分析し、改善点を見つけます。

4.4 パーティションインデックス:

大規模なテーブルでは、パーティションインデックスを使用することを検討します。 パーティションインデックスは、テーブルをパーティションに分割し、各パーティションに個別のインデックスを作成します。これにより、クエリのパフォーマンスを向上させることができます。
グローバルインデックスとローカルインデックスのどちらを使用するかを慎重に検討します。 グローバルインデックスは、すべてのパーティションにまたがるインデックスであり、ローカルインデックスは、各パーティションに個別のインデックスです。

4.5 その他の最適化テクニック:

Index-Organized Table (IOT) の使用: プライマリキーによる検索に非常に適しています。
Bitmap インデックスの使用: カーディナリティが低い列に適しています。
関数ベースインデックスの使用: 列の値に関数を適用した結果に基づいて検索する場合に有効です。

5. インデックスの監視：継続的なパフォーマンス維持

インデックスの最適化は、一度行ったら終わりではありません。パフォーマンスを維持するためには、インデックスの状態を継続的に監視する必要があります。

5.1 監視ツール:

Oracle Enterprise Manager (OEM): Oracleが提供する包括的な監視ツールです。インデックスの状態、パフォーマンス、使用状況などを監視できます。
SQL Developer: Oracleが提供する無料の開発ツールです。インデックスの構造、統計情報、使用状況などを確認できます。
サードパーティ製の監視ツール: 様々なサードパーティ製の監視ツールが利用可能です。

5.2 監視項目:

インデックスの断片化: 定期的にインデックスの断片化をチェックし、必要に応じて再構築します。
インデックスの統計情報: 定期的にインデックスの統計情報を更新します。
インデックスの使用状況: どのインデックスが使用されているか、使用されていないかを定期的にチェックします。
クエリのパフォーマンス: クエリのパフォーマンスを監視し、パフォーマンスが低下している場合は、関連するインデックスを調査します。
ディスクスペースの使用量: インデックスが過剰なディスクスペースを消費していないかを監視します。
無効なインデックス: 無効なインデックスがないかを定期的にチェックします。

5.3 アラート設定:

パフォーマンスの問題が発生した場合にアラートを発行するように監視ツールを設定します。 例えば、クエリの実行時間が異常に長くなった場合、インデックスの断片化が一定レベルを超えた場合、統計情報が一定期間更新されていない場合などにアラートを発行するように設定できます。

5.4 定期的なレビュー:

定期的にインデックスの設計を見直し、最適化を行います。 新しいクエリの追加、データの変更、ビジネス要件の変更などにより、インデックスの設計を見直す必要が生じる場合があります。

まとめ

Oracleデータベースのインデックスは、パフォーマンスを向上させるために不可欠な要素です。しかし、インデックスは時間とともに断片化したり、統計情報が古くなったり、不要なインデックスが残ったりすることで、パフォーマンスボトルネックとなる可能性があります。

本記事では、インデックスの状態確認、分析、再構築、および最適化に関する包括的なガイドを提供しました。インデックスの役割と種類、状態確認の方法、再構築の判断基準と手順、そしてパフォーマンス維持のための継続的な監視について詳しく解説しました。

これらのテクニックを適用することで、Oracleデータベースのパフォーマンスを最適化し、アプリケーションの応答性を高めることができます。定期的なメンテナンスと監視を通じて、データベースのパフォーマンスを常に最高の状態に保ちましょう。