オプティマイザのインプット情報とは

　DBの頭脳であるオプティマイザは、処理の実行計画を算出する際にある情報を参考にします。それがインプット情報です。
　
　インプット情報とは一種類の情報ではなく、いくつかの種類によって構成されています。
　代表的なものは以下のようなものになります。

統計情報
1. 表の統計情報
2. 列の統計情報
3. 索引の統計情報
4. システム統計情報
SQLテキスト
初期化パラメータ

各種情報について

　実行計画の算出にあたり、オプティマイザが使用する各種インプット情報についてのまとめです。

統計情報(全般

統計情報とは、オブジェクトやデータの実際の状況に関する詳細情報です。
　ここで設定された値が、オプティマイザのコスト計算に直接影響を与えます。
統計情報は表や列、索引についての情報ですが、必ずしも最新の情報を保持しているとは限りません。
統計情報は常に更新されるものではないですが、実データは常に更新され続けるので、統計情報と実データ状況の２つのデータの間には、乖離が発生することがあります。
オプティマイザは実データではなく統計情報に基づいて実行計画を算出するため、統計情報が誤った状態であれば実行計画も誤ったものが算出されてしまいます。
したがって、SQLチューニングの際には統計情報を確認し、現在の実データとの乖離がないことを確認することが必要です。
なお11gR2では、デフォルトの設定で約１日に１回の頻度で統計情報が自動的に取得されます。
オプティマイザ統計に関する情報は、以下のテーブルなどで確認することができます。

テーブル名	概要
ALL_TAB_STATISTICS	現行のユーザーがアクセスできる表についてのオプティマイザ統計情報を示します
ALL_IND_STATISTICS	現行のユーザーがアクセスできる表の索引についてのオプティマイザ統計を示します
ALL_TAB_COLUMNS	現行のユーザーがアクセスできる表、ビューおよびクラスタの列を示します

※テーブル名クリックによりリファレンスに遷移

表統計情報

　アクセス対象となる表についての情報です。以下のようなものがあります。

統計情報	概要	参照元	参照元：列名
格納行数	表の行数	ALL_TAB_STATISTICS	num_rows
データベースブロック数	HMWまでのブロック数	ALL_TAB_STATISTICS	blocks
平均レコードサイズ	１行当たりの平均サイズ	ALL_TAB_STATISTICS	avg_row_len

索引統計情報

　アクセス対象となる索引についての情報です。以下のようなものがあります。

統計情報	概要	参照元	参照元：列名
リーフブロック数	索引のブロック数	ALL_IND_STATISTICS	leaf_blocks
索引の高さ	Bツリーの索引の高さ	ALL_IND_STATISTICS	blevel
クラスタリングファクタ	索引列データの表での分散度合い	ALL_IND_STATISTICS	clustering_factor

列統計情報

　アクセス対象となる列についての情報です。以下のようなものがあります。

統計情報	概要	参照元	参照元：列名
列に存在する値の種類	列内の値の種類。NDVとも。	ALL_TAB_COLUMNS]	num_distinct
列内のnull数	列内のnullの数	ALL_TAB_COLUMNS]	num_nulls
ヒストグラム	列データの分布状況	ALL_TAB_COLUMNS]	histgram

システム統計情報

　システムのハードウェア特性をもとにコスト算出を最適化するためのインプット情報です。
　通常のSQLチューニングでは、ほとんど意識する必要はありません。

SQLテキスト

実際に発行されるSQLテキストそのものです。不適切な記述方法がされている場合、適切な実行計画を作成することができませんのでテキストそのものを見直すことが必要です。

　SQLを作成するにあたり注意すべき点の代表例は、以下のようなものです。

問題のある処理	発生する問題
索引の使用を想定している列に演算処理を行う	索引が適用されない
NULL比較やNOT(!=)を使用	索引が適用されない
where句内の条件でバインド変数を使用しない	不要な解析が発生する

初期化パラメータ

　オプティマイザ関連のパラメータ設定です。データベース設計時に適切な設定を行うことが求められます。
　一時的なパフォーマンス問題のために本項目を修正することは、あまり現実的ではありません。

実行計画作成までの流れ

　実行計画の作成はその環境により千差万別といえますが、ここでイメージを作るために一例を挙げます。
　以下のようなSQLを発行した際、オプティマイザが実行計画を作成するまでの過程イメージを記載します。

実行するSQLイメージ


select 
  A.emp_name
from
  table_A A, 
  table_B B
where
  A.id  = B.id

CPU速度やIO関連の情報を確認する (システム統計情報、初期化パラメータの取得)

物理的なレイヤでの問題として、データファイルへのアクセスなどに伴うコスト情報を取得します。
SQLがアクセスしている table_A 表と table_B 表の情報を確認する(表統計情報、列統計情報、索引統計情報の取得)

　アクセス対象となる表、列、索引についての統計情報を取得します。
二つの表に対して単一アクセスの場合のアクセスパスを決定する(各種統計情報の使用)

　取得した統計情報などをもとに、単一アクセスの際のパスとそのコストを計算します。
　table_B においては id 列を参照していますが、そのコスト計算が以下のように行われれば索引スキャンが行われます。

表フルスキャン：コスト＝10.0
索引ユニークスキャン：コスト＝3.0

二つの表に対して結合方法と結合順序を決定する(各種統計情報の使用)
　表の結合についてのコスト計算を行います。　table_A における id列の参照については以下のようなコスト計算がなされたとします。

表フルスキャン：コスト20.0

　表の結合においてはコストの低いものをベース（外部表）にした結合を行うことが望ましいので、table_B を外部表、table_Aを内部表としたネステッドループ結合を行うことなどを決定します。

実行計画の作成
　ここまでの情報をもとに、実行計画を作成します。

参考

本記事を執筆するにあたり、以下の情報サイト・書籍などを参考にさせていただいています。

情報サイト
- 津島博士のパフォーマンス講座：http://www.oracle.com/technetwork/jp/database/articles/tsushima/index.html
- 統計情報はなぜ必要か：http://www.doppo1.net/oracle/tuning/statistics_outline.html

書籍
- 「即戦力のOracle管理術」技術評論社
- 「Oracle SQLチューニング」翔泳社

SQLチューニングについて(2) オプティマイザのインプット情報について