徹底解説！Oracle Database 12cのすべて Vol.5

前回はOracle Database 12cのRACとASMにおける変更点を紹介しました。本シリーズ最終回となる今回は、SQLの実行において重要な役割を担うオプティマイザについて紹介します。

これまでのリリースでもバージョンアップする度に様々な新機能が追加されてきたオプティマイザですが、Oracle Database 12c（以下、12c）ではより最適な実行計画が選択されるように「適応問合せの最適化」、「SQL計画管理（SQL Plan Management）」、「バルクロードのオンライン統計収集」などの新機能が追加されています。今回は12cにバージョンアップした際の大きな変更点となる、「適応問合せの最適化」について紹介します。

「適応問合せの最適化」とは、より優れた実行計画になるための追加情報を検出する機能の総称です。役割に応じて以下のように細かく分類されていますが、わかりやすく言えば「今まで最適な実行計画が選ばれなかったSQLでも、12cにするとより良い実行計画が選ばれやすくなる」という機能です。

Oracle Databaseでは、SQLを実行するとデータ・ディクショナリに格納されているオプティマイザ統計をもとに最も効率的な実行計画を選択し、実行フェーズに移ります。この時データ・ディクショナリに格納した統計が不正確であったり、複雑な演算子や条件述語が存在する状況では、非効率な実行計画が選択されることがあります。

Oracle DatabaseではSQLの解析負荷を軽減するために、事前に収集した統計をもとに実行計画を立てる動作になっていますが、あくまで統計取得時点の情報であるため、現実とのギャップが生じてしまうことも少なくありません。そのため、管理者が適切な取得タイミングや頻度、レベルで統計情報を収集するといった作業が求められます。

12cから新しく実装された適応計画では、SQL実行中に検索対象となる行数などの追加情報を収集し、見積った結果と大きく異なる場合、追加情報をもとに最終的な実行計画を選択してくれるようになりました。

適応計画の基本動作

1. オプティマイザ統計をもとにデフォルト・プランを作成
2. SQL実行中に統計コレクタが追加の情報を収集し、閾値を上回るか否かをチェック
3. 収集した情報によって最適なサブ・プランを選択し、最終プランを決定

簡単なSQLで適応計画の動作を確認してみましょう。今回はLOG表（顧客のログ情報 200万行）とCUST表（顧客情報 8万行）の2つを結合し、CUST表から会社番号が42299の情報だけを検索します。

まず、オプティマイザ統計をもとに見積ったデフォルト・プランを確認してみます。Rows列を見るとCUST表で該当するデータは19行、LOG表で該当するのは131行、結合した結果は2497行と少量のデータを結合するため、索引を使ったネステッド・ループ結合が最適だと見積っています。ここでポイントとなるのは、この結果はあくまでオプティマイザ統計をもとにして見積った計画であり、現在の表の状況をもとにした計画ではないという点です。

次に、実行時に実際に選択された最終プランを確認してみます。まず注目すべきはId4のSTATISTICS COLLECTOR（統計コレクタ）です。CUST表への全表スキャンで得られた実行数などを、計画の一部として挿入しています。本例のようにE-Rows（見積もりの行数）とA-Rows（実際の行数）が大きく乖離しているような状況では、統計コレクタが効果を発揮します。統計コレクタによる追加情報の挿入によって、当初ネステッド・ループ結合が最適だと見積っていた計画を変更し、最終的にはハッシュ結合に切り替えて実行しています。

Idにハイフン（-）が付加されたものは実際には使用されなかったサブ・プランを表しており、ネステッド・ループ結合からハッシュ結合へと変更されたのが判別できるようになっています。

このように、適応計画はオプティマイザが見積った情報と実際の情報が乖離している場合に効果を発揮します。Oracle Database 11g以前では最適な実行計画が選択されなかった状況でも、12cではより良い実行計画を検出し、SQL実行時のパフォーマンス低下を回避することができます。

ただし、適応計画では結合方法の最適化やパラレル実行時の分散方法を最適化できるものの、既に選択した表の追加情報を収集した後に次のサブ・プランを検討するため、結合順序やパラレル度の最適化はできません。このような場合には、次にご紹介する自動再最適化が効果的です。

自動再最適化はハード解析時に収集した情報をカーソルに記録し、次回実行時に記録した情報をもとに実行計画を作成してくれる12cの新機能です。

・初回実行時には最適化せず次回以降の実行において最適化する
・適応計画では最適化できなかった結合順序などを最適化する

という点が適応計画との違いです。

自動再最適化は「統計フィードバック」と「パフォーマンス・フィードバック」という2つの機能で成り立っています。

統計フィードバックは、11gR2でオプティマイザ・フィードバックやカーディナリティ・フィードバックという名前で呼ばれていた機能です。この機能はカーディナリティ、つまり問合せによって返される予測行数と、実際の行数が異なる場合に次回実行時に実測値をフィードバックしてくれる機能です。これにより結合方法の最適化だけでなく、結合順序も最適化できます。一方、パフォーマンス・フィードバックは12cで新たに追加された機能で、実行時にパフォーマンスに関する情報（例えばCPU時間など）を収集し、次回実行時にフィードバックしてくれる機能です。これによりパラレル度を最適化できるというメリットがあります。

実際に自動再最適化の動作例を確認してみます。ここではLOG表（顧客のログ情報 200万行）とWORKER表（ログの登録者情報 2,000行）、AREA表（地域情報300行）の3つを結合し、AREA表から地域がTOKYOで、顧客番号2000～10000を検索するといったシンプルなSQLを使用します。

まず、1回目に選択された実行計画を確認してみます。ここではWORKER表→AREA表→LOG表の順に結合されており、AREA表とWORKER表ではデカルト結合が選択されていることがわかります。また、E-Rowsを見るとわずか1,800行の少量データの結合結果になると見積られていますが、A-Rowsから実際には47万行もの結合結果が返ってきてしまうことがわかります。デカルト結合は少量データの結合には向いていますが、大量データの結合においては不向きなため、非効率な実行計画です。

1回目に実行されたSQLのカーソルを確認すると、IS_REOPTIMIZABLE列がYになっています。これは先述の統計フィードバックにより、実際のカーディナリティが見積りと乖離しており、次回実行時に自動再最適化の対象になることを表しています。

続いて2回目の実行に移ります。通常であれば既にハード解析によってカーソルがキャッシュされているため、ソフト解析の対象となり初回と同じ実行計画が選択されるはずですが、自動再最適化が行われた場合は同じSQL_IDに対し2つ目のカーソルが生成されます。child numberが1となっていることからも、別のカーソルとしてハード解析されていることがわかります。

実行計画を見るとWORKER表→LOG表→AREA表の順に結合されており、デカルト結合からハッシュ結合に変更されています。さらに初回実行時は乖離していたE-RowsとA-Arowsが正確に一致していることもわかります。これは初回実行で得られたカーディナリティの情報をカーソルに記録し、次回実行時にその情報をもとにハード解析することで最適な実行計画が選択できたことを示しています。このように自動再最適化によって結合方法のみではなく、適応計画では対応できなかった結合順序も最適化することができます。

システムによっては定期的にオプティマイザ統計を収集しない、あるいは統計をロックするなどしてオプティマイザによる計画変動を制御している場合もありますが、先述の2つの機能はSQLの実行計画を自動的に最適化します。そのため、12cで実行計画を固定化したい場合は注意する必要があります。

適応計画と自動再最適化は、初期化パラメータ OPTIMIZER_ADAPTIVE_REPORTING_ONLYを有効、無効に切り替えることで制御できます。このパラメータを有効（TRUE）にした場合、本機能による実行計画の変更をレポートするだけのモードとなり、実際に計画の変更は行われません。一方、無効（FALSE）にした場合は先述の通り計画の変更が行われる動作となります。デフォルトはFALSEであり、適応計画と自動再最適化が行われます。「TRUEにするとレポート・モードになる」というパラメータであるため設定の際は注意してください。このパラメータはシステム・レベルだけでなくセッション・レベルで制御できるため、特定のセッションで動作させたくない場合にも有効です。

先ほどのSQLでテストしてみます。自動再最適化が動作すれば、2回目はデカルト結合からハッシュ結合に切り替わるはずですが、今回はレポート・モードであるため初回の実行計画が維持されていることがわかります。

あわせてカーソルを確認すると、先ほどは2つのカーソルが作られていたのに対し、今回はカーソルが1つしか存在しません。また、自動再最適化の動作を表すIS_REOPTIMIZABLE列がRになっていることがわかります。これはレポート・モードに設定したことで、自動再最適化が実行されなかったことを表します。

パラメータをTRUEにした場合、計画の変更はされないものの自動再最適化に必要な情報は収集しているため、FALSEにした場合にどのような変更が行われるかを確認することができます。そのため、変更内容を確認した上で実装するか否かを判断することができます。

オプティマイザに関する機能はバージョンアップの度に自動化が強化されており、管理者が細かいことを意識せずとも実行計画が最適化されるように進歩を続けています。12cの機能追加もその流れの1つと言えますが、本機能はあくまで非効率な実行計画が選択される状況において最適化を図り、パフォーマンス劣化を回避するための機能であり、すべてにおいて最良な計画を選択するわけではありません。そのため、既存の機能を補完するものと捉え、オプティマイザの管理、運用においてはこれまでどおり行うことをお勧めします。

・徹底解説！Oracle Database 12cのすべて Vol.1
・徹底解説！Oracle Database 12cのすべて Vol.2
・徹底解説！Oracle Database 12cのすべて Vol.3
・徹底解説！Oracle Database 12cのすべて Vol.4

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