津島博士のパフォーマンス講座
第5回 オプティマイザとオプティマイザ統計の収集について
今回は、オプティマイザ統計（Statspackなどで収集する統計情報と区別するため、オプティマイザが実行計画作成のために使用するものをオプティマイザ統計と言うことにします）の収集について説明します。最近、次のようにオプティマイザ統計の収集ついて聞かれる機会がありましたので、ここで説明することにしました。
「コストベース・オプティマイザ（CBO）だけになったためにオプティマイザ統計の収集は必須になりましたが、オブジェクトが多いと収集時間も軽視することができません。かといって収集時間を短縮したいがためにサンプル・サイズを小さくしたり、収集頻度を減らしたりすることで実行計画が最適でなくなったら困ります。オプティマイザ統計の収集に対する指針などあれば教えてください。」
これには、こうすれば良いという明確な指針はないので、皆さん結構悩まれているのではないかと思います。これについて、私の考えをまとめてみましたので、参考にして下さい。
■１．オプティマイザとは
先ずはオプティマイザについて説明します。オプティマイザ統計からコストを計算して、最少コストの実行計画を決定するのがコストベース・オプティマイザ（CBO）です（これに対してSQL文のルールによって実行計画を決定するのがルールベース・オプティマイザ（RBO）です。以前のバージョンでは両方使用できましたが今はCBOだけです）。
そのオプティマイザ統計には、次に示すものなどがあります。
「コストベース・オプティマイザ（CBO）だけになったためにオプティマイザ統計の収集は必須になりましたが、オブジェクトが多いと収集時間も軽視することができません。かといって収集時間を短縮したいがためにサンプル・サイズを小さくしたり、収集頻度を減らしたりすることで実行計画が最適でなくなったら困ります。オプティマイザ統計の収集に対する指針などあれば教えてください。」
これには、こうすれば良いという明確な指針はないので、皆さん結構悩まれているのではないかと思います。これについて、私の考えをまとめてみましたので、参考にして下さい。
■１．オプティマイザとは
先ずはオプティマイザについて説明します。オプティマイザ統計からコストを計算して、最少コストの実行計画を決定するのがコストベース・オプティマイザ（CBO）です（これに対してSQL文のルールによって実行計画を決定するのがルールベース・オプティマイザ（RBO）です。以前のバージョンでは両方使用できましたが今はCBOだけです）。
そのオプティマイザ統計には、次に示すものなどがあります。
- テーブル統計（行数、ブロック数、平均行長など）
- 列統計（カーディナリティ、NULLの数、最小値、最大値、ヒストグラムなど。ヒストグラムがない場合は、最小値と最大値でデータ分布を均一とする）
- 索引統計（リーフブロック数、ツリーの高さ、クラスタ化係数などの索引の有効性）
- システム統計（CPU性能、I/O性能など）
これを使用してCBOは、どのようにアクセスする（フルスキャンまたは索引スキャン、どのような順番でどう結合するかなど）と最少コストになるかを見積もります（アクセス行数が最少になるように）。これが正確でないと最適な実行計画にはならないため（例えば、図のように列Aのデータ分布が偏っているのにヒストグラムを収集していないため均一と判断される）、オプティマイザ統計はパフォーマンスにはとても重要な情報になります。
■２．どのように収集するのか
それでは、どのようにオプティマイザ統計を収集すれば良いか説明します。基本は、次に示すガイドラインで行います。これでもパフォーマンスが劣化する場合はありますので、その場合の対処については後で説明します。
それでは、どのようにオプティマイザ統計を収集すれば良いか説明します。基本は、次に示すガイドラインで行います。これでもパフォーマンスが劣化する場合はありますので、その場合の対処については後で説明します。
- DBMS_STATSパッケージを使用
ANALYZEコマンドも下位互換のために残されていますがDBMS_STATSパッケージを使用して下さい（正確な統計情報が効果的に収集され履歴管理も自動的に行ってくれます）。
- オブジェクトごとに適切な収集頻度
オブジェクトに対する変更の量と、それでオプティマイザ統計が不正確になるレベルによって収集頻度を決定します。このとき静的データなどに対して無駄な収集をしないように注意して下さい。こう記述してもよく分からないと思いますので、通常は次のように行って下さい（Oracleが適切に行います）。
一時表や揮発性の高いデータを格納するテーブル（頻繁に大量行のDELETEまたはTRUNCATEされるようなテーブル）は動的サンプリング（後で説明）を行います（統計情報を削除後にロックすることで常に動的サンプリングを行うことができます。以下のSQL文で統計ロック状態のオブジェクトを確認できます）。
一時表や揮発性の高いデータを格納するテーブル（頻繁に大量行のDELETEまたはTRUNCATEされるようなテーブル）は動的サンプリング（後で説明）を行います（統計情報を削除後にロックすることで常に動的サンプリングを行うことができます。以下のSQL文で統計ロック状態のオブジェクトを確認できます）。
その他は自動オプティマイザ統計収集（後で説明）を行います。ただし、収集する時間帯から外れたタイミングでメンテナンスや大量のデータ更新が行われた場合には、その後に対象テーブルを手動でオプティマイザ統計収集するようにします。
- テーブルには適切なサンプル・サイズの統計
テーブルのオプティマイザ統計収集にはソート処理を行う必要がありますので、大規模なテーブルでは非常に時間がかかります。そのためテーブルについてはサンプル・サイズを指定して行います（通常は5％程度のサンプル・サイズで問題ありません）。
DBMS_STATS パッケージのパラメータの値も、Oracle Database 10gからのデフォルトからはestimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE（オブジェクトの行数の見積もりに依存したサンプリングサイズ）になりましたので、これで問題ないと思います（オプティマイザ統計を収集した時のサンプル・サイズは以下のSQL文で確認できます）。
DBMS_STATS パッケージのパラメータの値も、Oracle Database 10gからのデフォルトからはestimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE（オブジェクトの行数の見積もりに依存したサンプリングサイズ）になりましたので、これで問題ないと思います（オプティマイザ統計を収集した時のサンプル・サイズは以下のSQL文で確認できます）。
- 索引には完全な統計
索引はソート済みになっている（ソート処理が必要ない）ので、完全に（100％で）収集します。
Oracle Database 10gからは索引作成または再構築時にオプティマイザ統計が自動的に収集されます（Oracle9iでは索引作成または再構築時にCOMPUTE STATISTICSオプションを指定することで収集されます）ので、作成後に収集する必要はありません。
こちらのDBMS_STATS パッケージのパラメータの値も、Oracle Database 10gからのデフォルトはcascade=>DBMS_STATS.AUTO_CASCADE（索引の統計を収集する必要があるかどうかと最適なサンプル・サイズを Oracle が決定する）になりましたので、これで問題ないと思います。以下のSQL文で索引のオプティマイザ統計を確認できます。
Oracle Database 10gからは索引作成または再構築時にオプティマイザ統計が自動的に収集されます（Oracle9iでは索引作成または再構築時にCOMPUTE STATISTICSオプションを指定することで収集されます）ので、作成後に収集する必要はありません。
こちらのDBMS_STATS パッケージのパラメータの値も、Oracle Database 10gからのデフォルトはcascade=>DBMS_STATS.AUTO_CASCADE（索引の統計を収集する必要があるかどうかと最適なサンプル・サイズを Oracle が決定する）になりましたので、これで問題ないと思います。以下のSQL文で索引のオプティマイザ統計を確認できます。
- データに偏りがある列はヒストグラム統計
データの分布に偏りがある列をWHERE句の条件に使用したSQL文は実行計画が最適でない可能性がある（前回の例のように、苗字が'鈴木'さんと'林'さんでは該当件数が異なりますので、最適な実行計画も異なる可能性がある）ので、正確なデータ分布を収集する必要があります。
DBMS_STATS パッケージのパラメータの値も、通常はmethod_opt=>'FOR ALL COLUMNS SIZE AUTO'（Oracle Database 10gR2からのデフォルト）で問題ありません（Oracleが自動的にどの列がヒストグラムを必要とするかどうかと、各ヒストグラムが必要とするバケットの数（多いほど精度が高い）を判別します）。ヒストグラムのバケット数は以下のSQL文で確認できます。
DBMS_STATS パッケージのパラメータの値も、通常はmethod_opt=>'FOR ALL COLUMNS SIZE AUTO'（Oracle Database 10gR2からのデフォルト）で問題ありません（Oracleが自動的にどの列がヒストグラムを必要とするかどうかと、各ヒストグラムが必要とするバケットの数（多いほど精度が高い）を判別します）。ヒストグラムのバケット数は以下のSQL文で確認できます。
SQL文のWHERE句に複数の列を指定していると列統計が正しくない場合がありますので、場合によっては「複数列の統計」（後で説明）を収集することを検討して下さい。
また、Oracle Database 10g以降で追加された新機能にも、統計情報の収集に便利な機能があります。
以下にご紹介しますので、是非ご活用ください。
また、Oracle Database 10g以降で追加された新機能にも、統計情報の収集に便利な機能があります。
以下にご紹介しますので、是非ご活用ください。
（１）自動オプティマイザ統計収集
Oracle Database 10g以降のバージョンでは、デフォルトで自動オプティマイザ統計収集機能が有効です。そのため、次のオブジェクトに関して自動的にオプティマイザ統計が収集されます。
Oracle Database 10g以降のバージョンでは、デフォルトで自動オプティマイザ統計収集機能が有効です。そのため、次のオブジェクトに関して自動的にオプティマイザ統計が収集されます。
- オプティマイザ統計をまだ収集していないオブジェクト
- オプティマイザ統計が最新の状態でない可能性のある（10％以上の行が更新された）オブジェクト
月曜日から金曜日は22:00～翌朝2:00、土曜日から日曜日は6:00～翌朝2:00の間（Oracle Database 10g以降では週末は土曜0時～日曜23:59:59の間）に収集するように事前定義されていますので、運用に合わせて時間を調整して使用して下さい。次のSQL文で自動メンテナンス・タスクのウィンドウ名、実行日時、ステータスが確認できます。
このとき、dbms_stats.gather_database_stats_job_procプロシージャ（dbms_stats.gather_database_stats (options=>GATHER AUTO)の実行とほぼ同じ）が実行されます（並列度は、テーブルのPARALLEL句に指定されたデフォルト並列度が使用されますので、大規模テーブルに対して収集時間を高速化したい場合は対象テーブルのデフォルト並列度を調整して下さい）。これらのデフォルト値を確認または変更するには、get_prefsプロシージャとset_*_prefsプロシージャを使用して行います（Oracle Database 10gまではdbms_stats.get_paramとdbms_stats.set_paramを使用します）。次のSQL文が変更および確認の例です。
（２）動的サンプリング（初期化パラメータOPTIMIZER_DYNAMIC_SAMPLING）
オプティマイザ統計が不足しているとSQL文実行時に自動的にオプティマイザ統計をサンプリングして実行計画を作成します（オプティマイザ統計はディクショナリに格納されないので、共有プールから削除されると再実行されます。そのため、その対象テーブルをアクセスするSQL文が沢山ある場合は、手動で収集するようにして下さい）。
Oracle Database 10gからのデフォルト（初期化パラメータOPTIMIZER_DYNAMIC_SAMPLING=2）では、オプティマイザ統計が収集されていない全てのテーブルについて動的サンプリングを行います（サンプリングでアクセスされるブロック数はOracle Database 10gからは64ブロックになります）ので、通常はこれで問題ありません（ただし、複雑なSQL文などはデフォルト以外にする必要もあります。これの説明については別の機会にします。）。
テーブルを常に動的サンプリングする場合には、次のようにオプティマイザ統計を削除後にロックして下さい。これで初期化パラメータOPTIMIZER_DYNAMIC_SAMPLING=2以上で常に動的サンプリングされます。
オプティマイザ統計が不足しているとSQL文実行時に自動的にオプティマイザ統計をサンプリングして実行計画を作成します（オプティマイザ統計はディクショナリに格納されないので、共有プールから削除されると再実行されます。そのため、その対象テーブルをアクセスするSQL文が沢山ある場合は、手動で収集するようにして下さい）。
Oracle Database 10gからのデフォルト（初期化パラメータOPTIMIZER_DYNAMIC_SAMPLING=2）では、オプティマイザ統計が収集されていない全てのテーブルについて動的サンプリングを行います（サンプリングでアクセスされるブロック数はOracle Database 10gからは64ブロックになります）ので、通常はこれで問題ありません（ただし、複雑なSQL文などはデフォルト以外にする必要もあります。これの説明については別の機会にします。）。
テーブルを常に動的サンプリングする場合には、次のようにオプティマイザ統計を削除後にロックして下さい。これで初期化パラメータOPTIMIZER_DYNAMIC_SAMPLING=2以上で常に動的サンプリングされます。
（３）複数列の統計
SQL文のWHERE句に複数の列を条件として指定している場合には列統計が正しくない場合があります。例えば、次のSQL文に対してc1とc2の複数列索引が作成されていた場合に、索引スキャンするかフルスキャンするかを判断するのは、それぞれの列単独の統計では正しく判断できない場合があります。これは、二つの列の組合せによるデータ分布でないと正確ではありません（二つの列の値に対して何件ヒットするかを見積もる必要があるからです）。
SQL文のWHERE句に複数の列を条件として指定している場合には列統計が正しくない場合があります。例えば、次のSQL文に対してc1とc2の複数列索引が作成されていた場合に、索引スキャンするかフルスキャンするかを判断するのは、それぞれの列単独の統計では正しく判断できない場合があります。これは、二つの列の組合せによるデータ分布でないと正確ではありません（二つの列の値に対して何件ヒットするかを見積もる必要があるからです）。
このような場合などはOracle Database 11gからの複数列の統計（列グループとして統計）を収集します（Oracle Database 10g以前ではヒント文などで調整する必要があります）。例えば、上記SQL文のようにWHERE句に列c1と列c2が条件として指定されていたとすると、次のように収集します。
■３．パフォーマンス劣化した場合
そうはいってもオプティマイザ統計やCBOは完璧ではありません（すべてのオプティマイザ統計が正確とは限らない場合や、完全なオプティマイザ統計を使用してもCBOが選択する実行計画が最適とは限らない場合があります）。そのため、オプティマイザ統計を収集してパフォーマンスが劣化する場合もあります。その場合は、以下の対象方法を行って下さい。
そうはいってもオプティマイザ統計やCBOは完璧ではありません（すべてのオプティマイザ統計が正確とは限らない場合や、完全なオプティマイザ統計を使用してもCBOが選択する実行計画が最適とは限らない場合があります）。そのため、オプティマイザ統計を収集してパフォーマンスが劣化する場合もあります。その場合は、以下の対象方法を行って下さい。
- 劣化前のオプティマイザ統計に戻す
劣化前のオプティマイザ統計に戻して統計情報をロックすることで固定化できます。
Oracle Database 10gからはDBMS_STATSパッケージで自動的に履歴管理されます。例えば、次のSQL文のように「11-03-22 11:28:09.105833」に取得したオプティマイザ統計をリストアするには、このオプティマイザ統計を使用していた時間「11-03-22 12:00:00.000000」（戻したい列STATS_UPDATE_TIMEの値に少しプラスした時間）をタイムスタンプ'as_of_timestamp'で指定します。
Oracle Database 10gからはDBMS_STATSパッケージで自動的に履歴管理されます。例えば、次のSQL文のように「11-03-22 11:28:09.105833」に取得したオプティマイザ統計をリストアするには、このオプティマイザ統計を使用していた時間「11-03-22 12:00:00.000000」（戻したい列STATS_UPDATE_TIMEの値に少しプラスした時間）をタイムスタンプ'as_of_timestamp'で指定します。
- 正確なオプティマイザ統計を収集する（サンプル・サイズやヒストグラムのバケット数などを大きくする）
サンプル・サイズを完全で収集して性能が改善されたら、それで収集するようにする（時間的な問題がある場合は、サンプル・サイズを少しづつ小さくして実行計画に影響しないところに調整して下さい）。
- ヒント文などを使用して問題点を回避する
劣化前の実行計画と比較して異なる部分に対してヒント文を入れる。例えば、索引'ix_tab01_c1'を使用したい場合は次のようにINDEXヒント（赤字の部分）を入れます（ヒント文は、存在しない索引を指定するなどしても構文エラーにはなりませんので注意して下さい）。
このように運用中にパフォーマンスが急に劣化しては困るので、実行計画を頻繁に変えたくないということをよく聞きます。最後にそれについて説明します。
■４．実行計画を変えたくない
CBOなのでオプティマイザ統計によって実行計画が変わる可能性はあります。どうしても変えたくないのであれば、次のことを行って変更しないようにする方法もあります。
■４．実行計画を変えたくない
CBOなのでオプティマイザ統計によって実行計画が変わる可能性はあります。どうしても変えたくないのであれば、次のことを行って変更しないようにする方法もあります。
- ヒント文（SQL文を変更できないなどでヒント文を埋め込めない場合はプラン・スタビリティを使用する）
- プラン・スタビリティ（実行計画を実現するためのアウトライン「後で埋め込むためのヒント文の集まり」を作成して、実行時にSQL文にアウトラインを埋め込んでくれる）
テーブルのデータ分布などが変わって効率が悪くなる場合もありますので、データ分布の傾向が変わらない場合などで使用して下さい（例えば、列c1の索引アクセスが効率良かったが、データ分布が変わって効率が悪くなったなど）。そのため、データ分布が変わった場合は、現在の実行計画が問題ないか確認して下さい。
このような事もあるので、Oracle Database 11gであれば図のようなSQL実行計画管理（SPM:SQL Plan Management）を使用して下さい。実行計画の履歴管理を行って最適な実行計画だけを採用するようにしてくれます。
オラクルエンジニア通信：「【技術資料】SQL Plan Management 機能解説：Oracle Database 11g」
このような事もあるので、Oracle Database 11gであれば図のようなSQL実行計画管理（SPM:SQL Plan Management）を使用して下さい。実行計画の履歴管理を行って最適な実行計画だけを採用するようにしてくれます。
オラクルエンジニア通信：「【技術資料】SQL Plan Management 機能解説：Oracle Database 11g」
■５．おわりに
今回はオプティマイザ統計の収集についていろいろと説明しましたが、すべてのデータを正確に把握することは難しいことです。そのため、事前テストを十分に行って個別対応する必要があることを忘れないで下さい。
他にも疑問点がありましたらお問合わせ下さい。お待ちしております。
それでは、次回まで、ごきげんよう。
＜＜関連コラム・資料＞＞
オラクルエンジニア通信：「Oracleの統計情報にまつわる頻出FAQ～概要、確認、収集・取得」
オラクルエンジニア通信：「【セミナー資料】オラクルコンサルタントが語る 統計情報管理の真髄 シリーズPart1」
オラクルエンジニア通信：「【セミナー資料】オラクルコンサルタントが語る 統計情報管理の真髄 シリーズPart2」
オラクルエンジニア通信：「【セミナー資料】オラクルコンサルタントが語る 統計情報管理の真髄 シリーズPart3」
オラクルエンジニア通信：「【セミナー資料】オラクルコンサルタントが語る 統計情報管理の真髄 シリーズPart4」
オラクルエンジニア通信：「【連載コラム】Oracle Database のソースコードを書いているエンジニアが語る "Oracle のオプティマイザ"」
今回はオプティマイザ統計の収集についていろいろと説明しましたが、すべてのデータを正確に把握することは難しいことです。そのため、事前テストを十分に行って個別対応する必要があることを忘れないで下さい。
他にも疑問点がありましたらお問合わせ下さい。お待ちしております。
それでは、次回まで、ごきげんよう。
＜＜関連コラム・資料＞＞
オラクルエンジニア通信：「Oracleの統計情報にまつわる頻出FAQ～概要、確認、収集・取得」
オラクルエンジニア通信：「【セミナー資料】オラクルコンサルタントが語る 統計情報管理の真髄 シリーズPart1」
オラクルエンジニア通信：「【セミナー資料】オラクルコンサルタントが語る 統計情報管理の真髄 シリーズPart2」
オラクルエンジニア通信：「【セミナー資料】オラクルコンサルタントが語る 統計情報管理の真髄 シリーズPart3」
オラクルエンジニア通信：「【セミナー資料】オラクルコンサルタントが語る 統計情報管理の真髄 シリーズPart4」
オラクルエンジニア通信：「【連載コラム】Oracle Database のソースコードを書いているエンジニアが語る "Oracle のオプティマイザ"」
長年に渡りデータベースの構築やパフォーマンスチューニングなどに従事し、最近では若手エンジニアの育成および大規模データベース案件などの支援に従事しております。今までの経験が少しでもお役に立てればと思い、この連載を始めることに致しました。できるだけ長く続けたいと思いますのでよろしくお願いいたします。
Oracle Databaseは、技術の進化により非常に扱いやすくなったと思います。私自身も昔のバージョンを使用したころに比べると非常に楽になったと感じています。いろいろと進化したとはいえパフォーマンス問題が発生しなくなった訳ではありません。今でも多くの担当者が色々と苦労していると思います。その中でスキルや機能を知らずに苦労している場合もあるように思いますので、ここで紹介していけたらと考えています。
この連載では、このようなOracle技術者（データベース技術者）の方へのアドバイスとして様々なパフォーマンス問題を題材に解説していこうと考えています。既にデータベース運用を行っている管理者、これから管理者を目指す方までを対象に、様々な疑問に対して少しでも何かの手助けになればと願っています。できるだけ読者の皆様からの疑問に答えていきたいと思っておりますので（問合せなどの具体例を使用して説明した方が分かりやすいと思いますので）、パフォーマンス問題に関する様々な質問をお願いいたします。
津島博士の記事についてのご質問はこちらまでお願いいたします。