| Oracle® Database SQLチューニング・ガイド 12c リリース1 (12.1) B71277-09 |
|
 前 |
 次へ |
SQLチューニングには、オプティマイザに対する深い理解が必要です。
この章の内容は次のとおりです。
問合せオプティマイザ(単にオプティマイザと呼ばれます)は、SQL文が必要なデータにアクセスするための最も効率的な方法を判断する組込みデータベース・ソフトウェアです。
この項の内容は次のとおりです。
オプティマイザは、SQL文に最も適した実行計画の生成を試みます。
オプティマイザは、考えられるすべての計画の候補中で最もコストの低い計画を選択します。オプティマイザは使用可能な統計を使用してコストを計算します。指定された環境における特定の問合せでは、コスト計算はI/O、CPUおよび通信など問合せ実行の要因から構成されます。
たとえば、問合せで従業員に関する情報を要求する場合があります。オプティマイザ統計で従業員の80%がマネージャであると示されている場合、オプティマイザは、全表スキャンが最も効率的であると判断できます。ただし、統計でマネージャが少ないことが示されている場合、索引を読み取ってからROWIDによる表アクセスを行うほうが全表スキャンよりも効率的なことがあります。
データベースには、自由に使用できる多数の内部統計およびツールがあるため、オプティマイザは通常、文の実行の最適な方法を決める上でユーザーよりも優位な立場にあります。このため、すべてのSQL文でオプティマイザが使用されます。
問合せ最適化は、SQL文を実行するための最も効率的な方法を選択する全体的なプロセスです。SQLは非手続き型言語であるため、オプティマイザでマージ、再編成、処理の順序を自由に選択できます。
アクセスするデータに関して収集された統計に基づいて、各SQL文が最適化されます。オプティマイザは、複数のアクセス方法(全表スキャンや索引スキャンなど)および様々な結合方法(ネステッド・ループ結合やハッシュ結合、様々な結合順序および可能な変換など)を調べることで、SQL文の最適な計画を決定します。
指定された問合せおよび環境では、オプティマイザは関連する数値コストを可能な計画の各ステップに割り当てた後、これらの値を総合的に要因分解して計画の全体的なコスト見積りを生成します。代替計画のコストを計算した後、オプティマイザは最もコストの低い見積りの計画を選択します。このため、オプティマイザは、従来のルールベース・オプティマイザ(RBO)との対比で、コストベース・オプティマイザ(CBO)と呼ばれることがあります。
注意:
オプティマイザは、Oracle Databaseのあるバージョンとその次のバージョンで同じ決定を行うとはかぎりません。新しいバージョンのオプティマイザでは、より高度な情報を使用でき、かつより多くのオプティマイザ変換が可能なため、異なる決定が行われる場合があります。
関連項目:
コスト実行計画とは、SQL文の推奨実行方法を示したものです。
この計画により、SQL文を実行するためにOracle Databaseで使用するステップの組合せが示されます。各処理では、データベースからデータ行を物理的に検索するか、文を発行したユーザーのためにデータ行を準備します。
実行計画では、計画全体のコスト(行0に示されます)および個々の操作ごとのコストが表示されます。コストは、計画の比較で使用できるように実行計画のみが表示される内部的な単位です。そのため、コストの値をチューニングしたり変更したりすることはできません。
次の図では、オプティマイザは、入力SQL文の実行計画として2つの使用可能な計画を生成し、統計を使用してそれらのコストを見積り、コストを比較して、最もコストの低い計画を選択します。
オプティマイザへの入力は、SQL文の解析済の表現です。
元のSQL文内の各SELECTブロックは、内部的には問合せブロックによって表されます。問合せブロックは、トップレベルの文、副問合せまたはマージされていないビューのいずれでもかまいません。
例4-1 問合せブロック
次のSQL文は、2つの問合せブロックで構成されています。括弧内の副問合せは、内側の問合せブロックです。SQL文の残りの部分が外側の問合せブロックで、副問合せによって提供されたIDを持つ部門内の従業員の名前を取り出します。問合せの形式によって、問合せブロックの相互関係が判断されます。
SELECT first_name, last_name
FROM hr.employees
WHERE department_id
IN (SELECT department_id
FROM hr.departments
WHERE location_id = 1800);
関連項目:
SQL処理の概要は、『Oracle Database概要』を参照してください。
各問合せブロックについて、オプティマイザは、問合せサブプランを生成します。
データベースは、問合せブロックを下位から上位へ順番に最適化します。このため、データベースは最も内側の問合せブロックを最初に最適化してそのサブプランを生成し、そして問合せ全体を表す外側の問合せブロックを生成します。
問合せブロックに使用できる可能性のある計画の数は、FROM句にあるオブジェクトの数に比例します。この数は、オブジェクトの数によって指数関数的に上昇します。たとえば、5つの表の結合に使用できる可能性のある計画の数は、2つの表の結合に使用できる可能性のある計画の数より大幅に多くなります。
オプティマイザの1つの例えとして、オンライン旅行アドバイザがあります。
ある旅行者が自転車に乗ってA地点からB地点に移動するための最も効率的なルートを知りたいと思っています。問合せは、「A地点からB地点への最も効率的なルートが知りたい」、「A地点からC地点経由でB地点に行く最も効率的なルートが知りたい」などのようなディレクティブになります。旅行アドバイザは、速度や難易度などの要因に基づく内部アルゴリズムを使用して、最も効率的なルートを決定します。旅行者は、「できるだけ速く到着したい」、「できるだけ楽なルートで行きたい」などのディレクティブを使用することで、旅行アドバイザの決定に影響を与えることができます。
この例えでは、旅行アドバイザが作成する可能なルートが実行計画に該当します。内部的には、アドバイザは、全体ルートをいくつかのサブルート(サブプラン)に分け、各サブルートについて別々に効率性を判断することも可能です。たとえば、旅行アドバイザは、あるサブルートについては所要時間15分難易度中、別のサブルートについては所要時間22分難易度低などのように見積ることが考えられます。
アドバイザは、ユーザーが指定した目的と、道路や交通の状況に関する利用可能な統計情報に基づいて、最も効率的な(コストの低い)全体ルートを選択します。統計情報が正確であればあるほど、より適切なアドバイスを提供できます。たとえば、アドバイザが交通渋滞、通行止めおよび道路の状態の悪さについて頻繁に確認していない場合、推奨されたルートは、結局は非効率な(コストが高い)ものになることがあります。
オプティマイザには、3つのコンポーネント(トランスフォーマ、エスティメータおよびプラン・ジェネレータ)が含まれています。
次の図に、コンポーネントを示します。
問合せブロックのセットは、解析済の問合せを表します。解析済の問合せは、オプティマイザへの入力です。次の表にオプティマイザ操作を示します。
表4-1 オプティマイザ操作
| フェーズ | 操作 | 説明 | 詳細情報 |
|---|---|---|---|
| 1 | 問合せトランスフォーマ |
オプティマイザは、オプティマイザがより優れた実行計画を生成できるように、問合せの形式を変更することが有用かどうかを判断します。 |
問合せトランスフォーマ |
| 2 | エスティメータ |
オプティマイザは、データ・ディクショナリ内の統計に基づき、各計画のコストを見積ります。 |
「エスティメータ」 |
| 3 | プラン・ジェネレータ |
オプティマイザは各計画のコストを比較し、コストが最も低い計画(実行計画と呼ばれます)を選択して、行ソース・ジェネレータに渡します。 |
「プラン・ジェネレータ」 |
一部の文では、問合せトランスフォーマは、元のSQL文を、意味的に同等でよりコストの低いSQL文にリライトすることが有利かどうかを判断します。
実行可能な代替案がある場合、データベースでは、代替案のコストが別々に計算され、最もコストの低い代替案が選択されます。次の図は、ORを使用する入力問合せを、UNION ALLを使用する出力問合せにリライトする問合せトランスフォーマを示しています。
エスティメータは、指定された実行計画の全体コストを判断する、オプティマイザのコンポーネントです。
エスティメータは、3つの異なる計測を使用してコストを決定します。
問合せで選択される行セットの中の行の割合で、0は行がないことを表し、1はすべての行を表します。選択性は、問合せの述語(WHERE last_name LIKE 'A%'など)や述語の組合せに関連します。述語は、選択性の値が0に近付くほど選択性が高くなり、値が1に近付くほど選択性が低く(非選択性が高く)なります。
注意:
選択性は、実行計画では表示されない内部的な計算です。
カーディナリティは、実行計画で各操作によって戻される行数です。これは最適な計画の作成に不可欠であり、すべてのコスト機能に共通の入力です。エスティメータは、DBMS_STATSによって収集される表統計からカーディナリティを導出することもでき、述語(フィルタ、結合など)や、DISTINCTまたはGROUP BY操作などの影響を考慮した後で導出することもできます。実行計画のRows列には、予測カーディナリティが表示されます。
このメジャーは、作業単位または使用されるリソースを表します。問合せオプティマイザはディスクI/O、CPU使用量、メモリー使用量を作業単位として使用します。
次の図に示すように、統計が使用可能な場合、エスティメータは統計を使用してメジャーを計算します。統計によって、メジャーの正確さの度合いは改良されます。
例4-1で示されている問合せの場合、エスティメータは、選択性、予測カーディナリティ(合計10行が戻される)およびコストのメジャーを使用して、その合計コストを3と見積ります。
--------------------------------------------------------------------------------
| Id| Operation |Name |Rows|Bytes|Cost %CPU|Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10| 250| 3 (0)| 00:00:01|
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 10| 250| 3 (0)| 00:00:01|
|*3 | TABLE ACCESS FULL |DEPARTMENTS | 1| 7| 2 (0)| 00:00:01|
|*4 | INDEX RANGE SCAN |EMP_DEPARTMENT_IX| 10| | 0 (0)| 00:00:01|
| 5 | TABLE ACCESS BY INDEX ROWID|EMPLOYEES | 10| 180| 1 (0)| 00:00:01|
--------------------------------------------------------------------------------
選択性は行セットの一部の行を表します。
行セットは、実表、ビュー、または結合の結果のいずれでもかまいません。選択性は、last_name = 'Smith'などの問合せ述語、またはlast_name = 'Smith' AND job_id = 'SH_CLERK'などの述語の組合せに拘束されています。
注意:
選択性は、実行計画では表示されない内部的な計算です。
述語は、行セット内の特定の行数をフィルタします。したがって、述語の選択性は、どれだけの行数が述語テストを通過するかを示します。選択性には、0.0から1.0の範囲があります。選択性が0.0の場合、行セットから行は選択されません。一方、選択性が1.0の場合、すべての行が選択されます。値が0.0に近づくほど述語の選択性は高くなり、値が1.0に近づくほど選択性は低くなります。
オプティマイザは統計を使用できるかどうかに応じて、選択性を見積ります。
統計が使用不可
OPTIMIZER_DYNAMIC_SAMPLING初期化パラメータの値に応じて、オプティマイザは、動的統計と内部のデフォルト値のいずれかを使用します。述語のタイプに応じて、使用する内部デフォルト値が異なります。たとえば、等価述語(last_name = 'Smith')の内部デフォルト値は範囲述語(last_name > 'Smith')の内部デフォルト値より小さくなります。これは等価述語の方が少ない行数を戻すことが予測されるためです。
統計が使用可能
統計が使用可能な場合、エスティメータは統計を使用して選択性を推測します。150の個別の従業員の名字があると仮定します。等価述語last_name = 'Smith'の場合、選択性はlast_nameの個別値の数nの逆数です。この例では、問合せは150の個別値から1つの個別値を含む行を選択するため、選択性は.006になります。
last_name列にヒストグラムがある場合、エスティメータは個別値の数かわりにヒストグラムを使用します。ヒストグラムには列内の異なる値の分布が示されているので、特にデータの偏りがある列では、より適切な選択性の見積りが行われます。
関連項目:
カーディナリティは、実行計画で各操作によって戻される行数です。
たとえば、全表スキャンによって戻される行数に関するオプティマイザの見積りが100である場合、この操作のカーディナリティ予測は100です。カーディナリティ予測は、実行計画のRows列に表示されます。
オプティマイザは、入力として表レベルと列レベルの両方の統計または動的統計を使用する一連の複雑な計算式に基づいて各操作のカーディナリティを判定します。オプティマイザは、単一の等価述語が、ヒストグラムを持たない単一表問合せに存在する場合は、最も単純な計算式のいずれかを使用します。この場合、オプティマイザは、データ配分が均一であると想定し、表内の行の合計数を、WHERE句の述語で使用されている列内の個別値の数で割ることで、問合せのカーディナリティを計算します。
たとえば、ユーザーhrが次のようにemployees表を問い合せるとします。
SELECT first_name, last_name FROM employees WHERE salary='10200';
employees表には107の行があります。現在のデータベース統計では、salary列の個別値の数が58と示されています。したがって、オプティマイザは、107/58=1.84という計算式を使用して、結果セットのカーディナリティを2と見積ります。
カーディナリティの見積りは、実行計画のすべての側面に影響を及ぼすため、できるだけ正確である必要があります。オプティマイザが結合のコストを判断する際、カーディナリティが重要になります。たとえば、employeesおよびdepartments表のネステッド・ループ結合では、employees内の行数は、データベースでdepartments表をプローブする頻度を決定します。カーディナリティはまた、ソートのコストを計算する上でも重要です。
オプティマイザのコスト・モデルは、問合せで使用が予測されるマシン・リソースから構成されます。
コストは、計画のリソース使用量の見積りを表す内部の数値メジャーです。コストは、オプティマイザ環境での問合せに固有です。コストを見積もるために、オプティマイザでは次のような要因が考慮されます。
システム・リソース(推定されるI/O、CPU、メモリーなど)
返される推定行数(カーディナリティ)
初期データ・セットのサイズ
データの分散
アクセス構造
注意:
コストは、オプティマイザが同じ問合せに対する様々な計画を比較するために使用する内部尺度です。コストをチューニングしたり変更することはできません。
実行時間はコストの関数ですが、コストは直接に時間とは一致しません。たとえば、問合せAの計画のコストが問合せBの計画より低い場合、次の結果が考えられます。
AのほうがBより高速で実行される。
AのほうがBより低速で実行される。
AがBと同じ時間で実行される。
したがって、異なる問合せのコストを相互に比較することはできません。また、異なるオプティマイザ・モードを使用する意味的に同等の問合せのコストは比較できません。
例4-2 サンプル実行計画のコスト
実行計画では、計画全体のコスト(行0に示されます)および個々の操作ごとのコストが表示されます。たとえば、次の計画は全体コストが14であることを示しています。
EXPLAINED SQL STATEMENT:
------------------------
SELECT prod_category, AVG(amount_sold) FROM sales s, products p WHERE
p.prod_id = s.prod_id GROUP BY prod_category
Plan hash value: 4073170114
----------------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 (100)|
| 1 | HASH GROUP BY | | 14 (22)|
| 2 | HASH JOIN | | 13 (16)|
| 3 | VIEW | index$_join$_002 | 7 (15)|
| 4 | HASH JOIN | | |
| 5 | INDEX FAST FULL SCAN| PRODUCTS_PK | 4 (0)|
| 6 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 4 (0)|
| 7 | PARTITION RANGE ALL | | 5 (0)|
| 8 | TABLE ACCESS FULL | SALES | 5 (0)|
----------------------------------------------------------------------
アクセス・パスは、実表からデータを得るときに実行される作業単位数です。計画の全体コストを判断するために、オプティマイザは各アクセス・パスにコストを割り当てます。
表スキャンまたは高速全索引スキャン
表スキャンまたは高速全索引スキャンの間、データベースは単一I/Oでディスクから多数のブロックを読み取ります。スキャンのコストは、スキャンされるブロック数およびマルチブロックREADカウントに左右されます。
索引スキャン
索引スキャンのコストは、Bツリーにおけるレベル、つまりスキャンされる索引リーフ・ブロック数、索引キーのROWIDを使用してフェッチされる行数に左右されます。ROWIDを使用して行をフェッチするコストは、索引クラスタ化係数に依存します。
結合コストは、結合されている2つの行セットの個別のアクセス・コストの組合せと、結合操作のコストを表します。
プラン・ジェネレータは、別のアクセス・パス、結合方法および結合順序を試行することによって、問合せブロックに対する様々な計画を探索します。
データベースでは、様々な組合せを使用して、同じ結果をもたらすことができるため、様々な計画が可能になります。オプティマイザは、コストが最も低い計画を選択します。
次の図は、入力問合せの様々な計画をテストするオプティマイザを示しています。
オプティマイザのトレース・ファイルの次のスニペットは、オプティマイザが実行するいくつかの計算を示しています。
GENERAL PLANS *************************************** Considering cardinality-based initial join order. Permutations for Starting Table :0 Join order[1]: DEPARTMENTS[D]#0 EMPLOYEES[E]#1 *************** Now joining: EMPLOYEES[E]#1 *************** NL Join Outer table: Card: 27.00 Cost: 2.01 Resp: 2.01 Degree: 1 Bytes: 16 Access path analysis for EMPLOYEES . . . Best NL cost: 13.17 . . . SM Join SM cost: 6.08 resc: 6.08 resc_io: 4.00 resc_cpu: 2501688 resp: 6.08 resp_io: 4.00 resp_cpu: 2501688 . . . SM Join (with index on outer) Access Path: index (FullScan) . . . HA Join HA cost: 4.57 resc: 4.57 resc_io: 4.00 resc_cpu: 678154 resp: 4.57 resp_io: 4.00 resp_cpu: 678154 Best:: JoinMethod: Hash Cost: 4.57 Degree: 1 Resp: 4.57 Card: 106.00 Bytes: 27 . . . *********************** Join order[2]: EMPLOYEES[E]#1 DEPARTMENTS[D]#0 . . . *************** Now joining: DEPARTMENTS[D]#0 *************** . . . HA Join HA cost: 4.58 resc: 4.58 resc_io: 4.00 resc_cpu: 690054 resp: 4.58 resp_io: 4.00 resp_cpu: 690054 Join order aborted: cost > best plan cost ***********************
トレース・ファイルは、オプティマイザが最初に結合の外部表としてdepartments表の使用を試みていることを示しています。オプティマイザは、ネステッド・ループ結合(NL)、ソート/マージ(SM)およびハッシュ結合(HA)という3つの異なる結合方法のコストを計算します。オプティマイザは、最も効率的な方法としてハッシュ結合を選択します。
Best:: JoinMethod: Hash
Cost: 4.57 Degree: 1 Resp: 4.57 Card: 106.00 Bytes: 27
その後、オプティマイザは、外部表としてemployeesを使用して、別の結合順序を試行します。この結合順序のコストは、前の結合順序のコストよりも高いので、放棄されます。
オプティマイザは、内部カットオフを使用して計画数を削減し、最もコストの低い計画を検索しようとします。カットオフの基準は、現行の最適な計画のコストです。現行の最適コストが大きい場合、オプティマイザはコストがより低い別の計画を探索します。現行の最適コストが低い場合は、さらなる大幅なコストの改善ができないため、オプティマイザは検索を速やかに終了します。
オプティマイザは、どのように起動されるかによって異なる操作を実行します。
データベースには、次のタイプの最適化が用意されています。
標準の最適化
オプティマイザは、SQLをコンパイルして実行計画を生成します。標準モードでは、ほとんどのSQL文に対して妥当な実行計画が生成されます。標準モードでは、オプティマイザは、通常はミリ秒単位の厳密な時間的制約に従って動作し、その間に最適な計画を見つける必要があります。
SQLチューニング・アドバイザの最適化
SQLチューニング・アドバイザがオプティマイザを起動した場合、オプティマイザは自動チューニング・オプティマイザと呼ばれます。この場合、オプティマイザは、追加の分析を実行して、標準モードで生成された計画をさらに改善します。オプティマイザの出力は、実行計画ではなく、さらに優れた計画を生成するための一連のアクションと、その理論的根拠および予測されるメリットを示します。
関連項目:
SQLチューニング・アドバイザの詳細は、『Oracle Database 2日でパフォーマンス・チューニング・ガイド』を参照してください
Oracle Databaseの適応問合せ最適化により、オプティマイザは実行計画に対する実行時の調整を行い、より優れた統計を導くための追加情報を検出できます。
適応最適化は、最適な計画を生成するために既存の統計が十分でない場合に役に立ちます。次の図は、適応問合せ最適化の機能セットを示しています。
適応計画を使用すると、オプティマイザが文の最終計画を決定するのを実行時まで遅延させることができます。
この項の内容は次のとおりです。
適応計画には事前定義された複数のサブプランとオプティマイザ統計コレクタが含まれます。
サブプランは、オプティマイザが実行時に代替案として切り替えることが可能な、計画の一部分です。たとえば、実行時にネステッド・ループ結合をハッシュ結合に切り替えることができます。オプティマイザ統計コレクタは、実行時統計を収集するために、キー・ポイントで計画に挿入される行ソースです。オプティマイザはこれらの統計を利用して、複数のサブプランから最終的な計画を決定します。
文の実行時に、統計コレクタによって実行に関する情報が収集され、サブプランの受け取った行の一部がバッファされます。コレクタによって検出された情報に基づき、オプティマイザでサブプランが選択されます。この時点で、コレクタでは統計の収集と行のバッファリングが停止され、かわりに行が渡されます。以後、子カーソルの実行では、オプティマイザによって同じ計画が使用され、この計画がキャッシュから破棄されるか、別のオプティマイザ機能(適応カーソル共有や統計フィードバックなど)によって無効化されるまで使用が続きます。
OPTIMIZER_FEATURES_ENABLEが12.1.0.1以降の場合、データベースでは適応計画が使用され、OPTIMIZER_ADAPTIVE_REPORTING_ONLY初期化パラメータは、デフォルトであるfalseに設定されます。
関連項目:
この例では、実行時に収集された情報に基づいてオプティマイザが異なる計画を選択できる仕組みを示します。
次の問合せでは、表order_itemsと表prod_infoの結合を示しています。
SELECT product_name FROM order_items o, prod_info p WHERE o.unit_price = 15 AND quantity > 1 AND p.product_id = o.product_id
この文の適応問合せ計画では、2つの使用可能な計画が示されています。1つはネステッド・ループ結合を使用する計画、もう1つはハッシュ結合を使用する計画です。
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(FORMAT => 'ADAPTIVE'));
SQL_ID 7hj8dwwy6gm7p, child number 0
-------------------------------------
SELECT product_name FROM order_items o, prod_info p WHERE
o.unit_price = 15 AND quantity > 1 AND p.product_id = o.product_id
Plan hash value: 1553478007
-----------------------------------------------------------------------------
| Id | Operation | Name |Rows|Bytes|Cost (%CPU)|Time|
-----------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | |7(100)| |
| * 1| HASH JOIN | |4| 128 | 7 (0)|00:00:01|
|- 2| NESTED LOOPS | |4| 128 | 7 (0)|00:00:01|
|- 3| NESTED LOOPS | |4| 128 | 7 (0)|00:00:01|
|- 4| STATISTICS COLLECTOR | | | | | |
| * 5| TABLE ACCESS FULL | ORDER_ITEMS |4| 48 | 3 (0)|00:00:01|
|-* 6| INDEX UNIQUE SCAN | PROD_INFO_PK |1| | 0 (0)| |
|- 7| TABLE ACCESS BY INDEX ROWID| PROD_INFO |1| 20 | 1 (0)|00:00:01|
| 8| TABLE ACCESS FULL | PROD_INFO |1| 20 | 1 (0)|00:00:01|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
5 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))
6 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
結合述語によって行がフィルタ処理されることで、データベースでprod_infoのかなりの部分をスキャンせずに済むのであれば、ネステッド・ループ結合が適しています。ただし、行がほとんどフィルタ処理されていない場合は、ハッシュ結合の右表をスキャンすることが適切です。
次の図は、適応プロセスを示しています。前述の例の問合せでは、デフォルトの計画の適応部分には2つのサブプランが含まれており、それらのサブプランではそれぞれ異なる結合方法を使用します。オプティマイザは、結合の左側のカーディナリティに応じて、各結合方法が最適になるタイミングを自動的に判断します。
統計コレクタは、使用する結合方法を決定できるように、order_items表から取得された行を十分にバッファします。オプティマイザによって決定されたしきい値を行カウントが下回っている場合、オプティマイザは、ネステッド・ループ結合を選択します。それ以外の場合、オプティマイザは、ハッシュ結合を選択します。この場合、order_items表の行カウントがしきい値を上回っているため、オプティマイザは最終計画でハッシュ結合を選択し、バッファリングを無効にします。
実行計画のNoteセクションには、この計画が適応計画であるかどうか、および計画のどの行がアクティブでないかが示されます。
関連項目:
適応問合せ計画を示す詳細な例は、「実行計画の読取り: 詳細」を参照してください。
通常、パラレル実行では、パラレル・ソート、集計、結合などの操作を実行するためにデータを再配分する必要があります。
Oracle Databaseでは様々なデータ配分方法を使用できます。データベースでは、配分される行数および操作のパラレル・サーバー・プロセス数に基づいて方法が選択されます。
たとえば、次のような代替ケースについて考えてみます。
多くのパラレル・サーバー・プロセスで少ない行を配分する。
ブロードキャスト配分方法が選択される可能性があります。この場合、各パラレル・サーバー・プロセスで結果セットの各行を受け取ります。
少ないパラレル・サーバー・プロセスで多くの行を配分する。
データの再配分時にデータの偏りが見つかった場合、文のパフォーマンスに悪影響を及ぼす可能性があります。各パラレル・サーバー・プロセスで同じ行数を受け取るようにするために、ハッシュ配分が選択される可能性が高くなります。
ハイブリッド・ハッシュ配分手法は適応パラレル・データ配分です。この配分方法では実行時まで最終的なデータ配分方法は決定されません。オプティマイザは、操作のプロデューサ側のパラレル・サーバー・プロセスの前に統計コレクタを挿入します。行数がしきい値(並列度(DOP)の2倍で定義)よりも少ない場合は、データの配分方法がハッシュからブロードキャストに切り替わります。それ以外の場合、配分方法はハッシュになります。
ブロードキャスト配分
次の図は、departments表とemployees表の間のハイブリッド・ハッシュ結合を示し、ここでは、問合せコーディネータによって8つのパラレル・サーバー・プロセスが指示されています。P5からP8はプロデューサで、P1からP4はコンシューマです。各プロデューサに、それぞれ固有のコンシューマがあります。
データベースは、departments表をスキャンする各プロデューサ・プロセスの前に統計コレクタを挿入します。問合せコーディネータは、収集された統計を集計します。配分方法は実行時の統計に基づきます。図4-8では、行数がしきい値(8)、つまりDOP (4)の2倍を下回っているため、オプティマイザによってdepartments表に対してブロードキャスト手法が選択されます。
ハイブリッド・ハッシュ配分
より多くの行数を戻す例について検討します。次の例では、しきい値は8、すなわちDOPの指定値4の2倍です。ただし、統計コレクタでは行数(27)がしきい値(8)を上回っているため(手順10)、オプティマイザによってブロードキャスト配分のかわりにハイブリッド・ハッシュ配分が選択されます。(time列には00:00:01と表示される必要がありますが、計画がページに収まるように0:01と表示されています。)
EXPLAIN PLAN FOR SELECT /*+ parallel(4) full(e) full(d) */ department_name, sum(salary) FROM employees e, departments d WHERE d.department_id=e.department_id GROUP BY department_name; Plan hash value: 2940813933 ----------------------------------------------------------------------------------------------- |Id|Operation | Name |Rows|Bytes|Cost |Time| TQ |IN-OUT|PQ Distrib| ----------------------------------------------------------------------------------------------- | 0|SELECT STATEMENT |DEPARTMENTS| 27|621 |6(34)|0:01| | | | | 1| PX COORDINATOR | | | | | | | | | | 2| PX SEND QC (RANDOM) | :TQ10003 | 27|621 |6(34)|0:01|Q1,03|P->S| QC (RAND) | | 3| HASH GROUP BY | | 27|621 |6(34)|0:01|Q1,03|PCWP| | | 4| PX RECEIVE | | 27|621 |6(34)|0:01|Q1,03|PCWP| | | 5| PX SEND HASH | :TQ10002 | 27|621 |6(34)|0:01|Q1,02|P->P| HASH | | 6| HASH GROUP BY | | 27|621 |6(34)|0:01|Q1,02|PCWP| | |*7| HASH JOIN | |106|2438|5(20)|0:01|Q1,02|PCWP| | | 8| PX RECEIVE | | 27|432 |2 (0)|0:01|Q1,02|PCWP| | | 9| PX SEND HYBRID HASH | :TQ10000 | 27|432 |2 (0)|0:01|Q1,00|P->P|HYBRID HASH| |10| STATISTICS COLLECTOR | | | | | |Q1,00|PCWC| | |11| PX BLOCK ITERATOR | | 27|432 |2 (0)|0:01|Q1,00|PCWC| | |12| TABLE ACCESS FULL |DEPARTMENTS| 27|432 |2 (0)|0:01|Q1,00|PCWP| | |13| PX RECEIVE | |107|749 |2 (0)|0:01|Q1,02|PCWP| | |14| PX SEND HYBRID HASH (SKEW)| :TQ10001 |107|749 |2 (0)|0:01|Q1,01|P->P|HYBRID HASH| |15| PX BLOCK ITERATOR | |107|749 |2 (0)|0:01|Q1,01|PCWC| | |16| TABLE ACCESS FULL | EMPLOYEES |107|749 |2 (0)|0:01|Q1,01|PCWP| | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 7 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID") Note ----- - Degree of Parallelism is 4 because of hint 32 rows selected.
関連項目:
パラレル・データ配分手法の詳細は、『Oracle Database VLDBおよびパーティショニング・ガイド』を参照してください。
適応計画では、一致する行の数が大幅に削減されない索引をプルーニングします。
オプティマイザがスター型変換計画を生成した場合、オプティマイザはビットマップ索引の適切な組合せを選択して、関連するROWIDのセットをできるだけ効率的に削減する必要があります。多数の索引が存在する場合、一部の索引が、ROWIDのセットの大幅な削減につながらず、かつ問合せの実行中の処理コストを増加させることがあります。適応計画では、パフォーマンスを低下させる索引を使用しないことでこの問題を解決できます。
例4-3 ビットマップ索引プルーニング
この例では、次のスター問合せを発行し、carsファクト表を複数のディメンション表に結合します(出力例も示します)。
SELECT /*+ star_transformation(r) */ l.color_name, k.make_name,
h.filter_col, count(*)
FROM cars r, colors l, makes k, models d, hcc_tab h
WHERE r.make_id = k.make_id
AND r.color_id = l.color_id
AND r.model_id = d.model_id
AND r.high_card_col = h.high_card_col
AND d.model_name = 'RAV4'
AND k.make_name = 'Toyota'
AND l.color_name = 'Burgundy'
AND h.filter_col = 100
GROUP BY l.color_name, k.make_name, h.filter_col;
COLOR_NA MAKE_N FILTER_COL COUNT(*)
-------- ------ ---------- ----------
Burgundy Toyota 100 15000
次のサンプル実行計画は、問合せで手順12と手順17でビットマップ・ノードに対して行が生成されなかったことを示しています。適応オプティマイザは、CAR_MODEL_IDX索引およびCAR_MAKE_IDX索引を使用して行をフィルタリングすることは効率的でないと判断しました。問合せではダッシュ(-)で始まる計画の手順は使用されませんでした。
----------------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT GROUP BY NOSORT | | | 2 | HASH JOIN | | | 3 | VIEW | VW_ST_5497B905 | | 4 | NESTED LOOPS | | | 5 | BITMAP CONVERSION TO ROWIDS | | | 6 | BITMAP AND | | | 7 | BITMAP MERGE | | | 8 | BITMAP KEY ITERATION | | | 9 | TABLE ACCESS FULL | COLORS | | 10 | BITMAP INDEX RANGE SCAN | CAR_COLOR_IDX | |- 11 | STATISTICS COLLECTOR | | |- 12 | BITMAP MERGE | | |- 13 | BITMAP KEY ITERATION | | |- 14 | TABLE ACCESS FULL | MODELS | |- 15 | BITMAP INDEX RANGE SCAN | CAR_MODEL_IDX | |- 16 | STATISTICS COLLECTOR | | |- 17 | BITMAP MERGE | | |- 18 | BITMAP KEY ITERATION | | |- 19 | TABLE ACCESS FULL | MAKES | |- 20 | BITMAP INDEX RANGE SCAN | CAR_MAKE_IDX | | 21 | TABLE ACCESS BY USER ROWID | CARS | | 22 | MERGE JOIN CARTESIAN | | | 23 | MERGE JOIN CARTESIAN | | | 24 | MERGE JOIN CARTESIAN | | | 25 | TABLE ACCESS FULL | MAKES | | 26 | BUFFER SORT | | | 27 | TABLE ACCESS FULL | MODELS | | 28 | BUFFER SORT | | | 29 | TABLE ACCESS FULL | COLORS | | 30 | BUFFER SORT | | | 31 | TABLE ACCESS FULL | HCC_TAB | ----------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2) - star transformation used for this statement - this is an adaptive plan (rows marked '-' are inactive)
問合せ述語が複雑すぎて実表の統計のみに基づくことは十分でない場合、オプティマイザでは適応統計を使用できます。
次の各トピックでは、適応統計のタイプについて説明します。
SQL文のコンパイル時に、オプティマイザは、最適な実行計画を生成するために使用可能な統計が十分にあるかどうかを検討し、動的統計を使用するかどうかを判断します。
使用可能な統計が不十分な場合、オプティマイザは動的統計を使用して統計を増強します。動的統計の一つに、動的サンプリングによって収集された情報があります。オプティマイザでは、表スキャン、索引アクセス、結合およびGROUP BY操作の統計を使用でき、その結果、オプティマイザの決定の品質が向上します。
関連項目:
動的統計およびオプティマイザ統計の詳細は、「補足的な動的統計」を参照してください。
自動再最適化では、オプティマイザによって、最初の実行後に以降の実行の計画が変更されます。
適応問合せ計画は、すべての種類の計画変更に適しているわけではありません。たとえば、非効率な結合順序の問合せは、最適には実行されませんが、適応問合せ計画は、実行時の結合順序の適応をサポートしていません。SQL文の最初の実行の最後に、オプティマイザは実行時に収集された情報を使用して、自動再最適化にコスト・ベネフィットがあるかどうかを判断します。実行情報がオプティマイザの見積りと大幅に異なる場合、オプティマイザは次回実行時に置き換えるための計画を探します。
オプティマイザは、前回の実行時に収集された情報を使用して代替計画を決定します。オプティマイザは、問合せを複数回再最適化でき、そのたびに追加データを収集し、計画が改善されます。
自動再最適化には、統計フィードバックとパフォーマンス・フィードバックの2つの形式があります。
関連項目:
統計フィードバック(旧カーディナリティ・フィードバック)と呼ばれる再最適化の形式は、カーディナリティの見積りが誤っている繰返し問合せの計画を自動的に改善します。
オプティマイザは、統計がない、統計が不正確である、述語が複雑であるなど、多数の理由でカーディナリティを誤って見積ることがあります。統計フィードバックを使用した再最適化の基本プロセスは次のとおりです。
SQL文の最初の実行時に、オプティマイザが実行計画を生成します。
オプティマイザは、次のような場合に、共有SQL領域の統計フィードバックの監視を有効化することがあります。
表に統計がない場合
接続フィルタ述語または選言フィルタ述語が表に複数ある場合
オプティマイザが選択性の見積りを正確に計算できないような複雑な演算子が述語に含まれる場合
実行の最後に、オプティマイザは最初のカーディナリティの見積りと、実行時に計画の各操作で返された実際の行数を比較します。見積りが実際のカーディナリティと大きく異なる場合、オプティマイザは以降に使用するために正しい見積りを保存します。オプティマイザは、最初の実行時に取得された情報を他のSQL文が有効に利用できるようにSQL計画ディレクティブも作成します。
最初の実行後、オプティマイザは統計フィードバックの監視を無効にします。
問合せが再度実行された場合、オプティマイザは、その通常の見積りではなく、修正されたカーディナリティの見積りを使用します。
例4-4 統計フィードバック
次の例では、データベースが統計フィードバックを使用して、誤った見積りを調整する仕組みを示します。
ユーザーoeは、orders、order_itemsおよびproduct_information表の次の問合せを実行します。
SELECT o.order_id, v.product_name
FROM orders o,
( SELECT order_id, product_name
FROM order_items o, product_information p
WHERE p.product_id = o.product_id
AND list_price < 50
AND min_price < 40 ) v
WHERE o.order_id = v.order_id
カーソル内の計画を問い合せてみると、見積られた行数(E-Rows)が実際の行数(A-Rows)よりも非常に少ないことがわかりました。
--------------------------------------------------------------------------------------------------
| Id | Operation | Name |Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|O/1/M|
--------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1| | 269 |00:00:00.14|1338| | | |
| 1| NESTED LOOPS | | 1| 1 | 269 |00:00:00.14|1338| | | |
| 2| MERGE JOIN CARTESIAN| | 1| 4 |9135 |00:00:00.05| 33| | | |
|*3| TABLE ACCESS FULL |PRODUCT_INFORMATION| 1| 1 | 87 |00:00:00.01| 32| | | |
| 4| BUFFER SORT | | 87| 105 |9135 |00:00:00.02| 1|4096|4096|1/0/0|
| 5| INDEX FULL SCAN |ORDER_PK | 1| 105 | 105 |00:00:00.01| 1| | | |
|*6| INDEX UNIQUE SCAN |ORDER_ITEMS_UK |9135| 1 | 269 |00:00:00.04|1305| | | |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50))
6 - access("O"."ORDER_ID"="ORDER_ID" AND "P"."PRODUCT_ID"="O"."PRODUCT_ID")
ユーザーoeは、手順1の問合せを再実行します。
カーソル内の計画を問い合せてみると、2回目の実行ではオプティマイザが統計フィードバック(Noteを参照)を使用し、異なる計画を選択したことがわかりました。
--------------------------------------------------------------------------------------------------
|Id | Operation | Name | Starts |E-Rows|A-Rows|A-Time|Buffers|Reads|OMem|1Mem|O/1/M|
--------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1| | 269 |00:00:00.05|60|1| | | |
| 1| NESTED LOOPS | | 1|269| 269 |00:00:00.05|60|1| | | |
|*2| HASH JOIN | | 1|313| 269 |00:00:00.05|39|1|1398K|1398K|1/0/0|
|*3| TABLE ACCESS FULL |PRODUCT_INFORMATION| 1| 87| 87 |00:00:00.01|15|0| | | |
| 4| INDEX FAST FULL SCAN|ORDER_ITEMS_UK | 1|665| 665 |00:00:00.01|24|1| | | |
|*5| INDEX UNIQUE SCAN |ORDER_PK |269| 1| 269 |00:00:00.01|21|0| | | |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50))
5 - access("O"."ORDER_ID"="ORDER_ID")
Note
-----
- statistics feedback used for this statement
この出力では、手順1で見積られた行数(269)は、実際の行数と一致しています。
再最適化の別の形式は、パフォーマンス・フィードバックです。この再最適化は、PARALLEL_DEGREE_POLICYがADAPTIVEに設定されている場合に、繰返しSQL文の自動的に選択された並列度を改善するために役立ちます。
パフォーマンス・フィードバックを使用した再最適化の基本プロセスは次のとおりです。
SQL文の最初の実行時に、PARALLEL_DEGREE_POLICYがADAPTIVEに設定されていると、オプティマイザはその文をパラレルに実行するかどうか、また、実行する場合はどの程度の並列度を使用するかを決定します。
オプティマイザは、文の推定パフォーマンスに基づいて並列度を選択します。すべての文に対して追加のパフォーマンス監視が有効になります。
最初の実行の最後に、オプティマイザは次のことを比較します。
オプティマイザによって選択された並列度
文の実際の実行時に収集されたパフォーマンス統計(CPU時間など)に基づいて計算された並列度
2つの値が大幅に異なる場合、その文は再解析の対象としてマークされ、最初の実行統計がフィードバックとして保存されます。このフィードバックは、以降の実行で並列度の計算精度を向上させるのに役立ちます。
問合せが再度実行された場合、オプティマイザは、最初の実行時に収集されたパフォーマンス統計を使用して、より適切な文の並列度を決定します。
注意:
PARALLEL_DEGREE_POLICYがADAPTIVEに設定されていない場合でも、統計フィードバックが文に選択された並列度に影響する場合があります。
SQL計画ディレクティブは、オプティマイザがより最適な計画を生成するために使用する追加情報です。
たとえば、問合せ最適化中に、表が動的統計の候補であるかどうかを判断する場合、統計リポジトリに対して表のディレクティブが問い合されます。問合せで結合列にデータの偏りがある2つの表を結合する場合、SQL計画ディレクティブでは、動的統計を使用して正確なカーディナリティの見積りを取得するようオプティマイザに指示できます。
オプティマイザは、文レベルではなく、問合せ式のSQL計画ディレクティブを収集します。このようにして、オプティマイザはディレクティブを複数のSQL文に適用できます。データベースは自動的に、ディレクティブを保持し、それらをSYSAUX表領域に格納します。パッケージDBMS_SPDを使用してディレクティブを管理できます。
関連項目:
DBMS_SPDパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
SQL計画管理は、データベースで既知の計画または確認済の計画のみが使用されるようにオプティマイザで実行計画を自動的に管理するメカニズムです。
このメカニズムにより、SQL計画ベースラインを構築できます。これには、各SQL文の承認済計画が1つ以上含まれます。
オプティマイザは、SQL文の計画履歴およびSQL計画ベースラインにアクセスし、それらを管理できます。この機能は、SQL計画管理アーキテクチャの中核を成すものです。SQL計画管理では、オプティマイザには次の主な目的があります。
繰返し可能なSQL文の識別
一連のSQL文に関する計画履歴および場合によってはSQL計画ベースラインの保持
計画履歴に存在しない計画の検出
SQL計画ベースラインに存在しない、潜在的により優れた計画の検出
オプティマイザは、通常のコストベースの検索方法を使用します。
関連項目:
