移行前に知っておきたい、Oracle Database 12c新機能のオモテとウラ Vol.6

前回はOracle Database 12c（以下、12c）のセキュリティ新機能について解説しました。今回は、移行後すぐに使える便利なオプティマイザ関連の新機能を紹介します。

12cが国内で出荷されてからおよそ1年が経ち、新規システムでの採用や既存システムのバージョンアップを本格的に検討しているという声を多く耳にするようになりました。Oracle Multitenantをはじめとする新機能や、Oracle Open World 2013で発表されたインメモリの機能には、非常に高い期待が寄せられています。

その一方で、バージョンアップの話題になると「オプティマイザの動作に変更はありましたか？」というご質問をいただくことが多々あります。これは期待というよりも、むしろ不安の方が大きいようです。Oracle Database 10gR1でルール・ベース・オプティマイザがサポートされなくなって以来、コスト・ベース・オプティマイザと賢く付き合うことが性能安定の必須条件になりましたが、実行計画が意図しない方向に変わらないか心配だという声が多く、これがバージョンアップを躊躇する理由の1つとなっています。

どうしてもマイナス面の影響を心配してしまうコスト・ベース・オプティマイザですが、本来はデータの偏りや量に基づいて最適な実行計画を選択するために実装された機能なので、バージョンが上がれば当然その精度も上がるはずです。通常はデータベースのバージョンアップとハードウェアの更改を同時に行うことが多いため、SQLのレスポンスが良くなってもオプティマイザが注目されることは少ないのですが、見えないところで確実に進化を続けています。

以下の表は、12cにおけるオプティマイザ関連の新機能と、非推奨の機能をまとめたものです。新機能には、以前「連載：徹底解説！Oracle Database 12cのすべて」の中で解説した適応計画や自動再最適化をはじめ、より最適な実行計画が選択されやすくなる機能が多く実装されています。非推奨となった機能に注意しつつ、これらの新機能を活用していけば、以前のバージョンよりも安定した実行計画でデータベースを運用できるはずです。

（※1）マニュアルによって「適合計画」や「適用計画」と表記されている場合もありますが、本稿では『SQLチューニング・ガイド』に従って「適応計画」とします。

（※2）ただし、ソフト解析時に共有プール内で類似文を検索するというオーバーヘッドが生じるので、使用にあたっては注意が必要です。

今回は、動的統計の拡張と新しいヒストグラムに着目し、12c以前のバージョンとの違いについて説明していきます。

動的サンプリング（Dynamic Sampling）はOracle 9i Databaseから実装されている機能で、コスト・ベース・オプティマイザがSQLを解析する際に動的に統計情報を収集するというものです。作成されたばかりの表や、長期間統計情報が収集されていない表でも適切な実行計画を選択できるようになりますが、初期化パラメータのoptimizer_dynamic_samplingを0から10のどれにするかによって動作が変わるため、適切な設定を判断しにくいという課題がありました。そのため、0（無効）またはデフォルト値である2に設定されているケースがほとんどで、有効活用されているとは言いにくい機能でした。

12cでは、動的サンプリングから動的統計（Dynamic　Statistics）という機能名に変更され、さらにoptimizer_dynamic_samplingに11という新しい値が追加されました（若干ややこしいのですが、初期化パラメータ名はoptimizer_dynamic_"sampling”のままです。statisticsではありません）。

optimizer_dynamic_samplingを11に設定すると、オプティマイザが必要だと判断した場合に自動的に統計が収集されるようになります。いわゆるお任せ設定で、サンプリングのブロック数を含めてすべて自動的に判断されます。これにより、optimizer_dynamic_samplingを0から10のどれにするか悩むのではなく、11に設定してすべてオプティマイザに任せるという新しい選択肢が増えました。この機能はOracle Database 11gR2（以下、11gR2）のターミナル・リリースである11.2.0.4にも搭載されているため、12cへのバージョンアップ前に使用することもできます。

それでは、簡単な検証でoptimizer_dynamic_samplingを11に設定した時の効果を確認してみましょう。今回は100,000行ある社員表の中から、名前が「A」で始まる社員の情報だけを検索するというSELECT文を実行します。その際、optimizer_dynamic_samplingを１１に設定して動的統計を発生させ、オプティマイザが正しい行数を見積もれるのかを確認してみます。なお、名前が「A」で始まる社員の情報は100,000行のうち2,000行あり、それらのデータは特定のデータ・ブロックに集中しないよう分散されているものとします。

まずは、12cのデフォルトであるoptimizer_dynamic_sampling=2の結果から確認します。2に設定した場合はサンプリングのブロック数が64と少ないため、今回のように行数の多い表にはあまり向いていません。名前が「A」ではじまる社員の情報は2,000行ありますが、オプティマイザによる見積り結果は808行となっており、かなりの開きがあります。

続いて、新しく設定できるようになったoptimizer_dynamic_sampling=11の結果を確認します。11に設定すると、動的統計の動作がオプティマイザによって自動的に制御されるため、2に設定した時よりも柔軟に対応できるはずです。今回の検証では見積り結果が1,946行となっており、optimizer_dynamic_sampling=2の場合よりも正確な値が出ることを確認できました。

適切なタイミングで統計情報を収集しているデータベースでは出番のない機能かもしれませんが、何らかの理由で統計情報と実際のデータに大きな差異が生じてしまった場合には、最適な実行計画を得るための一助となってくれるはずです。ただし、オプティマイザのトレースを取得しても詳細な情報が出力されないなど、ユーザから見えにくい部分も多少あります。自動化とのトレードオフとして許容できるかどうか見極めたうえで使用することをお勧めします。

ヒストグラムは、列データの分布状況を表現したもので、データにどの程度偏りがあるのかをオプティマイザに伝えるという役割を果たします。例えば以下のような表がある場合、ヒストグラムがなければオプティマイザはデータが均等に分布しているものと判断するため、最適な実行計画を選択できないことがあります。

11gR2の時点で使用できるヒストグラムには、「頻度ヒストグラム」「高さ調整済ヒストグラム」の2種類があります。頻度ヒストグラムは、列の個別値がヒストグラム収集時に指定したバケット数以下の場合に生成され、各バケットにそれぞれ個別値を格納します。何だか難しく聞こえるかもしれませが、以下のようにバケット（=バケツ）の中に値を1つひとつ入れるだけというシンプルな方法です。頻度ヒストグラムでは個別値の分布をほぼ正確に把握できますが、バケット数が最大254までという制限があるため、個別値が多い列には使用できません。

高さ調整済ヒストグラムは、列の個別値がヒストグラム収集時に指定したバケット数より多い場合に作成され、各バケットにあらかじめソートした値を同数ずつ格納します。頻度ヒストグラムのようにそれぞれの個別値が何行あるのかまでは分かりませんが、以下のようにバケットに格納された最後の値を記録していくことで、おおよその分布を把握することができます。

12cでは、先ほど説明した2つに加え、新しく「上位頻度ヒストグラム」と「ハイブリッド・ヒストグラム」が追加されました。上位頻度ヒストグラムは、その名のとおり頻度ヒストグラムから派生したもので、個別値の中でも特に割合の多いものだけを対象としたヒストグラムです。

11gまでは列の個別値がヒストグラム収集時に指定したバケット数より多い場合に、高さ調整済ヒストグラムが作成されていましたが、12cでは上位n個の個別値がデータの大半を占めているような場合には、上位頻度ヒストグラムが作成されます。これにより、上位n個についてはオプティマイザが正確に行数を見積ることができます。

もう1つの新機能であるハイブリッド・ヒストグラムは、高さ調整済ヒストグラムと頻度ヒストグラムを組み合わせたものです。まず、高さ調整済ヒストグラムのように各バケットに同数ずつ値を格納していき、そのあとで同じ値が複数のバケットに入らないよう調整を行います。そうしてできたバケットに格納されている最後の値が何行あるのかを記録していくことで、頻出する値に対して正確な見積りができるようになります。

上位頻度ヒストグラムとハイブリッド・ヒストグラムを加えると、12cでは全部で4つのヒストグラムが存在することになりますが、このうちどれが作成されるか以下のフローに従って決まります。高さ調整済ヒストグラムは「レガシー」という位置づけになり、11gからアップグレードしたデータベースである場合や、estimate_percentをデフォルト値以外に指定した場合を除いて作成されなくなっています。11gまで高さ調整済ヒストグラムが作成されていたケースにおいては、上位頻度ヒストグラムまたはハイブリッド・ヒストグラムが作成されるようになるので、これらの動作を抑えておくと、ヒストグラムによる効果がイメージしやすくなります。

12cで追加されたヒストグラムのうち、上位頻度ヒストグラムについては「頻度ヒストグラムを上位n件に絞ったもの」と考えれば分かりやすいのですが、一方のハイブリッド・ヒストグラムについては説明を読んでも動作がイメージできない方が多いと思います。

そこで今回は、高さ調整済ヒストグラムと比較しながらハイブリッド・ヒストグラムの動作とその効果について見ていきます。検証に使用するのは以下の表で、A列が1のデータが10件、2のデータが20件、3のデータが30件というように偏りを発生させています。

まずはじめに、高さ調整済ヒストグラムを作成します。バケット数には個別値よりも少ない40を指定し、estimate_percentには100を指定します。ヒストグラムを作成したら、USER_TAB_COL_STATISTICSビューやUSER_TAB_HISTOGRAMSビューから情報を確認します。

USER_TAB_HISTOGRAMSビューを確認すると、ENDPOINT_NUMBER（エンドポイント番号）列が0,1,2,3と1ずつ増えていますが、途中で20,22,23と値が飛んでいることが分かります。これは21と22のENDPOINT_VALUE（エンドポイント値）がともに37であったため、21が省略されていることを意味します。つまり、高さ調整済ヒストグラムではA列が37であるデータをポピュラー値として認識しているということです。オプティマイザによる見積り結果から、ヒストグラムの効果を確認してみましょう。

A列にポピュラー値である37を指定したケースでは、実際の行数である370行に対して見積り結果が638行となっており、若干多めの数値が出ています。一方で、非ポピュラー値である38が指定されると見積り結果が203行になっており、こちらは若干少なめです。高さ調整済ヒストグラムではおおよその分布しか把握できないため、このように見積り結果にばらつきが生じてしまいます。

同じ状況において、12cのハイブリッド・ヒストグラムがどのような動作をするのか確認してみましょう。

ハイブリッド・ヒストグラムでは、ENDPOINT_REPEAT_COUNT（エンドポイント繰り返しカウント）列にエンドポイント値がそのバケット内にいくつ存在するのかを記録します。先ほどの高さ調整済ヒストグラムで思うような見積り結果が出なかったエンドポイント値36や37を見てみると、ENDPOINT_REPEAT_COUNT列に実際の行数と同じ値が入っていることが分かります。オプティマイザはこれを見て行数を見積もれば良いので、正確な結果が出ます。

このように、高さ調整済ヒストグラムでは見積り結果にばらつきがあった検索条件でも、ハイブリッド・ヒストグラムであれば正確な見積りができる場合があります。今回はシンプルなケースでの効果をご紹介しましたが、11gからバージョンアップを行う際には、新しいヒストグラムによる効果に是非着目してください。

次回は12cで新しく追加されたバックアップ関連の機能についてご紹介します。

・移行前に知っておきたい、Oracle Database 12c新機能のオモテとウラ Vol.1
・移行前に知っておきたい、Oracle Database 12c新機能のオモテとウラ Vol.2
・移行前に知っておきたい、Oracle Database 12c新機能のオモテとウラ Vol.3
・移行前に知っておきたい、Oracle Database 12c新機能のオモテとウラ Vol.4
・移行前に知っておきたい、Oracle Database 12c新機能のオモテとウラ Vol.5
・移行前に知っておきたい、Oracle Database 12c新機能のオモテとウラ Vol.7

最後までご覧いただきありがとうございました。
本記事でご紹介した製品・サービスに関するコンテンツをご用意しています。また、この記事の他にも、IT技術情報に関する執筆記事を多数公開しておりますのでぜひご覧ください。