オプティマイザ統計についてメモ

2種類のオプティマイザ

• CBO(コストベースオプティマイザ)

• オプティマイザ統計からコストを計算して、最少コストの実行計画を決定すること

• RBO(ルールベースオプティマイザ)

• SQL文のルールによって実行計画を決定すること

• 現在のバージョンでは使用されていない

オプティマイザ統計の内容

• テーブル統計
• 行数、ブロック数、平均行数など
• 列統計
• カーディナリティ、NULLの数、最小値、最大値、ヒストグラムなど
• ヒストグラムを取得しないと最小値と最大値で出た分布を均一とする
• 索引統計
• リーフブロック数、ツリーの高さ、クラスタ化係数などの索引の有効性など
• システム統計
• CPU性能、IO性能など

データ分布について例
https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsm05-1598251-ja.html

オプティマイザ統計を取得するときの考慮事項

DBMS_STATSパッケージを使用する

　　ANALYZEコマンドは下位互換。
　　DBMS_STATSパッケージにより、効率的な収集と履歴管理が実施される。

オブジェクトごとに適切な収集頻度がある

　　オブジェクトに対する変更量とオプティマイザ統計が不正確になるレベルによって収集頻度を決定する。
　　静的データに無駄な収集をかけないこと
　　一時表や揮発性の高いデータを格納するテーブルは、事前の統計情報収集が難しいため、
　　SQL解析時に統計情報取得が行われる動的サンプリングにて対応。
　　その他は自動オプティマイザ統計にて実行。
　　ただし、収集時間帯から外れたタイミングでメンテナンスや大量のデータ更新がある場合は、
　　そのあとに手動で取得を行うこと

テーブルは適切なサンプルサイズを設定する

　　テーブルのオプティマイザ統計の際は、ソート処理が行われ、大規模テーブルではかなり時間がかかる。
　　サンプルサイズを指定することで、サンプリングして統計取得ができる。（通常は5%程度で問題なし）
　　10gからはデフォルトで、estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
　　（オブジェクトの行数見積に依存しサンプリングサイズを決める）であり、そのままでだいたいOK。

索引は完全な統計で取得する

　　索引はソート済になっていて、ソート処理が不要のため、完全収集（100%）をする。
　　10gからは索引の作成時や再構成時にオプティマイザ統計が自動収集される。
　　DBMS_STATSパッケージのパラメータもデフォルトは、cascade=>DBMS_STATS.AUTO_CASCADEであり、
　　索引の統計を収集するかどうかとその際の最適なサンプルサイズは、Oracleにて決定する設定となっている。

データに偏りがある列はヒストグラム統計を使用する

　　データの分布に偏りがある列をWHERE句に指定した場合、値によって適切な実行計画が異なる可能性があるため、
　　データの偏りについての統計であるヒストグラム統計を取得するのが有効。
　　10gR2よりDBMS_STATSパッケージのパラメータはデフォルトでmethod_opt=>'FOR ALL COLUMNS SIZE AUTO'であり、
　　ヒストグラム統計を取得することが有効な列とヒストグラム統計を取得する際のバケット数をOracleが自動判定する。
　　（バケット：列のデータを振り分けるバケツのようなもの。多いほどデータの正確な振り分けが可能）
　　WHERE句に複数の列を指定する場合は、複数列の統計を取得することも可能

もしもパフォーマンス劣化してしまったら

　オプティマイザ統計を使用してもパフォーマンスが劣化した場合は、以下を検討すべき。
　1. 劣化前のオプティマイザ統計の戻す
　　劣化前のオプティマイザ統計に戻して、統計情報をロックする。
　　オプティマイザ統計の履歴テーブル(dba_tab_stats_history)を確認して、
　　DBMS_STATS.RESTORE_TABLES_STATSを使用してリストアする。
　2. 正確なオプティマイザ統計を収集する
　　サンプルサイズを完全にして、オプティマイザ統計を収集する。
　　オプティマイザ統計の取得に時間がかかるようであれば、サンプルサイズを少しずつ小さくして調整する。
　3. ヒント文を使用して問題を回避する
　　劣化前後の実行計画を比較して、改善ポイント（特定の索引を使用している）などがあれば、
　　ヒント文を使用して、実行計画を改善する

実行計画を変えないという選択

　収集するオプティマイザ統計により実行計画は変化してしまうため、変えたくない場合もある。
　その場合は、ヒント句を使用する。
　埋込SQLなどヒント句を使用できない場合は、プラン・スタビリティを使用する。
　プラン・スタビリティ：実行計画を実現するためのアウトライン「後で埋め込むためのヒント文の集まり」を作成して、実行時にSQL文にアウトラインを埋め込んでくれる
　データ分布があまり変わらない場合に実行計画の固定は有効
　SQL実行計画管理（SPM）が11gより使用でき、ベースラインに設定した実行計画と比較して、
　それよりも効率的でない場合は、ベースラインを使用するようにすることもできる。
　

Tips

自動オプティマイザ統計情報取得

　　10gより追加された機能。以下のオブジェクトに関して、オプティマイザ統計を取得する。
　　　・オプティマイザ統計をまだ収集していないオブジェクト
　　　・オプティマイザ統計が最新状態でない可能性のあるオブジェクト（10%以上の行が更新されているオブジェクト）

動的サンプリング

　　オプティマイザ統計が不足している場合、SQL文実行時にオプティマイザ統計をサンプリングして実行計画を作成する。
　　初期パラOPTIMIZER_DYNAMIC_SAMPLINGにて設定。デフォルトは2であり、オプティマイザ統計が収集されていないオブジェクトすべてに対して64ブロックのサンプリングアクセスを行う。