日本語PDF

4 問合せオプティマイザの概念

この章では、主なコンポーネントを含む問合せオプティマイザに関連する最も重要な概念について説明します。

4.1 問合せオプティマイザの概要

問合せオプティマイザ(単にオプティマイザと呼ばれます)は、SQL文が必要なデータにアクセスするための最も効率的な方法を判断する組込みデータベース・ソフトウェアです。

4.1.1 問合せオプティマイザの目的

オプティマイザは、SQL文の最適な実行計画の生成を試行します。

オプティマイザは、考えられるすべての計画の候補から、コストが最小になる計画を選択します。オプティマイザは、コストの計算に利用可能な統計を使用します。特定の環境内の特定の問合せについて、問合せ実行の要因(I/O、CPU、通信など)がコスト計算によって明らかになります。

たとえば、ある問合せが管理職の従業員に関する情報をリクエストするとします。オプティマイザ統計に従業員の80%がマネージャであることが示されている場合、オプティマイザは、全表スキャンが最も効率的であると判断するようになります。ただし、統計でマネージャが非常に少ないことが示されている場合、索引を読み取ってからROWIDによる表アクセスを行うほうが全表スキャンよりも効率的なことがあります。

データベースには、自由に使用できる内部統計とツールが多数あるため、通常、オプティマイザは文の最適な実行方法を決める上でユーザーよりも優位な立場にあります。このため、すべてのSQL文でオプティマイザが使用されます。

4.1.2 コストベースの最適化

問合せの最適化は、SQL文を実行する最も効率的な方法を選択するプロセスです。

SQLは非手続き型言語であるため、オプティマイザでマージ、再編成、処理の順序を自由に選択できます。アクセスするデータに関して収集された統計に基づいて、各SQL文が最適化されます。オプティマイザは、複数のアクセス方法(全表スキャンや索引スキャンなど)、様々な結合方法(ネステッド・ループ結合やハッシュ結合など)、様々な結合順序および可能な変換を調べることで、SQL文にとって最適な計画を判断します。

指定された問合せおよび環境では、オプティマイザは関連する数値コストを可能な計画の各ステップに割り当てた後、これらの値を総合的に要因分解して計画の全体的なコスト見積りを生成します。代替計画のコストを計算した後、オプティマイザは最もコストの低い見積りの計画を選択します。このため、オプティマイザは、従来のルールベース・オプティマイザ(RBO)との対比で、コストベース・オプティマイザ(CBO)と呼ばれることがあります。

ノート:

オプティマイザは、Oracle Databaseのあるバージョンとその次のバージョンで同じ決定を行うとはかぎりません。新しいバージョンのオプティマイザでは、より高度な情報を使用でき、かつより多くのオプティマイザ変換が可能なため、異なる決定が行われる場合があります。

4.1.3 実行計画

実行計画とは、SQL文の推奨実行方法を示したものです。

この計画により、SQL文を実行するためにOracle Databaseで使用するステップの組合せが示されます。各処理では、データベースからデータ行を物理的に検索するか、文を発行したユーザーのためにデータ行を準備します。

実行計画では、計画全体のコスト(行0に示されます)および個々の操作ごとのコストが表示されます。コストは、計画の比較で使用できるように実行計画のみが表示される内部的な単位です。そのため、コストの値をチューニングしたり変更したりすることはできません。

次の図では、オプティマイザは、入力SQL文の実行計画として2つの使用可能な計画を生成し、統計を使用してそれらのコストを見積り、コストを比較して、最もコストの低い計画を選択します。

図4-1 実行計画

図4-1「実行計画」の説明

4.1.3.1 問合せブロック

オプティマイザへの入力は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概要』を参照してください。

4.1.3.2 問合せサブプラン

各問合せブロックについて、オプティマイザは、問合せサブプランを生成します。

データベースは、問合せブロックを下位から上位へ順番に最適化します。このため、データベースは最も内側の問合せブロックを最初に最適化してそのサブプランを生成し、そして問合せ全体を表す外側の問合せブロックを生成します。

問合せブロックに使用できる可能性のある計画の数は、FROM句にあるオブジェクトの数に比例します。この数は、オブジェクトの数によって指数関数的に上昇します。たとえば、5つの表の結合に使用できる可能性のある計画の数は、2つの表の結合に使用できる可能性のある計画の数より大幅に多くなります。

4.1.3.3 オプティマイザの例え

オプティマイザの1つの例えとして、オンライン旅行アドバイザがあります。

ある旅行者が自転車に乗ってA地点からB地点に移動するための最も効率的なルートを知りたいと思っています。問合せは、「A地点からB地点への最も効率的なルートが知りたい」、「A地点からC地点経由でB地点に行く最も効率的なルートが知りたい」などのようなディレクティブになります。旅行アドバイザは、速度や難易度などの要因に基づく内部アルゴリズムを使用して、最も効率的なルートを決定します。旅行者は、「できるだけ速く到着したい」、「できるだけ楽なルートで行きたい」などのディレクティブを使用することで、旅行アドバイザの決定に影響を与えることができます。

この例えでは、旅行アドバイザが作成する可能なルートが実行計画に該当します。内部的には、アドバイザは、全体ルートをいくつかのサブルート(サブプラン)に分け、各サブルートについて別々に効率性を判断することも可能です。たとえば、旅行アドバイザは、あるサブルートについては所要時間15分難易度中、別のサブルートについては所要時間22分難易度低などのように見積ることが考えられます。

アドバイザは、ユーザーが指定した目的と、道路や交通の状況に関する利用可能な統計情報に基づいて、最も効率的な(コストの低い)全体ルートを選択します。統計情報が正確であればあるほど、より適切なアドバイスを提供できます。たとえば、アドバイザが交通渋滞、通行止めおよび道路の状態の悪さについて頻繁に確認していない場合、推奨されたルートは、結局は非効率な(コストが高い)ものになることがあります。

4.2 オプティマイザのコンポーネントについて

オプティマイザには、3つのコンポーネント(トランスフォーマ、エスティメータおよびプラン・ジェネレータ)が含まれています。

次の図にコンポーネントを示します。

図4-2 オプティマイザのコンポーネント

「図4-2 オプティマイザのコンポーネント」の説明

問合せブロックのセットは、解析済の問合せを表します。解析済の問合せは、オプティマイザへの入力です。次の表にオプティマイザ操作を示します。

表4-1 オプティマイザ操作

フェーズ	操作	説明	さらに学習するには
1	問合せトランスフォーマ	オプティマイザは、オプティマイザがより優れた実行計画を生成できるように、問合せの形式を変更することが有用かどうかを判断します。	「問合せトランスフォーマ」
2	エスティメータ	オプティマイザは、データ・ディクショナリ内の統計に基づき、各計画のコストを見積ります。	「エスティメータ」
3	プラン・ジェネレータ	オプティマイザは各計画のコストを比較し、コストが最も低い計画(実行計画と呼ばれます)を選択して、行ソース・ジェネレータに渡します。	「プラン・ジェネレータ」

4.2.1 問合せトランスフォーマ

一部の文では、問合せトランスフォーマは、元のSQL文を、意味的に同等でよりコストの低いSQL文にリライトすることが有利かどうかを判断します。

実行可能な代替案がある場合、データベースでは、代替案のコストが別々に計算され、最もコストの低い代替案が選択されます。次の図は、ORを使用する入力問合せを、UNION ALLを使用する出力問合せにリライトする問合せトランスフォーマを示しています。

図4-3 問合せトランスフォーマ

図4-3「問合せトランスフォーマ」の説明

4.2.2 エスティメータ

エスティメータは、指定された実行計画の全体コストを判断する、オプティマイザのコンポーネントです。

エスティメータは、3つの異なる計測を使用してコストを決定します。

• 選択性

  問合せで選択される行セットの中の行の割合で、0は行がないことを表し、1はすべての行を表します。選択性は、問合せの述語(WHERE last_name LIKE 'A%'など)や述語の組合せに関連します。述語は、選択性の値が0に近付くほど選択性が高くなり、値が1に近付くほど選択性が低く(非選択性が高く)なります。

  ノート:

  選択性は、実行計画では表示されない内部的な計算です。

• カーディナリティ

  カーディナリティは、実行計画で各操作によって戻される行数です。これは最適な計画の作成に不可欠であり、すべてのコスト機能に共通の入力です。エスティメータは、DBMS_STATSによって収集される表統計からカーディナリティを導出することもでき、述語(フィルタ、結合など)や、DISTINCTまたはGROUP BY操作などの影響を考慮した後で導出することもできます。実行計画のRows列には、予測カーディナリティが表示されます。

• コスト

  このメジャーは、作業単位または使用されるリソースを表します。問合せオプティマイザはディスクI/O、CPU使用量、メモリー使用量を作業単位として使用します。

次の図に示すように、統計が使用可能な場合にはエスティメータは統計を使用してメジャーを計算します。統計によって、メジャーの正確さの度合いは改良されます。

図4-4 エスティメータ

図4-4「エスティメータ」の説明

例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|
---------------------------------------------------------------------------

4.2.2.1 選択性

選択性は行セットの一部の行を表します。

行セットは、実表、ビュー、または結合の結果のいずれでもかまいません。選択性は、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列にヒストグラムがある場合、エスティメータは個別値の数かわりにヒストグラムを使用します。ヒストグラムには列内の異なる値の分布が示されているので、特にデータの偏りがある列では、より適切な選択性の見積りが行われます。

関連項目:

• ヒストグラム

• OPTIMIZER_DYNAMIC_SAMPLINGについてさらに学習するには、『Oracle Databaseリファレンス』を参照してください

4.2.2.2 カーディナリティ

カーディナリティは、実行計画で各操作によって戻される行数です。

たとえば、全表スキャンによって戻される行数に関するオプティマイザの見積りが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表をプローブする頻度を決定します。カーディナリティはまた、ソートのコストを計算する上でも重要です。

4.2.2.3 コスト

オプティマイザのコスト・モデルにより、問合せが使用すると予測されるマシン・リソースが明らかになります。

コストは、計画のリソース使用量の見積りを表す内部の数値メジャーです。コストは、オプティマイザ環境での問合せに固有です。コストを見積もるために、オプティマイザでは次のような要因が考慮されます。

• システム・リソース(見積もられるI/O、CPU、メモリーなどを含む)

• 返される行数の見積もり(カーディナリティ)

• 初期データ・セットのサイズ

• データの分布

• アクセス構造

ノート:

コストとは、同じ問合せに対する異なる計画を比較するために、オプティマイザが使用する内部の測定単位です。コストの調整や変更はできません。

実行時間にはコストとの相関関係がありますが、コストと時間が直接的に一致することはありません。たとえば、問合せAの計画が問合せBの計画よりもコストが小さいとすると、次に示す結果が考えられます。

• Aの実行は、Bの実行よりも速くなる。

• Aの実行は、Bの実行よりも遅くなる。

• Aの実行時間は、Bの実行時間と同じになる。

そのため、異なる問合せのコストを相互に比較することはできません。また、異なるオプティマイザ・モードを使用する意味的に同等の問合せのコストを比較することもできません。

4.2.3 プラン・ジェネレータ

プラン・ジェネレータは、別のアクセス・パス、結合方法および結合順序を試行することによって、問合せブロックに対する様々な計画を探索します。

データベースでは、様々な組合せを使用して、同じ結果をもたらすことができるため、様々な計画が可能になります。オプティマイザは、コストが最も低い計画を選択します。

次の図は、入力問合せに異なる計画をテストしているオプティマイザを示しています。

図4-5 プラン・ジェネレータ

「図4-5 プラン・ジェネレータ」の説明

オプティマイザのトレース・ファイルの次のスニペットは、オプティマイザが実行するいくつかの計算を示しています。

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を使用して、別の結合順序を試行します。この結合順序のコストは、前の結合順序のコストよりも高いので、放棄されます。

オプティマイザは、内部カットオフを使用して計画数を削減し、最もコストの低い計画を検索しようとします。カットオフの基準は、現行の最適な計画のコストです。現行の最適コストが大きい場合、オプティマイザはコストがより低い別の計画を探索します。現行の最適コストが低い場合は、さらなる大幅なコストの改善ができないため、オプティマイザは検索を速やかに終了します。

4.3 自動チューニング・オプティマイザについて

オプティマイザは、どのように起動されるかによって異なる操作を実行します。

データベースには、次のタイプの最適化が用意されています。

• 標準の最適化

  オプティマイザは、SQLをコンパイルして実行計画を生成します。標準モードでは、ほとんどのSQL文に対して妥当な実行計画が生成されます。標準モードでは、オプティマイザは、通常はミリ秒単位の厳密な時間的制約に従って動作し、その間に最適な計画を見つける必要があります。

• SQLチューニング・アドバイザの最適化

  SQLチューニング・アドバイザがオプティマイザを起動した場合、オプティマイザは自動チューニング・オプティマイザと呼ばれます。この場合、オプティマイザは、追加の分析を実行して、標準モードで生成された計画をさらに改善します。オプティマイザの出力は、実行計画ではなく、さらに優れた計画を生成するための一連のアクションと、その理論的根拠および予測されるメリットを示します。

関連項目:

• SQLチューニング・アドバイザを使用したSQLの分析

• SQLチューニング・アドバイザについてさらに学習するには、『Oracle Database 2日でパフォーマンス・チューニング・ガイド』を参照してください

4.4 適応問合せ最適化について

Oracle Databaseでは、適応問合せ最適化により、オプティマイザは、実行計画に対する実行時の調整を行い、より優れた統計を導くための追加情報を検出できます。

適応最適化は、最適な計画を生成するために既存の統計が十分でない場合に役に立ちます。次の図は、適応問合せ最適化の機能セットを示しています。

図4-6 適応問合せ最適化

「図4-6 適応問合せ最適化」の説明

4.4.1 適応問合せ計画

適応問合せ計画により、オプティマイザは実行中に文の計画を決定できます。

適応問合せ計画では、オプティマイザが一部のクラスの問題を実行時に修正できます。適応計画は、デフォルトで有効化されています。

4.4.1.1 適応問合せ計画について

適応問合せ計画には事前定義された複数のサブプランとオプティマイザ統計コレクタが含まれます。実行中に収集した統計に基づいて、動的計画コーディネータは、実行時に最適な計画を選択します。

動的計画

実行時に計画を変更するために、適応問合せ計画は、サブプラン・グループのセットとして表される動的計画を使用します。サブプラン・グループはサブプランのセットです。サブプランは、オプティマイザが実行時に代替案として切り替えることが可能な、計画の一部分です。たとえば、実行中にネステッド・ループ結合をハッシュ結合に切り替えることができます。

オプティマイザは、実行時にどのサブプランを使用するかを判断します。サブプラン・グループに関連する新しい統計値が通知されると、コーディネータは、このサブグループのハンドラ関数にディスパッチします。

図4-7 動的計画コーディネータ

「図4-7 動的計画コーディネータ」の説明

オプティマイザ統計コレクタ

オプティマイザ統計コレクタは、カーディナリティおよびヒストグラムに関連する実行時統計を収集するために、キー・ポイントで計画に挿入される行ソースです。オプティマイザはこれらの統計を利用して、複数のサブプランから最終的な計画を決定します。コレクタは、内部しきい値までのオプションのバッファリングもサポートします。

統計コレクタのパラレル・バッファリングのために、各パラレル実行サーバーが統計を収集し、これをパラレル問合せコーディネータが集計してクライアントに送信します。このコンテキストでは、クライアントが、動的計画のような収集された統計のコンシューマです。各クライアントが、各パラレルサーバーまたは問合せコーディネータで実行するコールバック関数を指定します。

4.4.1.2 適応問合せ計画の目的

オプティマイザは、実行時に得た統計に基づいて計画を適応させることができるので、問合せのパフォーマンスを大幅に向上できます。

適応問合せ計画が役立つのは、誤ったカーディナリティの見積りにより、オプティマイザが最適ではないデフォルトの計画を選択する場合です。オプティマイザが実際の実行統計に基づいて実行時に最適な計画を選択できるので、より最適な最終計画が生成されます。オプティマイザは最終計画を選択すると、以降の実行でその計画を使用します。そのため、最適ではない計画が再利用されることはありません。

4.4.1.3 適応問合せ計画の仕組み

文の最初の実行の場合、オプティマイザはデフォルトの計画を使用した後、適応計画を格納します。データベースは、特定の条件が満たされないかぎり、後続の実行に適応計画を使用します。

文の最初の実行中に、データベースは次のステップを実行します。

1. データベースは、デフォルトの計画を使用して、文の実行を開始します。

2. 統計コレクタによって進行中の実行に関する情報が収集され、サブプランの受け取った行の一部がバッファされます。

   統計コレクタを並列してバッファリングするため、各スレーブ・プロセスでは、クライアントに送信する前に問合せコーディネータが集計する統計を収集します。

3. コレクタが収集する統計に基づいて、オプティマイザはサブプランを選択します。

   動的計画コーディネータは、すべてのそのようなサブプラン・グループに対して実行時に使用するサブプランを決定します。サブプラン・グループに関連する新しい統計値が通知されると、コーディネータは、このサブグループのハンドラ関数にディスパッチします。

4. コレクタでは統計の収集と行のバッファリングを停止し、かわりに行の通過を許可します。

5. 文の次の実行で使用できるように、データベースでは子カーソルに適応計画を格納します。

子カーソルの後続の実行で、次の条件のいずれかがtrueでないかぎり、オプティマイザは同じ適応計画を引き続き使用します。その場合、現在の実行の新しい計画が選択されます。

• 現在の計画は共有プールからエージ・アウトします。

• 別のオプティマイザ機能(たとえば、適応カーソル共有または統計フィードバック)は、現在の計画を無効化します。

4.4.1.3.1 適応問合せ計画: 結合方法の例

この例では、実行時に収集した情報に基づいてオプティマイザがどのようにして異なる計画を選択できるかを示しています。

次の問合せでは、表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表の行カウントがしきい値を上回っているため、オプティマイザは最終計画でハッシュ結合を選択し、バッファリングを無効にします。

図4-8 適応結合方法

「図4-8 適応結合方法」の説明

実行計画のNoteセクションには、この計画が適応計画であるかどうかと、計画のどの行がアクティブでないかが示されます。

関連項目:

• 「適応最適化の制御」

• 適応問合せ計画を示す詳細な例は、「適応問合せ計画の表示: チュートリアル」を参照

4.4.1.3.2 適応問合せ計画: パラレル配分方法

通常、パラレル実行では、パラレル・ソート、集計、結合などの操作を実行するためにデータを再配分する必要があります。

Oracle Databaseでは様々なデータ配分方法を使用できます。データベースでは、配分される行数および操作のパラレル・サーバー・プロセス数に基づいて方法が選択されます。

たとえば、次のような代替ケースについて考えてみます。

• 多くのパラレル・サーバー・プロセスで少ない行を配分する。

  ブロードキャスト配分方法が選択される可能性があります。この場合、それぞれのパラレル・サーバー・プロセスが結果セットの各行を受け取ります。

• 少ないパラレル・サーバー・プロセスで多くの行を配分する。

  データの再配分時にデータの偏りが見つかった場合、文のパフォーマンスに悪影響を及ぼす可能性があります。各パラレル・サーバー・プロセスで同じ行数を受け取るようにするために、ハッシュ配分が選択される可能性が高くなります。

ハイブリッド・ハッシュ配分手法は適応パラレル・データ配分です。この配分方法では実行時まで最終的なデータ配分方法は決定されません。オプティマイザは、操作のプロデューサ側のパラレル・サーバー・プロセスの前に統計コレクタを挿入します。行数がしきい値(並列度(DOP)の2倍で定義)よりも少ない場合は、データの配分方法がハッシュからブロードキャストに切り替わります。それ以外の場合、配分方法はハッシュになります。

ブロードキャスト配分

次の図は、departments表とemployees表の間のハイブリッド・ハッシュ結合を、8つのパラレル・サーバー・プロセス(P5-P8はプロデューサでP1-P4はコンシューマ)に指示している問合せコーディネータとともに示しています。各プロデューサが独自のコンシューマを持っています。

図4-9 DOPが4の適応問合せ

「図4-9 DOPが4の適応問合せ」の説明

データベースは、departments表をスキャンする各プロデューサ・プロセスの前に統計コレクタを挿入します。問合せコーディネータは収集した統計を集計します。配分方法は実行時の統計に基づきます。図4-9では、行数がしきい値(8)、つまりDOP (4)の2倍を下回っているため、オプティマイザによってdepartments表に対してブロードキャスト手法が選択されます。

ハイブリッド・ハッシュ配分

多数の行を返す例を考えてみます。次の例では、しきい値は8、すなわちDOPの指定値4の2倍です。ただし、統計コレクタでは行数(27)がしきい値(8)を上回っているため(ステップ10)、オプティマイザによってブロードキャスト配分のかわりにハイブリッド・ハッシュ配分が選択されます。

ノート:

次の計画では、行がページに収まるように、NameおよびTimeの値が切り捨てられています。

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                   |DEPARTME| 27|621 |6(34)|:01|     |    |           |
| 1| PX COORDINATOR                    |        |   |    |     |   |     |    |           |
| 2|  PX SEND QC (RANDOM)              |:TQ10003| 27|621 |6(34)|:01|Q1,03|P->S| QC (RAND) |
| 3|   HASH GROUP BY                   |        | 27|621 |6(34)|:01|Q1,03|PCWP|           |
| 4|    PX RECEIVE                     |        | 27|621 |6(34)|:01|Q1,03|PCWP|           |
| 5|     PX SEND HASH                  |:TQ10002| 27|621 |6(34)|:01|Q1,02|P->P| HASH      |
| 6|      HASH GROUP BY                |        | 27|621 |6(34)|:01|Q1,02|PCWP|           |
|*7|       HASH JOIN                   |        |106|2438|5(20)|:01|Q1,02|PCWP|           |
| 8|        PX RECEIVE                 |        | 27|432 |2 (0)|:01|Q1,02|PCWP|           |
| 9|         PX SEND HYBRID HASH       |:TQ10000| 27|432 |2 (0)|:01|Q1,00|P->P|HYBRID HASH|
|10|          STATISTICS COLLECTOR     |        |   |    |     |   |Q1,00|PCWC|           |
|11|           PX BLOCK ITERATOR       |        | 27|432 |2 (0)|:01|Q1,00|PCWC|           |
|12|            TABLE ACCESS FULL      |DEPARTME| 27|432 |2 (0)|:01|Q1,00|PCWP|           |
|13|        PX RECEIVE                 |        |107|749 |2 (0)|:01|Q1,02|PCWP|           |
|14|         PX SEND HYBRID HASH (SKEW)|:TQ10001|107|749 |2 (0)|:01|Q1,01|P->P|HYBRID HASH|
|15|          PX BLOCK ITERATOR        |        |107|749 |2 (0)|:01|Q1,01|PCWC|           |
|16|           TABLE ACCESS FULL       |EMPLOYEE|107|749 |2 (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およびパーティショニング・ガイド』を参照してください。

4.4.1.3.3 適応問合せ計画: ビットマップ索引プルーニング

適応計画は、一致する行数がほとんど削減されない索引をプルーニングします。

オプティマイザがスター型変換計画を生成する場合、ビットマップ索引の適切な組合せを選択して、関連するROWIDのセットをできるだけ効率的に削減する必要があります。多くの索引が存在する場合、一部の索引ではそれほどROWIDセットが削減されないのに問合せ実行中の処理コストが大きくなる可能性があります。適応計画は、パフォーマンスを低下させる索引を使用しないことでこの問題を解決します。

例4-2 ビットマップ索引プルーニング

この例では、次のスター型問合せを発行し、これが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)

4.4.1.4 適応問合せ計画の有効化のタイミング

適応問合せ計画は、デフォルトで有効化されています。

適応計画は、次の初期化パラメータが設定されたときに有効になります。

• OPTIMIZER_ADAPTIVE_PLANSはTRUE (デフォルト)です

• OPTIMIZER_FEATURES_ENABLEが12.1.0.1以降

• OPTIMIZER_ADAPTIVE_REPORTING_ONLYはFALSE (デフォルト)です

適応計画は次の最適化を制御します。

• ネステッド・ループおよびハッシュ結合選択

• スター型変換ビットマップ・プルーニング

• 適応パラレル配分方法

関連項目:

• 「適応最適化の制御」

• OPTIMIZER_ADAPTIVE_PLANSについてさらに学習するには、『Oracle Databaseリファレンス』を参照してください

4.4.2 適応統計

最適化は、問合せ述語があまりにも複雑で実表のみを使用できない場合に適応統計を使用できます。デフォルトでは、適応統計は無効になっています(OPTIMIZER_ADAPTIVE_STATISTICSがfalse)。

4.4.2.1 動的統計

動的統計とは、述語のカーディナリティを見積もるために、データベースが再帰的SQL文を実行して表ブロックの小さいランダム・サンプルをスキャンする最適化手法です。

SQLのコンパイル時に、オプティマイザは、最適な実行計画を生成するために使用可能な統計が十分にあるかどうかを検討し、動的統計を使用するかどうかを判断します。使用可能な統計が不十分な場合、オプティマイザは動的統計を使用して統計を増強します。オプティマイザの決定の品質を向上させるために、オプティマイザでは、表スキャン、索引アクセス、結合およびGROUP BY操作の統計を使用できます。

4.4.2.2 自動再最適化

自動再最適化では、オプティマイザは最初の実行後に以降の実行の計画を変更します。

適応問合せ計画は、あらゆる種類の計画変更には適していません。たとえば、非効率な結合順序の問合せは、最適には実行されませんが、適応問合せ計画は、実行中の結合順序の適応をサポートしていません。SQL文の最初の実行の最後に、オプティマイザは実行時に収集された情報を使用して、自動再最適化にコスト上の利点があるかどうかを判断します。実行情報がオプティマイザの見積りと大幅に異なる場合、オプティマイザは次回実行時に置き換えるための計画を探します。

オプティマイザは、前回の実行時に収集された情報を使用して代替計画を決定します。オプティマイザは、問合せを複数回再最適化でき、そのたびに追加データを取集して計画をさらに改善します。

4.4.2.2.1 再最適化: 統計フィードバック

統計フィードバック(旧カーディナリティ・フィードバック)と呼ばれる再最適化の形式は、カーディナリティの見積りが誤っている繰返し問合せの計画を自動的に改善します。

オプティマイザは、統計がない、統計が不正確である、述語が複雑であるなど、多数の理由でカーディナリティを誤って見積ることがあります。統計フィードバックを使用した再最適化の基本プロセスは次のとおりです。

1. SQL文の最初の実行時に、オプティマイザが実行計画を生成します。

   オプティマイザは、次のような場合に、共有SQL領域の統計フィードバックの監視を有効化することがあります。

   • 表に統計がない場合

   • 接続フィルタ述語または選言フィルタ述語が表に複数ある場合

   • オプティマイザが選択性の見積りを正確に計算できないような複雑な演算子が述語に含まれる場合

2. 最初の実行の終了時に、オプティマイザは最初のカーディナリティの見積りと、実行時に計画の各操作により返された実際の行数を比較します。

   見積りが実際のカーディナリティと大きく異なる場合、オプティマイザは以降に使用するために正しい見積りを保存します。オプティマイザは、最初の実行時に取得された情報を他のSQL文が有効に利用できるようにSQL計画ディレクティブも作成します。

3. 問合せが再度実行された場合、オプティマイザは、その通常の見積りではなく、修正されたカーディナリティの見積りを使用します。

OPTIMIZER_ADAPTIVE_STATISTICS初期化パラメータは、自動再最適化のすべての機能を制御するわけではありません。具体的には、このパラメータは、自動再最適化のコンテキストでのみ結合カーディナリティの統計フィードバックを制御します。たとえば、OPTIMIZER_ADAPTIVE_STATISTICSをFALSEに設定すると、結合カーディナリティの誤った見積りについての統計フィードバックは無効化されますが、単一表カーディナリティの誤った見積りについての統計フィードバックは無効化されません。

例4-3 統計フィードバック

次の例では、データベースが統計フィードバックを使用して、誤った見積りを調整する仕組みを示します。

1. ユーザー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
   

2. カーソル内の計画を問い合せてみると、見積られた行数(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")

3. ユーザーoeはステップ1で問合せを返します。

4. カーソル内の計画を問い合せてみると、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)は、実際の行数と一致しています。

4.4.2.2.2 再最適化: パフォーマンス・フィードバック

再最適化の別の形式は、パフォーマンス・フィードバックです。この再最適化は、PARALLEL_DEGREE_POLICYがADAPTIVEに設定されている場合に、繰返しSQL文の自動的に選択された並列度を改善するために役立ちます。

パフォーマンス・フィードバックを使用した再最適化の基本プロセスは次のとおりです。

1. SQL文の最初の実行時に、PARALLEL_DEGREE_POLICYがADAPTIVEに設定されていると、オプティマイザはその文をパラレルに実行するかどうか、また、実行する場合はどの程度の並列度を使用するかを決定します。

   オプティマイザは、文の推定パフォーマンスに基づいて並列度を選択します。すべての文に対して追加のパフォーマンス監視が有効になります。

2. 最初の実行の最後に、オプティマイザは次のことを比較します。

   • オプティマイザによって選択された並列度

   • 文の実際の実行時に収集されたパフォーマンス統計(CPU時間など)に基づいて計算された並列度

   2つの値が大幅に異なる場合、その文は再解析の対象としてマークされ、最初の実行統計がフィードバックとして保存されます。このフィードバックは、以降の実行で並列度の計算精度を向上させるのに役立ちます。

3. 問合せが再度実行された場合、オプティマイザは、最初の実行時に収集されたパフォーマンス統計を使用して、より適切な文の並列度を決定します。

ノート:

PARALLEL_DEGREE_POLICYがADAPTIVEに設定されていない場合でも、統計フィードバックが文に選択された並列度に影響する場合があります。

4.4.2.3 SQL計画ディレクティブ

SQL計画ディレクティブは、オプティマイザがより最適な計画を生成するために使用する追加情報です。

ディレクティブは、特定のタイプの述語のカーディナリティ予測が誤っていることを示すオプティマイザによるノートであり、今後の予測の誤りを修正するために必要な統計を収集するためのDBMS_STATSのリマインダです。

たとえば、問合せ最適化中に、表が動的統計の候補であるかどうかを判断する場合、統計リポジトリに対して表のディレクティブが問い合されます。問合せで結合列にデータの偏りがある2つの表を結合する場合、SQL計画ディレクティブでは、動的統計を使用して正確なカーディナリティの見積りを取得するようオプティマイザに指示できます。

オプティマイザは、ディレクティブを複数のSQL文に適用できるように、文レベルではなく問合せ式のSQL計画ディレクティブを収集します。問合せおよび類似した問合せが共有プールからフラッシュされた後でもデータベースで見積りを引き続き修正できるように、オプティマイザはそれ自体で修正を行うのみでなく、誤りに関する情報も記録します。

データベースは自動的に、ディレクティブを作成し、それらをSYSAUX表領域に格納します。PL/SQLパッケージDBMS_SPDを使用して、ディレクティブを変更し、ディスクに保存し、転送できます。

関連項目:

• 「SQL計画ディレクティブ」

• 「SQL計画ディレクティブの管理」

• DBMS_SPDパッケージについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください

4.4.2.4 適応統計の有効化のタイミング

適応統計は、デフォルトで無効化されています。

適応統計は、次の初期化パラメータが設定されたときに有効になります。

• OPTIMIZER_ADAPTIVE_STATISTICSはTRUE (デフォルトはFALSE)です

• OPTIMIZER_FEATURES_ENABLEが12.1.0.1以降

OPTIMIZER_ADAPTIVE_STATISTICSをTRUEに設定すると、次の機能が有効化されます。

• SQL計画ディレクティブ

• 結合カーディナリティの統計フィードバック

• 適応動的サンプリング

ノート:

OPTIMIZER_ADAPTIVE_STATISTICSをFALSEに設定すると、単一表カーディナリティの誤った見積りについての統計フィードバックが保持されます。

関連項目:

• 「適応最適化の制御」

• OPTIMIZER_ADAPTIVE_STATISTICSについてさらに学習するには、『Oracle Databaseリファレンス』を参照してください

4.5 近似問合せ処理について

近似問合せ処理は、許容可能な範囲のエラーで結果を計算して分析の問合せを高速化する一連の最適化手法です。

ビジネス・インテリジェンス(BI)問合せは、COUNT DISTINCT、SUM、RANK、MEDIANなどの集計関数を含むソートに大きく依存します。たとえば、アプリケーションでは、ログオンする個別の顧客数または先週最もポピュラーだった製品を示すレポートを生成します。BIアプリケーションが次の要件を持つのはよくあることです。

• 問合せは、従来のデータ・ウェアハウスよりも桁違いに大きいデータ・セットを処理できる必要があります。

  たとえば、ポピュラーなWebサイトのWebログの毎日の量は、1日で数十または数百TBに達する可能性があります。

• 問合せでは、ほぼリアルタイムのレスポンスを提供する必要があります。

  たとえば、企業では、クレジット・カード詐欺の迅速な検出およびレスポンスを必要とします。

• 大きいデータ・セットの調査問合せは、高速である必要があります。

  たとえば、ユーザーは、販売が特定のしきい値にほとんど達している部署のリストを検索できます。ユーザーは、正確な販売数、これらの部署の場所などの詳細情報を検索するために、これらの部署の対象の問合せを作成します。

大きいデータ・セットでは、正確な集計問合せはメモリーを非常に消費するため、多くの場合に一時領域にオーバーフローし、許容できないほど遅くなる可能性があります。アプリケーションは、多くの場合、正確な結果よりも一般的なパターンのほうに関心があるため、顧客は速度のために正確さを犠牲にすることに前向きです。たとえば、最もポピュラーな製品を示す棒グラフを表示することが目標の場合、製品を1百万ユニット販売したのか、.999百万個販売したのかは統計的に重要ではありません。

Oracle Databaseでは、近似問合せ処理を使用してソリューションを実装します。通常、近似集計の正確性は97%(95%の信頼度)以上ですが、処理時間は桁違いに高速になります。データベースでCPUの使用量が減り、一時ファイルへの書込みのI/Oコストを回避できます。

関連項目:

NDVアルゴリズム: 適応サンプリングおよびHyperLogLog

4.5.1 近似問合せの初期化パラメータ

APPROX_FOR_*初期化パラメータを使用することで、既存のコードを変更せずに近似問合せ処理を実装できます。

これらのパラメータは、データベース・レベルまたはセッション・レベルで設定します。次の表では、近似手法に関連する初期化パラメータとSQLファンクションについて説明しています。

表4-2 近似問合せの初期化パラメータ

初期化パラメータ	デフォルト	説明	関連項目
APPROX_FOR_AGGREGATION	FALSE	近似問合せ処理を有効(TRUE)または無効(FALSE)にします。このパラメータは、近似結果を返す関数の使用を有効にするためのアンブレラ・パラメータとして機能します。	『Oracle Databaseリファレンス』
APPROX_FOR_COUNT_DISTINCT	FALSE	COUNT(DISTINCT)をAPPROX_COUNT_DISTINCTに変換します。	『Oracle Databaseリファレンス』
APPROX_FOR_PERCENTILE	なし	対象の正確な割合ファンクションを同等のAPPROX_PERCENTILE_*に変換します。	『Oracle Databaseリファレンス』

関連項目:

• 「オプティマイザの初期化パラメータについて」

• 近似問合せ処理についてさらに学習するには、Oracle Databaseデータ・ウェアハウス・ガイドを参照してください

4.5.2 近似問合せSQLファンクション

近似問合せ処理では、SQL関数を使用して、近似処理が許容される探索的問合せに対してリアルタイムの応答を提供します。

次の表では、近似結果を返すSQL関数について説明します。

表4-3 近似問合せユーザー・インタフェース

SQL関数	説明	関連項目
APPROX_COUNT	APPROX_RANKファンクションで使用する場合、近似上位n個の最も一般的な値が計算されます。 式の近似カウントを返します。2番目の引数としてMAX_ERRORを指定した場合、このファンクションは、実績カウントと近似カウントの間の最大エラーを返します。 HAVING句で、対応するAPPROX_RANKファンクションとともにこのファンクションを使用する必要があります。問合せでAPPROX_COUNT、APPROX_SUM、APPROX_RANKを使用している場合、その問合せは他の非近似集計関数を使用する必要がありません。 次の問合せは、各部門内で共通する上位10件のジョブを返します。 SELECT department_id, job_id, APPROX_COUNT(*) FROM employees GROUP BY department_id, job_id HAVING APPROX_RANK ( PARTITION BY department_id ORDER BY APPROX_COUNT(*) DESC ) <= 10;	Oracle Database SQL言語リファレンス
APPROX_COUNT_DISTINCT	式の個別値を含む行の概数を戻します。	Oracle Database SQL言語リファレンス
APPROX_COUNT_DISTINCT_AGG	事前に計算された概数個別シノプシスを高いレベルで集計します。	Oracle Database SQL言語リファレンス
APPROX_COUNT_DISTINCT_DETAIL	BLOBとしてAPPROX_COUNT_DISTINCTファンクションのシノプシスを戻します。 データベースは、返された結果を、さらなる集計のためにディスクに保存できます。	Oracle Database SQL言語リファレンス
APPROX_MEDIAN	数値または日時値を受け入れ、近似の中央値、または値のソート後に近似の中央値となる近似の補間値を返します。 このファンクションは、MEDIANファンクションの代替機能を提供します。	Oracle Database SQL言語リファレンス
APPROX_PERCENTILE	このファンクションは、パーセンタイル値およびソート指定を受け入れ、そのソート指定に従ってそのパーセンタイル値に該当する補間された近似値を戻します。 このファンクションは、PERCENTILE_CONTファンクションの代替機能を提供します。	Oracle Database SQL言語リファレンス
APPROX_RANK	値のグループの近似値を返します。 このファンクションは、オプションのPARTITION BY句をとり、ORDER BY ... DESC句が続きます。PARTITION BYキーは、GROUP BYキーのサブセットである必要があります。ORDER BY句には、APPROX_COUNTかAPPROX_SUMのいずれかを含める必要があります。	Oracle Database SQL言語リファレンス
APPROX_SUM	APPROX_RANKファンクションで使用する場合、上位近似n個の累計値が計算されます。 2番目の引数としてMAX_ERRORを指定した場合、このファンクションは実際の合計と近似の合計との間の最大エラーを返します。 HAVING句で、対応するAPPROX_RANKファンクションとともにこのファンクションを使用する必要があります。問合せでAPPROX_COUNT、APPROX_SUM、APPROX_RANKを使用している場合、その問合せは他の非近似集計関数を使用する必要がありません。 次の問合せは、最上位の集約給与を保持する部門ごとに10個のジョブ・タイプを返します。 SELECT department_id, job_id, APPROX_SUM(salary) FROM employees GROUP BY department_id, job_id HAVING APPROX_RANK ( PARTITION BY department_id ORDER BY APPROX_SUM(salary) DESC ) <= 10; 入力値が負の数の場合、APPROX_SUMはエラーを返すことに注意してください。	Oracle Database SQL言語リファレンス

関連項目:

近似問合せ処理についてさらに学習するには、Oracle Databaseデータ・ウェアハウス・ガイドを参照してください

4.6 SQL計画管理について

SQL計画管理は、データベースで既知の計画または確認済の計画のみが使用されるようにオプティマイザで実行計画を自動的に管理できます。

SQL計画管理により、SQL計画ベースラインを構築できます。これには、各SQL文の承認済計画が1つ以上含まれます。オプティマイザは、SQL文の計画履歴およびSQL計画ベースラインにアクセスし、それらを管理できます。主要な目的は次のとおりです。

• 繰返し可能なSQL文の識別

• 一連のSQL文に関する計画履歴および場合によってはSQL計画ベースラインの保持

• 計画履歴に存在しない計画の検出

• SQL計画ベースラインに存在しない、潜在的により優れた計画の検出

オプティマイザは、通常のコストベースの検索方法を使用します。

関連項目:

• 「SQL計画ベースラインの管理」

• DBMS_SPMパッケージについて学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください

4.7 隔離されたSQL計画について

Oracle Databaseは、リソース制限を超えたためにOracle Database Resource Manager (Resource Manager)によって終了されたSQL文の計画を自動的に隔離します。

Resource Managerでは、SQL文の最大推定実行時間を設定できます(例: 20分)。文の実行がこの制限を超えると、リソース・マネージャはその文を終了します。ただし、文が終了する前に繰り返し実行される場合があり、実行されるたびに20分のリソースが浪費されます。

Oracle Database 19c以降では、指定したリソース制限を超えた文は、Resource Managerによって実行が終了され、その計画は「隔離」されます。計画の隔離とは、計画を計画のブラックリストに登録することです。その計画は、データベースで実行されなくなります。文自体ではなく計画が隔離される点に注意してください。

この例の問合せは、20分間1回のみ実行され、リソース制限の緩和や計画の変更がない場合、再度実行されることはありません。制限が25分に緩和されると、Resource Managerは隔離された計画による文の再度実行を許可します。文の実行に23分かかった場合、この実行時間は新しいしきい値を下回るため、その計画はResource Managerによって隔離から除外されます。文の実行に26分かかった場合、この実行時間は新しいしきい値を上回ため、その計画は制限が緩和されるまで隔離されたままになります。

V$SQL.SQL_QUARANTINE列は、Resource Managerが実行を取り消した後に、文の計画が隔離されたかどうかを示します。AVOIDED_EXECUTIONS列は、Oracle Databaseが隔離された計画で文を実行できなかった回数を示します。

DBMS_SQLQ PL/SQLパッケージには、隔離情報をただちにディスクに保存したり、隔離される計画の構成オプション(計画の隔離を強制する)を設定したり、構成オプションを削除したりすることもできるプログラム・ユニットがあります。たとえば、それぞれのSQL文ごとに、単一の計画またはすべての計画を隔離するように指定できます。隔離されるプランに対する特定のしきい値を構成できます。たとえば、CPU時間のしきい値を10秒にしたり、I/Oリクエストのしきい値を削除したりできます。

関連項目:

• Resource Managerによる計画の隔離方法について学習するには、『Oracle Database管理者ガイド』を参照

• DBMS_SQLQについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照

• V$SQLについて学習するには、『Oracle Databaseリファレンス』を参照してください

• 各種エディションおよびサービスでサポートされる機能の詳細は、『Oracle Databaseライセンス情報ユーザー・マニュアル』を参照

4.8 式の統計ストア(ESS)について

式の統計ストア(ESS)は、式の評価に関する統計を格納するためにオプティマイザにより維持されるリポジトリです。

IM列ストアが有効化されると、データベースはインメモリー式(IM式)機能のためにESSを活用します。ただし、ESSはIM列ストアとは独立しています。ESSはデータベースの永続コンポーネントで、無効化できません。

データベースでは、ESSを使用して、式がホット(頻繁にアクセスされている)かどうか、ひいてはIM式の候補が判断されます。問合せのハード解析の間には、ESSにより、SELECTリスト、WHERE句、GROUP BY句などに含まれているアクティブな式が探されます。

各セグメントに対して、ESSは次のような式統計を保持します。

• 実行の頻度

• 評価のコスト

• タイムスタンプ評価

オプティマイザは、各式に、コストと評価回数に基づいた加重スコアを割り当てます。この値は、正確な値ではなく近似値です。アクティブな式ほど高いスコアになります。ESSは、最も頻繁にアクセスされる式の内部リストを保持します。

ESSはSGAにあり、ディスクにも保持されます。データベースは、DBMS_STATS.FLUSH_DATABASE_MONITORING_INFOプロシージャを使用して、15分ごとに、または即座に統計をディスクに保存します。ESS統計は、DBA_EXPRESSION_STATISTICSビューで参照できます。

関連項目:

• ESSについてさらに学習するには、Oracle Database In-Memoryガイドを参照してください

• DBMS_STATS.FLUSH_DATABASE_MONITORING_INFOについてさらに学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください