Oracle Databaseには、SQL文を実行する際に最適な方法（実行計画）を決める「オプティマイザ」機能が備わっています。本記事ではオプティマイザ機能の基本について解説していきます。
オプティマイザとは
オプティマイザとは
同じSQL文を実行しても、格納されているデータブロックの状況やインデックスの有無によって、実行計画は異なります。そのような状況でオプティマイザは最も効率よく実行結果を返すための実行計画を評価・選択してくれる機能です。
オプティマイザの目的
オプティマイザの主な目的は、SQL文に対して最も効率的な実行計画を作成・選択し、データベース全体のパフォーマンスを向上させることです。具体的なパフォーマンス目標としては、以下の3つが挙げられます：
- 応答時間の短縮（ユーザーへのレスポンス向上）
- システムリソースの最適化（CPU・メモリ・I/Oの効率化）
- スループットの向上（同時処理可能なトランザクション数の増加）
オプティマイザはこれらの目標を達成するために、各実行計画候補に対して「コスト」を算出し、最も低コストな実行計画を選択します。このコストには、主に以下のようなリソース消費が反映されています：
- ディスクI/Oの量（I/Oコスト）
- CPU使用量（CPUコスト）
- メモリの使用状況
- ネットワーク通信コスト（分散処理環境など）
これらの内部的なコスト評価に基づいて、オプティマイザは最終的な実行計画を決定します。
実行計画の仕組みと選択の流れ
大量のデータを扱うデータベースでは、同じSQLでも実行方法によって処理時間が大きく異なります。なぜなら、テーブルのレコード行数やインデックスの有無、結合条件、実行順序、アクセスパスなどが異なるためです。
オプティマイザは、SQL文に対して複数の実行計画候補を生成し、それぞれのコストを比較した上で、最も効率的な計画を選択します。
オプティマイザの構成要素
オプティマイザは、以下の3つのコンポーネントから成り立っています。
問合せトランスフォーマー
問合せトランスフォーマーは、SQLクエリの意味を保持したまま、効率的な形式に書き換える役割を担います。書き換える内容は、ビューの結合や不要な結合の削除、述語の移動および変換などです。問合せトランスフォーマーが変換した複数の表現を元に、エスティメータがコストを見積もり、最もコストの低い実行計画が選択されることになります。
エスティメータ
エスティメータは、各操作のコストを見積り、実行計画全体のコストを判断するコンポーネントです。コストは、データディクショナリに格納された統計情報から、I/O、CPU、メモリ使用量などのリソース消費を予測し計算します。これにより、システムリソースが最適化された実行計画が選択されます。
プランジェネレータ
プランジェネレータは、複数の実行計画を生成します。
同じSQL文でも、インデックスの有無や統計情報の違い、結合方法などにより、異なる実行計画が生成される場合があります。理論上無数の実行計画が考えられますが、プランジェネレータによって実用的な候補のみを生成します。
SQLチューニングの概要
SQLチューニングの必要性
SQLチューニングは、SQLの実行速度やレスポンス、スループットが低下し、改善が必要な場合に行います。SQLの実行速度が思うように出ない原因の1つとして、オプティマイザが適切な実行計画を選択していないケースが考えられます。
オプティマイザが適切な実行計画を選択しない要因として考えられるのは、以下が挙げられます。
- オプティマイザ統計情報が古い
- インデックスが適切に設定されていない
- Oracleのバージョンアップによって作成される実行計画が変わった
- 運用によってデータ量やデータの分布が変わった
- 複雑なSQL文のため、適切な実行計画が選択されにくい
SQLチューニングを行う対象のSQLは、AWR（Automatic Workload Repository）やASH（Active Session History）などのOracle診断ツールを利用し、実行時間が長いSQLや頻繁に実行されるSQLを特定します。次に、EXPLAIN PLANやAUTOTRACEなどのツールを使用して現在の実行計画を確認し、非効率の箇所を特定します。
SQLチューニングのアプローチ
SQLチューニングのアプローチとしては、以下の方法があります。
- テーブルやインデックスの設計見直し
- SQL文の書き換え
- ヒント句の追加
- 統計情報の更新
SQLチューニングは、Oracle SQL Developer や Enterprise Manager、SQLチューニングアドバイザなどのツールを活用すると、より効率よくチューニングが行えます。
オプティマイザに影響を与える手法
SQLチューニングの概要にもありますが、統計情報やヒント句など、オプティマイザが実行計画を作成する際に影響を及ぼす項目が存在します。これらを調整することで実行計画を意図的に変えることができます。オプティマイザの動作を変更したい場合の参考にしてください。
オプティマイザ統計
オプティマイザ統計とは、実行計画を生成する際に用いる情報であり、具体的には以下の情報が含まれています。
- テーブルの行数
- カラムの値の分布（最大値、最小値など）
- インデックスに関する情報（カーディナリティ・クラスタファクタなど）
- 述語（結合条件やWHERE句など）の選択度
オプティマイザはこれらの統計をもとに、どの実行プランが最もコスト効率に優れているかを判断します。
データベースを運用していると、データの追加や更新、削除によってテーブルの行数やカラムの値は変わってきます。そのため、定期的に統計情報を取得しなければいけません。古い統計情報を使用していると、その情報をもとに誤った実行計画を作成してしまうためです。
統計情報の収集は、DBMS_STATSパッケージを使用します。なお、デフォルトで自動メンテナンスタスクによって定期的に取得されます。
初期化パラメータ
データベースの動作を制御する初期化パラメータには、オプティマイザの動作を調整するものがあります。代表的なパラメータを表にまとめました。
|
パラメータ名 |
説明 |
|
OPTIMIZER_MODE |
インスタンスの最適化方法を選択する |
|
OPTIMIZER_FEATURES_ENABLE |
指定したバージョンにあわせてオプティマイザ機能を揃える |
|
OPTIMIZER_DYNAMIC_SAMPLING |
動的統計を収集するタイミングと、統計の収集にオプティマイザが使用するサンプル・サイズを制御する |
|
OPTIMIZER_ADAPTIVE_PLANS |
適応問合せ計画の有効・無効 |
|
OPTIMIZER_USE_SQL_PLAN_BASELINES |
SQL計画ベースラインを使用するかどうか |
|
OPTIMIZER_IGNORE_HINTS |
ヒント句を無視するかどうか |
初期化パラメータの調整は強力なチューニング手法ですが、システム全体に影響を与えるため、慎重にテストと検証を実施しましょう。
ヒント
ヒント句は、Oracle SQL内でオプティマイザに対して「この実行計画を選択してほしい」という指示を与えるための機能です。通常、オプティマイザは統計情報をもとに自動で最適な実行計画を選択しますが、場合によってはユーザーが意図する実行計画と異なる場合があります。その場合はヒント句を使うことである程度制御できます。例えば、以下の制御が可能です。
・インデックスの使用
・結合方法の指定
・結合順序の指定
・並列処理
ヒント句を使用する場合は、SQL文に /*+ INDEX(table_name index_name) */ のように記述することで、特定のインデックスを強制的に使用させることができます。ただし、ヒント句の多用は逆に保守性を下げたり、パフォーマンスが低下したりするリスクもあるため、十分な検証を行ったうえで使用しましょう。
SQL計画ベースライン
SQL計画ベースラインは、過去に優れたパフォーマンスを示した実行計画を保存し、以降も安定してその計画を使用できるようにする仕組みです。SQL計画ベースラインを導入することで、意図しないパフォーマンス劣化を防ぎ、実行計画の安定性を保つことができます。
実行計画の保存は、主にDBMS_SPMパッケージを使用します。事前に優れた実行計画を「計画履歴」に保存し、使用する実行計画は「承認済み計画」として設定しておきます。その後オプティマイザが新しい実行計画を生成すると、承認済み計画と比較し、承認済み計画を優先して使用します。逆に、より優れた実行計画が生成されると「未承認計画」として保存され、未承認計画はユーザーが確認し、承認済み計画とするか選択することが可能です。
SQL計画ベースラインの実装には、重要なSQLワークロードの計画をベースラインとして登録しておくと、アップグレード後のパフォーマンス問題を未然に防ぐことが可能です。
まとめ
本記事では、オプティマイザについて解説しました。
Oracle DatabaseでSQLの実行速度を改善したい場合は、オプティマイザに関する知識が不可欠です。特に、SQLを変更していないにもかかわらず応答速度が遅くなったり、スループットが低下したりした場合は、オプティマイザが作成する実行計画に問題がある可能性が高いです。
SQLチューニングは初心者には難しいと感じる人もいるかもしれませんが、本記事の内容を参考に、Oracle SQL Developer や Enterprise Managerなどのツールを使うことで簡単なチューニングが可能です。ぜひお役立てください。
![いまさら聞けない Oracleの基本 [初級編]](https://products.sint.co.jp/hubfs/images/siob/thumb/ora_b.png)
![いまさら聞けない Oracleの基本 [中級編]](https://products.sint.co.jp/hubfs/images/siob/thumb/ora2.webp)
