実務を行う上でOracleの実行計画のことが良く分からずに困っていたので、少し古い本ですが「新・門外不出のOracle現場ワザ エキスパートが明かす運用・管理の極意」という本を読んで勉強しました。
詳しい内容は本書参照ですが、簡単に内容をかいつまんで書くと以下の通りです。
【CBOの概要説明】
・RBOとCBO
従来はSQL文だけを解析するRBOが使われていた。
しかし、現在ではオプティマイザを使用するCBOが使われている。
（Oracle7でCBOが登場し、Oracle10gでRBOがサポートされなくなった）
・ソフトパースとハードパース
SQLの実行時に、ソフトパースとハードパースのどちらかが行われる。
ソフトパースは共有プールにキャッシュが存在する場合に使用される。
ハードパースは共有プールにキャッシュが存在しない場合に使用される。
（初めてSQLを実行する場合 or キャッシュアウトされている場合）
ハードパース時に、オプティマイザは使用される。
・オプティマイザの内容
オプティマイザは統計情報を収集している。
下記の統計を収集している。
-表統計（行数、ブロック数、平均行長）
-列統計（列内の平均個数、列内のNULL数、データ分布）
-索引統計（リーフブロック数、ツリーの高さ、クラスタリングファクタ）
-システム統計（I/OやCPUのパフォーマンス）
・実行計画
オプティマイザから実行計画が作成される。
実行計画では、表からどのようにデータを取り出し、
どのような順番・方法で結合するかを定めている。
【CBOの判断内容】
・クラスタリングファクタ(索引の偏り具合を示す値)
一般的には、条件指定により15%以下にデータを絞り込める場合は、
フルテーブルスキャンよりもインデックスアクセスの方が高速とされている。
しかし、索引の偏り具合によっては、
フルテーブルスキャンの方が高速な場合もある。
CBOでは、索引の偏り具合をクラスタリングファクタを見て判断し、
場合によってはフルテーブルスキャンを使用する。
・データ分布(最大値/最小値/ヒストグラム統計)
CBOではSQL文の条件指定を見て、何%の行がヒットするのかを予測する。
予測次第で、フルテーブルスキャンを使うか、
インデックスアクセスを使うかを判断する。
しかし、条件指定だけを見ると、データに偏りがある場合に判断を誤る。
そこで、CBOではデータの偏り具合をヒストグラム統計として取得し、
ヒストグラム統計を見ることで、
データが偏りがある場合にも正確に予測できるようにしている。
・バインド変数とバインドピーク機能
select hogeCol1 from hogeTBL where hogeCol2 = :hogeVer;
のようなSQL文があった場合、「:hogeVer」のことを「バインド変数」と呼ぶ。
バインド変数には、外部から値を指定することができる。
バインド変数を用いると、共有プールメモリ使用量が減少し、
ハードパース発生率も減少するメリットがある。
しかし、バインド変数を使用する場合、バインドピーク機能を使わないと、
デフォルトで「5%の値がヒットする」とCBOは判断してしまう。
そこで、バインドピーク機能を使用することで、
バインド変数にセットされた値を覗き(peekし)、
ヒストグラム統計等を用いて何%の行がヒットするか
予測することができるようになる。
・CURSOR SHARING パラメータ
リテラル値(...= 10 のような定数)を使用したSQL文に対して、
共有カーソルを共有することでバインド変数使用時と同じように、
共有プールメモリ使用量減少等のメリットを得ることができる。
デフォルト値は「EXACT」で、共有カーソルは共有されない。
「FORCE」とした場合は、共有カーソルは共有される。
「SIMILAR」とした場合は、
実行計画が確実に同じになる場合のみ共有カーソルが共有される。
・CPU+I/Oコストモデル
オプティマイザは、I/OやCPUのパフォーマンスを取ることで、
システム固有の性能や特性を考慮した実行計画を生成できる。
そのため、オンライン時間帯やバッチ時間帯を想定した
ワークロードを流して統計を取ることで、
より実運用に適した統計情報を生成することができる。
なお、オプティマイザは、
索引ブロックがバッファキャッシュ上に残っていることを見落とし、
実際よりもI/Oが多く発生すると見積もる可能性がある。
この場合、フルテーブルスキャンが選択されやすくなるため、
下記のパラメータにより補正することができる。
-OPTIMIZER_INDEX_CACHING
-OPTIMIZER_INDEX_COST_ADJ
・スループット重視/レスポンス重視
スループット重視とは、最後の行を返すまでに時間を最速にするものであり、
バッチ処理に適している。
OPTIMIZER_MODEを「ALL_ROWS」にすることでスループット重視になり、
デフォルトではこの状態になっている。
それに対して、レスポンス重視とは、
最初の数行を返すまでの時間を最速にするものであり、
最後の行まで読まれない可能性があるオンライン処理に適している。
OPTIMIZER_MODEを「FIRST_ROWS_n」(n=1,10,100,1000)にすることで
レスポンス重視になる。
OPTIMIZER_MODEはセッションレベルやSQLレベルで指定可能である。
・テーブル結合
テーブル結合では、CBOは実行計画作成時に以下について検討する。
-アクセスパス
各表からのデータの取り出し方法。
(フルテーブルスキャン、インデックスアクセス等)
-テーブルの結合順序
-テーブルの結合方法
(ネステッドループ結合、ソートマージ結合、ハッシュ結合)
本当に最適な方法を計算するとその計算自体に時間がかかってしまう。
そのため、以下の方策により、準最適な方法を短時間で割り出している。
-ヒット件数の少ないと見積もられる順に結合
-ベストコストの保持とコスト計算の途中打ち切り
-評価する結合順序の上限数の設定
【CBOの実運用】
・自動統計収集
Oracleでは、更新された行の割合が増える度に、
自動的にオプティマイザ統計を収集することができる。
これにより、常に最適な実行計画を生成できるようになる。
デフォルトでは自動統計収集がオンになっており、
自動統計収集の詳細は以下のパラメータで設定できる。
-GATHER_STATS_JOB
自動統計収集のジョブのスケジュール設定
-DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC
統計取得内容の詳細の設定
しかし、ミッションクリティカルなシステムでは、
意図せずオプティマイザ統計（やそこから生成される実行計画）
が変更されることを受け入れられないこともある。
その場合は、自動統計収集をオフにし、
手動で統計収集を取りメンテナンスする必要がある。
・統計再収集時の注意点
統計を再収集することで、却って不適切なオプティマイザになるリスクがある。
そのリスクを回避するためには、統計取得時に以下に注意する必要がある。
-統計収集対象に漏れがないこと
-適切なサンプルサイズで統計が取られること
-データの変動に応じた適切な頻度で統計が取られること
-システムの利用状況に応じて統計が取られること
-統計情報のインポート/エキスポートにより、統計情報のバックアップをすること
・動的サンプリング
事前にオプティマイザ統計が取得されていない表に対して、
ハードパースを行った際、動的に統計情報を収集することができる。
（デフォルトではこの設定がONになっている）
統計情報収集時のオーバーヘッドは無視できないものの、
データ量が少ない一時表に対してはこのオーバーヘッドが少ないため効果が高い。
また、列同士の相関関係（例：JOB='MANAGER'だとSALARYが高い）を
考慮できるというメリットもある。
・実行計画の固定
使用頻度や重要性が高いSQL文に対しては、
実行計画が変更されないように固定することが有効になる。
ヒント句の記述やプランスタビリティの機能で固定が可能である。
・自動チューニングオプティマイザ
自動チューニングオプティマイザを使用することで、
性能面で問題のあるSQL文の自動検出が可能になる。
自動検出されたSQL文について、どのような点が問題なのかも知ることができる。
