皆さん、明けましておめでとうございます。今年もよろしくお願いします。昨年の最終回は、風邪で体調を崩してスキップしてしまい申し訳ありませんでした。皆さんも体調には気を付けてください。
今年最初の内容は、第27回の続きとして、Oracle Database 12c（Oracle12c）も含めてオプティマイザ統計の運用について説明しようと思います。最近、Oracle12cのオプティマイザ関連について説明する機会があり、それを含めて説明することにしましたので、参考にしてください。
1. オプティマイザの問題点
まずは、現状のオプティマイザの問題点について説明しましょう。
オプティマイザ（CBO）のコンパイル(実行計画の作成)時に入力する統計には、以下の情報（ディレクトリ上に作成するオプティマイザ統計、メモリ上に作成する動的サンプリングとカーディナリティ）があり、このような情報を使用して正確な実行計画を作成するようにします。また、SQLチューニング・アドバイザでSQLプロファイルを作成して補正することもできますが、これは別の機会に説明します。
CBOは、統計を利用して様々なことを自動的に行います。代表的なのが第29回で説明したSQL自動変換（正しくはCost-Based Query Transformationと言います）になります。これを利用することでSQL文の作成が簡単になるので、CBOに任せるのが好ましいのですが、オプティマイザ統計の問題などで常に最適な実行計画になるとは限りません（ヒント文などで実行計画を調整できますが、簡単ではありません）。そこで、Oracle12cを説明する前に、Oracle Database 11g（Oracle11g）までのオプティマイザの問題点として、オプティマイザ統計の収集と補正についてまとめてみました。
（１）オプティマイザ統計の収集
オプティマイザ統計（統計）の収集の問題から考えてみます。
統計は、できるだけ正確にする必要があるのは分かるのですが、収集する時間が長くなり運用的に難しくなるので、収集するデータを決めることが簡単ではありません。第5回で説明した自動オプティマイザ統計収集（自動統計収集）がOracle11gで拡張されたことで、ある程度は収集するデータを自動的に決定するので、収集するのが非常に楽になってきています。ただし、第27回で説明した以下のような自動統計収集されない統計や、ヒストグラムなどの統計情報の限界で、最適な実行計画にならない場合もあり、これをどうするかが課題です。
・拡張統計
列グループ統計などは組合せパターンが多いので、どれを収集するかを決めるのが課題になります。第32回で説明した実行計画の見積もり行数（E-Rows）と実行時行数（A-Rows）などを確認して、決める必要があります。
・固定オブジェクトの統計
頻繁に収集する必要がないので、あまり気にする必要はありませんが、収集しなくて良い訳ではありません。
・システム統計
より正確な実行計画の作成が可能になりますが、収集する状況によって値が異なるので、収集するタイミングに注意が必要です。
・ヒストグラム
一意な値の数が多くデータに偏りがあるデータは、正しく表すことができないので注意が必要です。
（２）オプティマイザ統計の補正
統計が正しくないと最適な実行計画にならないので、ディレクトリ上の統計のある程度の不備を補正する機能として、第27回で説明した動的サンプリングとカーディナリティ・フィードバックがあります。ただし、他のSQLで利用できない、DBMS_STATSパッケージの収集時に反映されないなどの完全に補正できないのが課題です。
・動的サンプリング
Oracle Database 10gからのデフォルト（2）では、統計が欠落または失効時に統計の収集を行いますが、3以上を設定した場合には、WHERE句の条件（3は式、4以上は式と複数列）によって、統計が存在しても収集を行うので、統計の補正ではなく新たに収集することになります。また、これを統計として格納していないので、ハード・パースするたびに行う必要があります。そのため、ハード・パース時間が多少長くなり使用できるSQLが限られる、他のSQLでも利用できないなどの問題があります。Oracle11gR2からのパラレル実行では、デフォルトで表のデータ量やWHERE句の条件によってレベルを自動設定するので、統計が存在しても動作する場合があります。
・カーディナリティ・フィードバック
実行時のカーディナリティ（戻される行数）が見積もりと大きく異なると、そのカーディナリティを次回以降で利用するよう共有プール上に格納して、最適な実行計画を作成できるようにします。ただし、メモリ上に共有カーソルが存在するときしか利用できない、他のSQLでも利用できない、統計に反映できないなどの問題があります。
実行計画の固定化について
これについて聞かれる場合があるので、ここで私の考えを少しまとめてみました。
システムによって固定化が向いているものと、CBOに任せた方が最適なものがあるので、よく検討して使い分けるのが良いと思います（例えば、データに偏りがあるデータへのアクセスはCBOに任せた方が効果的です）。そして、固定化する場合でも最適な実行計画か、定期的に確認することも忘れないでください。最近のような大規模データでは、永久的に実行計画を固定化するという考えは無理だと思います。何度も言っているように、データが変化すれば最適な実行計画も変わるので、問題が発生してから改善するのではなく、発生しないようにする方が良いということです。
固定化を第5回で説明したプラン・スタビリティなどを使用することで、簡単に行えると思う方もいるかもしれませんので、間違わないようにこれも少し説明します。プラン・スタビリティは、CBOが作成した実行計画に対してアウトライン（後で埋め込むためのヒント文の集まり）を作成することで、次回以降もその実行計画にする機能です。そのため、統計が不十分な場合には、最適でない実行計画を作成するアウトラインになるので、統計を正確にする必要があり、それが簡単ではない場合があります。また、統計の再収集をしないで固定化するという考え方もありますが、これも同じように定期的に最適なのかを確認する必要があるのと、統計を変えなくても以下のように、実行計画が変化する場合もあることを忘れないようにしてください。
・カーディナリティ・フィードバック（正確でないカーディナリティを補正する）
・バインド変数の先読み又は優れたカーソル共有（バインド変数の値によってカーディナリティが変化する）
・動的サンプリング（パラレル実行のときデータ量やWHERE句の条件によってレベルを自動設定する）
2. Oracle Database 12cの新機能
次に、統計やその収集について改善されたOracle12cの機能について説明しましょう。 Oracle12cでは、最適な実行計画を作成するように様々な機能が拡張され、より自動的に最適化されるようになります。正確でない統計に対する最適な実行計画を作成する機能（以下の適応計画とSQL計画ディレクティブがOracle12cから追加された機能です。その他にも機能拡張されて名前が変更になった自動再最適化や動的統計があります）、統計の精度向上や収集時間短縮のための機能（ヒストグラムの拡張、バルク・ロードのオンライン統計収集など）が追加されています。これでCBOをより効果的に使用することが可能になります。
（１）オプティマイザ統計が不十分
Oracle12cでは、今まで説明してきた問題点である統計が不十分でも、以下のようにOracleデータベース自身で自動的に補完（改善）するように機能が追加されています（初回実行でも部分的に改善、他のSQLにも利用できるように記録、統計が不足していると動的統計で補完、次回の統計収集に追加などが行われます）。これは、初期化パラメータOPTIMIZER_ADAPTIVE_FEATURESが’TRUE’のときに動作します（デフォルトは’TRUE’です）。
・適応計画（初回実行時の最適化）
最初のSQL実行時に、2番目以降の操作（結合方法、第20回で説明したパラレル分散処理方法）の候補をサブプランとして作成します。そして、その操作以前まで実行したときの統計を使用して、最適なサブプランから最終プランを決定します。
・自動再最適化（2回目以降のカーソルが存在するときの最適化）
実行時の統計（カーディナリティなど）が見積もりと大きく異なると、実行時統計を次回以降で利用するようメモリ上に格納して、最適な実行計画を作成できるようにします（他のSQLでも改善されるようにSQL計画ディレクティブを作成して、ヒストグラムや拡張統計の欠落などを登録します）。ただし、共有カーソルがメモリ上に存在するときだけになります。これには、統計フィードバック（以前のカーディナリティ・フィードバック）とパフォーマンス・フィードバック（自動パラレル度の最適化）があります。
・SQL計画ディレクティブによる動的統計（ディレクティブが存在するときの最適化）
SQLの再実行で自動再最適化されない（共有カーソルがメモリ上に存在しない）場合に、SQL計画ディレクティブによる動的統計（以前の動的サンプリング）を行い実行計画を作成します。これは、同じSQLでなくても使用可能なSQL計画ディレクティブが存在すると（WHERE句の列が一緒などのSQLでも）行われます。
（２）オプティマイザ統計の収集
統計の収集についてもいろいろと機能強化がされていますが、ここではバルク・ロードのオンライン統計収集について説明します（その他は別の機会に説明します）。
大量にデータ更新したときの統計は、実際のデータと大幅に異なっている可能性があるので、一般的には手動で収集を行う必要があります。ただし、データ更新だけでも時間が掛る処理などは、統計収集に時間を確保するのは難しい場合があります。そこで、Oracle12cからバルク・ロード（CREATE TABLE AS SELECT、ダイレクト・パス・インサートのINSERT INTO SELECT）時にも統計の収集を行います。デフォルトでは、バルク・ロード時に統計が収集されますが、以下の場合などでは統計が収集されないので注意してください。
・Oracle所有のスキーマ（SYSなど）内に存在するテーブル
・INSERT文のINTO句でパーティション指定できない増分統計指定のパーティション表
明示的に統計を収集しないようにするには、以下のようにNO_GATHER_OPTIMIZER_STATISTICSヒントを指定します。
その他に、索引統計とヒストグラムは収集されないなどの制限があるので、グローバル統計の収集も含めて少し注意が必要です。これをまとめて説明した方が分かりやすいと思うので、以下に第10回で説明した「パーティション索引のメンテナンス」例を使用して、Oracle12cのバルク・ロードとメンテナンスの効果的な手順例を示します（赤字の部分がOracle12cからです）。これでパーティション表にバルク・ロード、索引メンテナンス、統計収集を短時間で行うことが可能になります。
SQL> EXEC dbms_stats.set_table_prefs(NULL,’tab1w’,’INCREMENTAL’,’true’); …②
SQL> EXEC dbms_stats.set_table_prefs(NULL,’tab1w’,’INCREMENTAL_LEVEL’,’table’); …②
SQL> INSERT /*+ APPEND */ INTO tab1w SELECT * FROM ext_tab1; …③
SQL> CREAT UNIQUE INDEX tab1w_pk ON tab1w (c1) NOLOGGING PARALLEL xx; …④
SQL> ALTER TABLE tab1w ADD CONSTRAINT tab1w_pk PRIMARY KEY (c1)
2 USING INDEX tab1w_pk [ENABLE NOVALIDATE]; …⑤
SQL> ALTER TABLE tab1 ADD PARTITION p999; …⑥
SQL> ALTER TABLE tab1 EXCHANGE PARTITION p999 WITH TABLE tab1w [INCLUDING INDEXES]; …⑦
SQL> EXEC dbms_stats.gather_table_stats(NULL,’tab1′,GRANULARITY=>’auto’); …⑧
SQL> ALTER TABLE tab1 DROP PARTITION p001; …⑨
SQL> DROP TABLE tab1w; …⑨
①ロードするテーブルを作成する（主キーの列にNOT NULL制約を付加する）。
②ロードするテーブルに増分統計のシノプシスを収集可能にする。
③①のテーブルに外部表からダイレクト・パス・インサートする。
④主キーの列に一意索引を（パラレルで）作成する（索引統計も収集される）。必要により他の索引も作成する。
⑤主キーを付加します。
⑥パーティションを追加する。
⑦パーティション表のパーティションとロードしたテーブルをEXCHANGE PARTITION（シノプシスも交換）する（主キー
以外の索引も交換する場合はINCLUDING INDEXESを指定する）。
⑧パーティション統計とシノプシスを使用してグローバル統計を作成する（11gR2まではパーティション統計を収集し
てシノプシスを作成する）。
⑨一番古いパーティションとロードに使用したテーブルを削除する。
Oracle12cから増分統計を行うときに、統計プリファレンス・パラメータINCREMENTAL_LEVELを’TABLE’に指定すると（デフォルトは’PARTITION’です）、Oracle11gではできなかった非パーティション表で増分統計のシノプシスが収集可能になります（上記②のSQLです）。これでEXCHANGE PARTITIONでシノプシスも交換することが可能になるので、パーティション統計を収集せずに、交換したパーティション統計とシノプシスの集計だけで、グローバル統計の作成が可能になります（上記⑧のSQLです）。つまり、交換前の統計がそのまま利用できるので、バルク・ロード時に作成された統計が効果的になります。
ヒストグラムが不要な場合はこの手順で良いのです が、 必要な場合にはバルク・ロード後に（上記⑦の前に）収集する必要があるので、そのためにテーブルをスキャンすることになります。 そのため、ヒストグラムが必要な場合には、オンライン統計収集を使用しない方が効果的といえます。その場合でもEXCHANGE PARTITIONでシノプシスの交換が可能になったことで、Oracle11gより効果的に運用できるようになります。
グローバル統計と増分統計について
ご存知ない方のために（説明していなかったので）、ここでグローバル統計と増分統計（インクリメンタル統計）について簡単に説明します。
パーティション表には、パーティション・レベルの統計（パーティション統計）とグローバル統計（表や索引レベルの統計）があり、パーティション・プルーニングされるとパーティション統計が使用されますが、パーティション・プルーニングされない場合には、グローバル統計が使用されるようになります。グローバル統計には、パーティション統計を集計することでは求められない、NUM_DISTINCT（一意な値の数）などがあるので、テーブル全体をフル・スキャンする必要があります（幾つかのパーティションに同じ値がある場合に、それを同じ値として集計することができないからです）。そのため、パーティションの多い大規模なテーブルでは、これを収集するのが問題になります。
Oracle11gからパーティション表に増分統計が提供されたことで、変更されたパーティションのみをスキャンすることによって、グローバル統計が収集可能になります。これは、パーティション統計の収集時に、シノプシス（NUM_DISTINCTなどを求めるための情報）を作成することで、パーティション統計とシノプシスを集計するだけで、グローバル統計の作成が可能になるからです。以下のように、パーティション表の統計プリファレンス’INCREMENTAL’と’PUBLISH’の値を’TRUE’に設定して、統計を収集するときにGRANULARITYパラメータに’AUTO’、ESTIMATE_PERCENTパラメータにDBMS_STATS.AUTO_SAMPLE_SIZEを指定することで動作します（赤字の部分はデフォルト値なので、指定しなくても問題ありません）。
SQL> EXEC dbms_stats.set_table_prefs(NULL,’tab1′,’PUBLISH’,’true’);
SQL> EXEC dbms_stats.gather_table_stats(NULL,’tab1′,GRANULARITY=>’auto’,
2 ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE);
ただし、第10回の「パーティション索引のメンテナンス」で説明したEXCHANGE PARTITIONでは、シノプシスを交換することができません（非パーティション表には、交換するシノプシスが存在しないからです）。そのため、グローバル統計を更新するには、交換後にパーティション統計を収集して、シノプシスを作成する必要があるので、交換する前に統計を収集しても意味がありませんでした。
3. おわりに
今回はOracle12cも含めたオプティマイザ統計の運用について説明しましたが、少しは参考になりましたでしょうか。また機会があれば他のことについても説明したいと思います。これからもよろしくお願いします。
それでは、次回まで、ごきげんよう。