SQLを自動最適化する12cのオプティマイザ

12cでは、より最適な実行計画が選ばれやすくなる

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

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

12c新機能 適応問合せの最適化

統計情報と現実のギャップを埋める、適応計画

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

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

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

適応計画の基本動作

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

適応計画の基本動作 

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

サンプルSQL
SELECT log.custno,logno,tel1
FROM log,cust
WHERE log.custno = cust.custno
AND cust.compno = 42299;

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

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

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

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

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