オプティマイザへの影響

19 オプティマイザへの影響

オプティマイザのデフォルト設定は、ほとんどの操作に適しています(すべてではありません)。

オプティマイザにとって不明な情報が含まれている場合や、特定の種類の文またはワークロードにオプティマイザを合せる必要がある場合もあります。そのような場合は、オプティマイザに与える影響によりパフォーマンスを改善できることがあります。

19.1 オプティマイザに影響を与える手法

SQLプロファイル、SQL計画管理、初期化パラメータおよびヒントなど、様々な手法を使用してオプティマイザに影響を与えることができます。

次の図は、オプティマイザに影響を与える主要な手法を示しています。

図19-1 オプティマイザに影響を与える手法

「図19-1 オプティマイザに影響を与える手法」の説明

前述の図にある重なり合った四角形は、SQL計画管理が初期化パラメータとヒントの両方を使用していることを示しています。また、SQLプロファイルにも技術的にはヒントが含まれています。

ノート:

ストアド・アウトラインは、SQL計画ベースラインとよく似た目的を果たすレガシーな手法です。

次の手法を使用してオプティマイザに影響を与えることができます。

表19-1 オプティマイザの手法

手法	説明	さらに学習するには
初期化パラメータ	パラメータは、オプティマイザの多くの種類の動作にデータベース・インスタンスやセッション・レベルで影響を与えます。	「初期化パラメータによるオプティマイザへの影響」
ヒント	ヒントは、SQL文でコメント化された命令です。ヒントで広範囲の動作が制御されます。	「ヒントによるオプティマイザへの影響」
`DBMS_STATS`	このパッケージではオプティマイザ統計を更新および管理します。統計をより正確にすることで、オプティマイザの見積りもより改善されます。この章では、`DBMS_STATS`については扱いません。	「オプティマイザ統計の収集」
SQLプロファイル	SQLプロファイルは、SQL文に固有の補足統計を含むデータベース・オブジェクトです。概念上、SQL文に対するSQLプロファイルの役割は、表または索引に対するオブジェクト・レベルの統計情報の役割と同じです。SQLプロファイルにより、SQLチューニングの間に見つかった最適ではないオプティマイザの見積りを修正できます。	「SQLプロファイルの管理」
SQL計画管理とストアド・アウトライン	SQL計画管理は、データベースで既知の計画または確認済の計画のみが使用されるようにオプティマイザで実行計画を自動的に管理する予防的なメカニズムです。この章では、SQL計画管理については扱いません。	「SQL計画ベースラインの管理」

場合によっては、複数の手法で同じ動作を最適化します。たとえば、初期化パラメータとヒントの両方を使用してオプティマイザの目標を設定できます。

関連項目:

ストアド・アウトラインをSQL計画ベースラインに移行する方法を学習するには、「ストアド・アウトラインのSQL計画ベースラインへの移行」を参照してください

19.2 初期化パラメータによるオプティマイザへの影響

この章では、最適化に影響を与える初期化パラメータと、その設定方法について説明しています。

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

Oracle Databaseには、カーソル共有、適応最適化およびオプティマイザ・モードを含めてオプティマイザ動作の様々な面に影響を与える初期化パラメータが提供されています。

次の表に、最も重要なオプティマイザ・パラメータの一部を示します。この表には、近似問合せ初期化パラメータが含まれていないことに注意してください。近似問合せの初期化パラメータで説明しています。

表19-2 オプティマイザ動作を制御する初期化パラメータ

初期化パラメータ	説明
`CURSOR_INVALIDATION`	DDL文のデフォルトのカーソル無効化レベルを指定します。 `IMMEDIATE`は、DDLについてOracle Database 12cリリース2 (12.2)より前のリリースと同じカーソル無効化動作を設定します。これはデフォルトです。 `DEFERRED`により、アプリケーションではアプリケーションを変更せずにDDLの削減されたカーソル無効化を利用できます。遅延した無効化は、カーソル無効化の数を削減し、長期にわたって再コンパイル・ワークロードを拡散します。このため、再コンパイルされるまで最適ではない計画でカーソルが実行され、少量の実行時間のオーバーヘッドが発生する場合があります。このパラメータは、`SYSTEM`レベルまたは`SESSION`レベルで設定できます。「共有カーソルのライフサイクルについて」を参照してください。
`CURSOR_SHARING`	SQL文のリテラル値をバインド変数に変換します。値を変換するとカーソル共有が改善され、SQL文の実行計画は影響を受けます。オプティマイザは、実際のリテラル値でなくバインド変数の有無に基づいて実行計画を生成します。 `FORCE`を設定することで、既存のカーソルの共有時、またはカーソル・プランが最適ではない場合に新しいカーソルを作成できます。`EXACT`に設定すると、同一のテキストを含む文のみで同じカーソルを共有できます。
`DB_FILE_MULTIBLOCK_READ_COUNT`	全表スキャンまたは高速全索引スキャン時に単一I/Oで読み取られるブロックの個数を指定します。オプティマイザは、このパラメータの値を使用して、全表スキャンと高速全索引スキャンのコストを計算します。値が大きいほど全表スキャンのコストは低くなり、結果として、オプティマイザは索引スキャンより全表スキャンを選択することになります。このパラメータのデフォルト値は、データベースを効果的に実行できる最大I/Oサイズに対応しています。この値は、プラットフォームに依存し、ほとんどのプラットフォームで1MBになります。このパラメータはブロック単位で表されるため、効率的に実行できる最大I/Oサイズを標準ブロック・サイズで除算した値に設定されます。セッション数が非常に大きい場合は、表スキャン・バッファが多すぎてバッファ・キャッシュがあふれないように、マルチブロック読取りカウントの値が減らされます。
`OPTIMIZER_ADAPTIVE_PLANS`	適応計画を制御します。適応計画には代替の選択肢があります。オプティマイザは、問合せの実行時に収集された統計に基づいて、実行時に計画を決定します。デフォルトでは、このパラメータは`true`で、適応計画が有効なことを意味します。このパラメータを`false`に設定すると、次の機能が無効になります。ネステッド・ループおよびハッシュ結合選択スター型変換ビットマップ・プルーニング適応パラレル配分方法適応問合せ計画についてを参照してください。
`OPTIMIZER_ADAPTIVE_REPORTING_ONLY`	自動再最適化と適応計画のレポート作成モードを制御します(適応問合せ計画を参照)。デフォルトでは、レポート作成モードはオフ(`false`)のため、適応最適化が有効になっています。 `true`に設定した場合は、レポート作成のみのモードで適応最適化が実行されます。この場合は、データベースで適応最適化に必要な情報が収集されるものの、計画変更のアクションは発生しません。たとえば、適応計画では常にデフォルトの計画が選択されますが、パラメータが`false`に設定されている場合は、データベースで使用する計画に関する情報が収集されます。`DBMS_XPLAN.DISPLAY_CURSOR`を使用することでレポートを表示できます。
`OPTIMIZER_ADAPTIVE_STATISTICS`	適応統計を制御します。最適化は、問合せ述語があまりにも複雑で実表のみを使用できない場合に適応統計を使用できます。デフォルトでは、`OPTIMIZER_ADAPTIVE_STATISTICS`は`false`で、次の機能が無効なことを意味します。 SQL計画ディレクティブ統計フィードバック適応動的サンプリング「適応統計」を参照してください。
`OPTIMIZER_MODE`	データベース・インスタンスの起動時にオプティマイザ・モードを設定します。`ALL_ROWS`、`FIRST_ROWS_n`および`FIRST_ROWS`の値が使用可能です。
`OPTIMIZER_INDEX_CACHING`	ネステッド・ループとともに索引プローブのコスト分析を制御します。値`0`-`100`の範囲はバッファ・キャッシュ内の索引ブロックの割合を示し、これにより、ネステッド・ループおよびINリスト・イテレータの索引キャッシュに関するオプティマイザの前提条件が変更されます。この値が`100`の場合は、索引ブロックの100%がバッファ・キャッシュに見つかる可能性があるため、それに応じてオプティマイザでは索引プローブまたはネステッド・ループのコストを調整します。実行計画は索引キャッシュを優先して変更される可能性があるため、このパラメータの設定時には注意を払います。
`OPTIMIZER_INDEX_COST_ADJ`	索引プローブのコストを調整します。値の範囲は、`1`から`10000`です。デフォルト値`100`の場合、オプティマイザは、標準のコスト・モデルに基づいて索引をアクセス・パスとして評価します。値`10`は、索引アクセス・パスの標準コストの1/10が索引アクセス・パスのコストであることを意味します。
`OPTIMIZER_INMEMORY_AWARE`	このパラメータでは、IM列ストア、表拡張、ブルーム・フィルタなどのコスト・モデルを含むすべてのOracle Database In-Memory (Database In-Memory)オプティマイザ機能を有効(`TRUE`)または無効(`FALSE`)にします。パラメータを`FALSE`に設定すると、SQL文の最適化の際に、オプティマイザは表の`INMEMORY`プロパティを無視するようになります。
`OPTIMIZER_REAL_TIME_STATISTICS`	`OPTIMIZER_REAL_TIME_STATISTICS`初期化パラメータに`true`が設定されている場合は、Oracle Databaseで従来のDML操作中にリアルタイム統計が自動的に収集されます。デフォルトの設定は`false`で、リアルタイム統計が無効になることを意味します。
`OPTIMIZER_SESSION_TYPE`	データベースで自動索引検証中に文を検証するかどうかを指定します。デフォルトは`NORMAL`で、文が検証されることを意味します。`CRITICAL`は`NORMAL`よりも優先されます。セッションで`OPTIMIZER_SESSION_TYPE`初期化パラメータに`ADHOC`を設定すると、このセッションで問合せに対する自動索引付けを一時停止できます。自動索引付け処理では、索引候補が識別されないか、索引が作成および検証されません。この制御は、非定型問合せまたは新しい機能のテストに役立つ場合があります。
`OPTIMIZER_USE_INVISIBLE_INDEXES`	非表示の索引の使用を有効化または無効化します。
`QUERY_REWRITE_ENABLED`	オプティマイザのクエリー・リライト機能を有効または無効にします。 `TRUE` (デフォルト)に設定すると、オプティマイザはパフォーマンスを改善するためにマテリアライズド・ビューを利用できるようになります。`FALSE`に設定すると、オプティマイザのクエリー・リライト機能が無効になり、最適化されていない問合せの見積り問合せコストの方が低くなる場合にも、マテリアライズド・ビューを使用した問合せのリライトが行われません。`FORCE`に設定すると、オプティマイザのクエリー・リライト機能が有効になり、最適化されていない問合せの見積り問合せコストの方が低くなる場合にも、マテリアライズド・ビューを使用した問合せのリライトが行われます。
`QUERY_REWRITE_INTEGRITY`	クエリー・リライトを適用する程度を決定します。デフォルトでは、整合性レベルは`ENFORCED`に設定されます。このモードでは、すべての制約の妥当性チェックを行う必要があります。データベースでは、強制されない制約に依存するクエリー・リライト変換は使用されません。そのため、`ENABLE NOVALIDATE RELY`を使用すると、一部のタイプのクエリー・リライトが動作しないことがあります。制約が`NOVALIDATE`モードのときにクエリー・リライトを有効にするには、整合性レベルを`TRUSTED`または`STALE_TOLERATED`にする必要があります。`TRUSTED`モードの場合、オプティマイザでは、ディメンションで宣言された関係および`RELY`制約が適切であることが信頼の対象になります。`STALE_TOLERATED`モードの場合、オプティマイザでは最新データを含むマテリアライズド・ビューの他に、有効だが失効データを含むマテリアライズド・ビューも使用されます。このモードでは、リライト機能を最大限に使用できますが、不正確な結果が生成される危険性もあります。
`RESULT_CACHE_MODE`	データベースで使用するSQL問合せ結果キャッシュの対象を、すべての問合せにするのか、または結果キャッシュ・ヒントで注釈付けされた問合せのみにするのかを制御します。`MANUAL`(デフォルト)に設定する場合は、`RESULT_CACHE`ヒントを使用して、特定の結果がキャッシュ内に格納されるように指定する必要があります。`FORCE`に設定すると、すべての結果がキャッシュに格納されます。このパラメータを設定する場合は、結果キャッシュでPL/SQLファンクションを処理する方法について検討します。データベースでは、PL/SQLファンクションのデータ依存性を追跡するのと同じメカニズムを使用して結果キャッシュ内の問合せ結果が無効化されますが、それ以外の場合は、PL/SQLファンクションを含む問合せをキャッシュできます。PL/SQLファンクション結果キャッシュの無効化では全種類の依存性(順序、`SYSDATE`、`SYS_CONTEXT`およびパッケージ変数など)が追跡されるわけではないため、このようなファンクションをコールする問合せで無差別に問合せ結果キャッシュを使用すると、結果が変更され、間違った結果になる可能性があります。したがって、結果キャッシュの有効化を選択する場合、特に`RESULT_CACHE_MODE`を`FORCE`に設定する場合は正当性やパフォーマンスを考慮に入れます。
`RESULT_CACHE_MAX_SIZE`	結果キャッシュに割り当てられているメモリーを変更します。このパラメータを`0`に設定すると、結果キャッシュが無効化されます。このパラメータの値は、指定された値を超えない範囲で32KBの最大倍数まで丸められます。丸め値が`0`の場合はこの機能が無効化されます。
`RESULT_CACHE_MAX_RESULT`	すべての単一の結果で使用可能なキャッシュ・メモリーの最大値を指定します。デフォルト値は5%ですが、`1`から`100`の任意のパーセント値を指定できます。
`RESULT_CACHE_REMOTE_EXPIRATION`	リモート・データベース・オブジェクトに依存する結果の有効時間(分単位)を指定します。デフォルトは`0`です。これは、データベースで、リモート・オブジェクトを使用して結果がキャッシュされないことを示します。このパラメータを0以外の値にすると、結果内で参照される表をリモート・データベースが変更した場合などに、失効した回答が生成される可能性があります。
`STAR_TRANSFORMATION_ENABLED`	オプティマイザはスター・クエリーのスター型変換のコストを計算できます(`true`の場合)。スター型変換により、様々なファクト表の列でビットマップ索引が結合されます。

関連項目:

前述の初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください
問合せ結果キャッシュのチューニング方法を学習するには、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください
Oracle Databaseデータウェアハウス・ガイド
スター型変換およびクエリー・リライトの詳細についてさらに学習します
Database In-Memory機能についてさらに学習するには、Oracle Database In-Memoryガイドを参照してください

19.2.2 オプティマイザ機能の有効化

OPTIMIZER_FEATURES_ENABLE初期化パラメータ(またはヒント)は一連のオプティマイザ関連機能をデータベース・リリースに応じて制御します。

パラメータによって、11.2.0.2または12.2.0.1など、リリース番号に対応する有効な文字列値のいずれかを受け入れます。このパラメータを使用して、データベースのアップグレード後もオプティマイザの以前の動作を保持できます。たとえば、Oracle Database 12cリリース1 (12.1.0.2)からOracle Database 12cリリース2 (12.2.0.1)にアップグレードすると、OPTIMIZER_FEATURES_ENABLEパラメータのデフォルト値は12.1.0.2から12.2.0.1に変更されます。

下位互換性のため、新規リリースの新しいオプティマイザ機能という理由で実行計画の変更を必要とはしない場合もあります。そのような場合は、OPTIMIZER_FEATURES_ENABLEを以前のバージョンに設定できます。新しいリリースにアップグレードする場合や、新しいリリースの機能を有効化する必要がある場合は、OPTIMIZER_FEATURES_ENABLE初期化パラメータを明示的に設定する必要はありません。

注意:

OPTIMIZER_FEATURES_ENABLE初期化パラメータを以前のリリースに明示的に設定することはお薦めしません。実行計画の変更から生じる可能性があるSQLパフォーマンスの低下を回避するには、かわりに、SQL計画管理の使用を検討してください。

前提条件

このチュートリアルでは、次のことが前提となっています。

最近、データベースをOracle Database 12cリリース1 (12 1.0.2)からOracle Database 12cリリース2 (12.2.0.1)にアップグレードしました。
以前のリリースのオプティマイザ動作を保持したいと思っています。

特定のリリースの問合せオプティマイザ機能を有効化するには:

適切な権限でデータベースにログインし、現在のオプティマイザ機能の設定を問い合せます。

たとえば、次のSQL*Plusコマンドを実行します。

SQL> SHOW PARAMETER optimizer_features_enable
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
optimizer_features_enable            string      12.2.0.1

インスタンスまたはセッション・レベルでオプティマイザ機能設定を設定します。

たとえば、次のSQL文を実行してオプティマイザ・バージョンを12.1.0.2に設定します。
```
SQL> ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE='12.1.0.2';
```
前述の文では、Oracle Database 12cリリース1 (12.1.0.2)に存在するオプティマイザ機能がリストアされます。

関連項目:

「SQL計画ベースラインの管理」
OPTIMIZER_FEATURES_ENABLEを異なるリリースの値に設定する際に有効化されるオプティマイザ機能について学習するには、Oracle Databaseリファレンスを参照してください

19.2.3 オプティマイザの目標の選択

オプティマイザの目標は、オプティマイザによるリソース使用を優先順位付けしたものです。

OPTIMIZER_MODE初期化パラメータを使用して、次のオプティマイザの目標を設定できます。

最高のスループット(デフォルト)

OPTIMIZER_MODE値をALL_ROWSに設定すると、データベースでは、文がアクセスしたすべての行の処理に必要な最小限のリソース量が使用されます。

Oracle Reportsなどのバッチ・アプリケーションでは、最高のスループットを目標に最適化します。通常、バッチ・アプリケーションでスループットがより重要になるのは、ユーザーが、アプリケーションの完了に必要な時間にのみ関心を持っているためです。アプリケーションの実行中に、ユーザーは個々の文の結果を検証しないため、レスポンス時間はそれほど重要ではありません。
最短のレスポンス時間

OPTIMIZER_MODE値をFIRST_ROWS_nに設定すると、データベースでは、最初のn行を戻す最短のレスポンス時間を目標に最適化されます。ここでのnは1、10、100または1000です。

Oracle FormsまたはSQL*Plusの対話型アプリケーションでは、レスポンス時間に対して最適化します。通常、レスポンス時間が重要なのは、文がアクセスした最初の行または数行を確認するために対話型ユーザーが待機しているためです。

前提条件

このチュートリアルでは、次のことが前提となっています。

プライマリ・アプリケーションが対話型であるため、データベース・インスタンスにオプティマイザの目標を設定してレスポンス時間を最小化します。
現行のセッションの場合のみ、レポート作成を実行し、スループットに対して最適化します。

特定のリリースの問合せオプティマイザ機能を有効化するには:

適切な権限でSQL*Plusをデータベースに接続し、現行のオプティマイザ・モードを問い合せます。

たとえば、次のSQL*Plusコマンドを実行します。

dba1@PROD> SHOW PARAMETER OPTIMIZER_MODE

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
optimizer_mode                       string      ALL_ROWS

インスタンス・レベルで、レスポンス時間に対して最適化します。

たとえば、次のSQL文を実行して、最初の10行をできるかぎり迅速に取得するようにシステムを構成します。
```
SQL> ALTER SYSTEM SET OPTIMIZER_MODE='FIRST_ROWS_10';
```
セッション・レベルでのみ、レポート作成を実行する前にスループットに対して最適化します。

たとえば、次のSQL文を実行して、スループットに対して最適化するようにこのセッションのみを構成します。
```
SQL> ALTER SESSION SET OPTIMIZER_MODE='ALL_ROWS';
```

関連項目:

OPTIMIZER_MODE初期化パラメータについて学習するには、『Oracle Databaseリファレンス』を参照してください

19.2.4 適応最適化の制御

Oracle Databaseでは、適応問合せ最適化が、実行時に収集された統計に基づいてオプティマイザで実行計画を採用するためのプロセスです。

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

OPTIMIZER_ADAPTIVE_PLANSはTRUE(デフォルト)
OPTIMIZER_FEATURES_ENABLEが12.1.0.1以降
OPTIMIZER_ADAPTIVE_REPORTING_ONLYはFALSE(デフォルト)

OPTIMIZER_ADAPTIVE_REPORTING_ONLYがtrueに設定されている場合は、レポート作成のみのモードで適応最適化が実行されます。この場合は、データベースで適応最適化に必要な情報が収集されるものの、計画は変更されません。適応計画では常にデフォルトの計画が選択されますが、パラメータがfalseに設定されているかのように実行に関する情報がデータベースで収集されます。

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

OPTIMIZER_ADAPTIVE_STATISTICSはTRUE(デフォルトはFALSE)
OPTIMIZER_FEATURES_ENABLEが12.1.0.1以降

前提条件

このチュートリアルでは、次のことが前提となっています。

OPTIMIZER_FEATURES_ENABLE初期化パラメータが12.1.0.1以降に設定されている。
OPTIMIZER_ADAPTIVE_REPORTING_ONLY初期化パラメータがfalse(デフォルト)に設定されている。
テスト目的で適応計画を無効化し、データベースでレポートのみが生成されるようにします。

適応計画を無効化するには:

SYSTEMとしてSQL*Plusをデータベースに接続し、現行の設定を問い合せます。

たとえば、次のSQL*Plusコマンドを実行します。

SHOW PARAMETER OPTIMIZER_ADAPTIVE_REPORTING_ONLY

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
optimizer_adaptive_reporting_only    boolean     FALSE

セッション・レベルで、OPTIMIZER_ADAPTIVE_REPORTING_ONLY初期化パラメータをtrueに設定します。

たとえば、SQL*Plusで次のSQL文を実行します。
```
ALTER SESSION SET OPTIMIZER_ADAPTIVE_REPORTING_ONLY=true;
```
問合せを実行します。
DBMS_XPLAN.DISPLAY_CURSORを+REPORTパラメータで実行します。

+REPORTパラメータが設定されると、自動最適化が有効になっている場合、オプティマイザが選択する計画がレポートに表示されます。

関連項目:

適応問合せ最適化について
OPTIMIZER_ADAPTIVE_REPORTING_ONLY初期化パラメータについて学習するには、『Oracle Databaseリファレンス』を参照してください
DBMS_XPLAN.DISPLAY_CURSORのファンクションの+REPORTパラメータについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

19.3 ヒントによるオプティマイザへの影響

オプティマイザ・ヒントは、命令をオプティマイザに渡すSQL文の特別なコメントです。

オプティマイザは、なんらかの状況に妨害されないかぎり、ヒントを使用して文の実行計画を選択します。

ノート:

すべてのSQLヒントの完全な詳細は、『Oracle Database SQL言語リファレンス』を参照してください

19.3.1 オプティマイザ・ヒントについて

ヒントはSQLコメント内に埋め込まれます。

ヒント・コメントは、SQL文ブロックの最初のキーワードのすぐ後ろに続ける必要があります。スラッシュスター(/*)またはダッシュのペア(--)のいずれかのコメント・スタイルを使用できます。プラス記号(+)のヒント・デリミタは、次のフラグメントのように、プラス記号の前にスペースを入れずに、コメント・デリミタの直後に続ける必要があります。

SELECT /*+ hint_text */ ...

プラス記号の後ろのスペースは任意です。文ブロックでは、ヒントを含むコメントは1つしか持つことができませんが、空白区切りのヒントは多数含むことができます。次の文に示すように、複数のヒントは1つ以上のスペースで区切ります。

SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name FROM employees hr_emp;

19.3.1.1 ヒントの目的

ヒントを使用すると、通常はオプティマイザによって行われる決定をユーザーが決定できます。

ヒントの使用によって、オプティマイザ・モード、問合せの転送、アクセス・パス、結合順序および結合方法に影響を与えることができます。テスト環境では、特定のアクセス・パスのパフォーマンスをテストする場合にヒントが役に立ちます。たとえば、特定の問合せには索引を選択する方が適切な計画が得られるとわかっていることがあります。次の図は、ヒントを使用してオプティマイザに特定の索引を使用するよう指示する方法を示しています。

図19-2 オプティマイザ・ヒント

「図19-2 オプティマイザ・ヒント」の説明

ヒントの短所は、管理、チェックおよび制御のためにコードが増えることです。ヒントが導入されたのはOracle7からですが、当時は、オプティマイザによって生成された計画が最善ではない場合にユーザーが取れる手段はほとんどありませんでした。データベースやホスト環境内の変更によってヒントが非推奨となるか、負の結果をもたらす可能性があるため、ヒントを使用したテストが望ましいものの、他の手法を使用して実行計画を管理することがあります。

オプティマイザでは解決されないパフォーマンスの問題に対処するために、SQLチューニング・アドバイザ、SQL計画管理、SQLパフォーマンス・アナライザなどの複数のツールが用意されています。これらのツールにはデータやデータベース環境の変更に対応した新しいソリューションが用意されているため、ヒントのかわりにこれらを使用することを強くお薦めします。

19.3.1.2 ヒントの型

ヒントは、表、問合せブロックおよび文に使用できます。

ヒントは次の型に分類されます。

単一表

単一表ヒントは、1つの表またはビュー上で指定します。単一表ヒントの例として、INDEXおよびUSE_NLがあります。次の文では単一表ヒントが使用されています。
```
SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_id
FROM   employees 
WHERE  department_id > 50;
```
マルチ表

マルチ表ヒントは単一表ヒントと同様のものですが、ヒントで複数の表またはビューを指定できる点が異なります。マルチ表ヒントの例として、LEADINGが挙げられます。次の文ではマルチ表ヒントを使用しています。
```
SELECT /*+ LEADING(e j) */ *
FROM   employees e, departments d, job_history j
WHERE  e.department_id = d.department_id
AND    e.hire_date = j.start_date;
```
ノート:

USE_NL(table1 table2)は、マルチ表ヒントとはみなされません。これは、USE_NL(table1)とUSE_NL(table2)のショートカットであるためです。
問合せブロック

問合せブロック・ヒントでは、単一の問合せブロックが処理されます。問合せブロック・ヒントの例として、STAR_TRANSFORMATIONおよびUNNESTがあります。次の文では、問合せブロック・ヒントを使用して、FULLヒントがemployeesを参照する問合せブロックにのみ適用されることを指定しています。
```
SELECT /*+ INDEX(t1) FULL(@sel$2 t1) */ COUNT(*)
FROM   jobs t1
WHERE t1.job_id IN (SELECT job_id FROM employees t1);
```
文

文ヒントはSQL文全体に適用されます。文ヒントの例には、ALL_ROWSがあります。次の文では文ヒントが使用されています。
```
SELECT /*+ ALL_ROWS */ * FROM sales;
```

関連項目:

機能カテゴリ別の最も一般的なヒントは、『Oracle Database SQL言語リファレンス』を参照してください。

19.3.1.3 ヒントの有効範囲

文ブロックでヒントを指定すると、そのヒントは文ブロック内の適切な問合せブロック、表または文全体に適用されます。ヒントは、インスタンス・レベルまたはセッション・レベルのどのパラメータよりも優先されます。

文ブロックは次のいずれかです。

単純なMERGE、SELECT、INSERT、UPDATEまたはDELETE文
複合文の親文または副問合せ
集合演算子(UNION、MINUS、INTERSECT)を使用した問合せの一部

例19-1 集合演算子を使用した問合せ

次の問合せは、2つのコンポーネント問合せとUNION演算子で構成されます。

SELECT /*+ FIRST_ROWS(10) */ prod_id, time_id FROM 2010_sales
UNION ALL
SELECT /*+ ALL_ROWS */ prod_id, time_id FROM current_year_sales;

前述の文には2つのブロックがあり、それぞれが各コンポーネント問合せに対応しています。最初のコンポーネント問合せのヒントはその最適化のみに適用され、2番目のコンポーネント問合せの最適化には適用されません。たとえば、2015年の第1週に現行年度と昨年度の売上を問い合せるとします。昨年度(2014)の売上の問合せにはFIRST_ROWS(10)を適用し、今年度(2015)の売上の問合せにはALL_ROWSヒントを適用します。

関連項目:

ヒントの概要は、『Oracle Database SQL言語リファレンス』を参照してください

19.3.2 結合順序のヒントのガイドライン

場合によっては、結果に影響を及ぼさない行にはアクセスしないように、SQL文で結合順序のヒントを指定することができます。

結合における駆動表は、その他の表が結合された表です。通常、駆動表には、表内を最も高い割合で排除するフィルタ条件が含まれています。結合順序は、SQL文のパフォーマンスに大きな影響を与えることがあります。

次のガイドラインを念頭に置いてください。

索引によって、要求された行がより効率的に取得される場合は、全表スキャンを実行しないでください。
少数の行をフェッチする索引を使用できる場合には、駆動表から多数の行をフェッチする索引を使用するようなことは避けてください。
結合順序の後ろへ行くほど結合する行が少なくなるように結合順序を選択してください。

次の例は、結合順序を効果的にチューニングする方法を示しています。

SELECT *
FROM   taba a, 
       tabb b, 
       tabc c
WHERE  a.acol BETWEEN   100 AND   200
AND    b.bcol BETWEEN 10000 AND 20000
AND    c.ccol BETWEEN 10000 AND 20000
AND    a.key1 = b.key1
AND    a.key2 = c.key2;

駆動表と駆動索引(存在する場合)を選択します。

前述の例における最初の3つの条件はそれぞれ、1つの表に適用されるフィルタ条件です。最後の2つの条件は結合条件です。

フィルタ条件は、駆動表と駆動索引の選択を左右します。通常、駆動表には、行を最も高い割合で排除するフィルタ条件が含まれています。100から200の範囲はacolの範囲に比べて狭く、10000から20000の範囲は相対的に大きいため、tabaは駆動表になり、その他はすべて同じになります。

ネステッド・ループ結合の場合、結合索引を介して結合します。この結合索引は、主キーまたは外部キーに付けられているもので、その表を結合ツリー内のそれより前にある表に結び付けるために使用します。駆動表を除いて、非結合条件にこの結合索引を使用することはほとんどありません。そのため、tabaを駆動表として選択した後、b.key1とc.key2の索引を使用してtabbとtabcをそれぞれ駆動します。
未使用の最適なフィルタを最初に駆動する最適な結合順序を選択します。

最適な未使用フィルタを持つ表に先に結合することで、その後の結合の作業を削減できます。したがって、bcol BETWEENがccol BETWEENよりも限定的な(行をより高い比率で排除する)場合は、tabbをtabcよりも前に結合すると、最後の結合が容易になります(行数が少なくなります)。
ORDEREDまたはSTARヒントを使用して、結合順序を強制的に設定できます。

関連項目:

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

19.3.3 ヒントに関するレポート

説明された計画には、計画の生成時に使用されたヒントを示すレポートが含まれています。

19.3.3.1 ヒント使用状況レポートの目的

Oracle Database 19cより前のリリースでは、オプティマイザがヒントを使用しなかった理由を知ることが困難な場合がありました。ヒント使用状況レポートにより、この問題が解決されます。

オプティマイザは、ヒント内のエンコードされた命令を使用して文の実行計画を選択します。ただし、条件によってはオプティマイザがヒントを使用しないことがあります。データベースでは、無視したヒントに対してエラー・メッセージは発行されません。ヒント・レポートには、使用されたヒントと無視されたヒントが表示されます。通常はヒントが無視された理由も示されます。ヒントが無視される最も一般的な理由は、次のとおりです。

構文エラー

ヒントには、入力ミスや無効な引数が含まれていることがあります。同じヒント・ブロックに複数のヒントがあり、そのうちの1つのヒントに構文エラーがあると、オプティマイザはエラーがあるヒントの前までのすべてのヒントを考慮に入れますが、その後にあるヒントを無視します。たとえば、ヒント指定/*+ INDEX(t1) FULL(t2) MERG(v) USE_NL(t2) */のMERG(v)には構文エラーがあります。オプティマイザはINDEX(t1)とFULL(t2)を考慮に入れますが、MERG(v)とUSE_NL(t2)を無視します。ヒント使用状況レポートには、エラーがあるものとしてMERG(v)が示されます。ただし、USE_NL(t2)は解析されないためレポートに示されません。
未解決のヒント

未解決のヒントは、構文エラー以外の理由により無効です。たとえば、文でINDEX(employees emp_idx)を指定していて、emp_idxが表employeesの有効な索引名ではない場合です。
競合するヒント

競合するヒントの組合せは、それらのヒントが正しく指定されていたとしてもデータベースによって無視されます。たとえば、文でFULL(employees) INDEX(employees)を指定していても、索引スキャンと全表スキャンは同時に両方実行することはできません。ほとんどの場合、オプティマイザは競合するヒントを両方とも無視します。
変換の影響を受けるヒント

一部のヒントは、変換によって無効になる場合があります。たとえば、文でPUSH_PRED(some_view) MERGE(some_view)を指定している場合です。some_viewが、それを含む問合せブロックにマージされると、some_viewが使用できなくなるため、オプティマイザはPUSH_PREDヒントを適用できなくなります。

関連項目:

コメントおよびヒントの構文ルールについて学習するには、Oracle Database SQL言語リファレンスを参照してください

19.3.3.2 ヒント使用状況レポートのユーザー・インタフェース

このレポートには、すべてのオプティマイザ・ヒントのステータスが表示されます。その他のヒントのサブセット(PARALLELやINMEMORYなど)も示されます。

レポート・アクセス

ヒントのトラッキングはデフォルトで有効になっています。ヒント使用状況レポートにアクセスするには、次のDBMS_XPLAN関数を使用します。

DISPLAY
DISPLAY_CURSOR
DISPLAY_WORKLOAD_REPOSITORY
DISPLAY_SQL_PLAN_BASELINE
DISPLAY_SQLSET

前述の関数は、formatパラメータで値HINT_REPORTを指定すると、レポートを生成します。値TYPICALを指定すると最終プランでは使用されていないヒントのみが表示されますが、値ALLを指定すると使用されたヒントと使用されていないヒントの両方が表示されます。

レポート・フォーマット

次のヒント付き問合せを説明するとします。

SELECT /*+ INDEX(t1) FULL(@sel$2 t1) */ COUNT(*)
FROM   jobs t1
WHERE t1.job_id IN (SELECT /*+ FULL(t1) */ job_id FROM employees t1);

次のDBMS_XPLAN.DISPLAYの出力では、ヒント・レポートを含む計画を示しています。

---------------------------------------------------------------------------
| Id| Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT     |           |   1 |    17 |  3  (34)| 00:00:01 |
| 1 |  SORT AGGREGATE      |           |   1 |    17 |         |          |
| 2 |   NESTED LOOPS       |           |  19 |   323 |  3  (34)| 00:00:01 |
| 3 |    SORT UNIQUE       |           | 107 |   963 |  2   (0)| 00:00:01 |
| 4 |     TABLE ACCESS FULL| EMPLOYEES | 107 |   963 |  2   (0)| 00:00:01 |
|*5 |    INDEX UNIQUE SCAN | JOB_ID_PK |   1 |     8 |  0   (0)| 00:00:01 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   4 - SEL$5DA710D3 / "T1"@"SEL$2"
   5 - SEL$5DA710D3 / "T1"@"SEL$1"

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T1"."JOB_ID"="JOB_ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (#keys=0)
   3 - (#keys=1) "JOB_ID"[VARCHAR2,10]
   4 - (rowset=256) "JOB_ID"[VARCHAR2,10]

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1))
---------------------------------------------------------------------------

   4 -  SEL$5DA710D3 / "T1"@"SEL$2"
         U -  FULL(t1) / hint overridden by another in parent query block
           -  FULL(@sel$2 t1)

   5 -  SEL$5DA710D3 / "T1"@"SEL$1"
           -  INDEX(t1)

レポートのヘッダーには、レポート内のヒントの合計数が示されます。この場合、文には合計3つのヒントが含まれています。ヒントが未使用、未解決の場合やヒントに構文エラーがある場合、ヘッダーにはその数が示されます。この場合、1つのヒントのみが未使用になっています。

レポートでは、計画に出現するオブジェクト(問合せブロックや表など)の下にヒントが表示されます。各オブジェクトの前にある番号によって、計画内でオブジェクトが最初に出現する行を識別できます。たとえば、前述のレポートには、個別のオブジェクトT1@SEL$2およびT1@SEL$1に適用されるヒントが示されています。表T1@SEL$2は、計画の行4で問合せブロックSEL$5DA710D3内に現れます。表T1@SEL$1は、計画の行5で同じ問合せブロックに現れます。

ヒントは、不正確に指定されることや、最終計画に存在しないオブジェクトに関連付けられることもあります。問合せブロックが最終計画に現れない場合、レポートでは、その問合せブロックに行番号0が割り当てられます。前述の例では、行番号0のヒントがないため、すべての問合せブロックが最終計画に現れます。

このレポートには、ヒントのテキストが表示されます。ヒントには、次のいずれかの注釈が付いているものもあります。

Eは、構文エラーを示します。
Nは、未解決のヒントを示します。
Uは、最終計画で対応するヒントが使用されなかったことを示します。

前述の例では、U - FULL(t1)は、問合せブロックSEL$5DA710D3が最終計画に現れたが、FULL(t1)ヒントは適用されなかったことを示しています。

それぞれのオブジェクトでは、未使用のヒントが最初に表示され、その後に使用されたヒントが表示されます。たとえば、レポートには、使用されなかったFULL(t1)ヒントが最初に表示され、その後に使用されたFULL(@sel$2t1)が表示されます。多くの未使用のヒントについて、レポートには、オプティマイザがヒントを適用しなかった理由が示されます。前述の例では、FULL(t1)はhint overridden by another in parent query blockという理由で使用されなかったことがレポートに示されています。

関連項目:

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

19.3.3.3 ヒント使用状況に関するレポート: チュートリアル

ヒント使用状況に関するレポートには、DBMS_XPLAN表示関数を使用できます。

ヒント使用状況レポートは、デフォルトで有効になっています。ヒント情報が含まれている計画を表示するステップは、計画の通常の表示ステップと同じです。

前提条件

このチュートリアルでは、次のことが前提となっています。

employees.employee_id列に対する索引emp_emp_id_pkが存在している。
特定の従業員を問い合せようとしている。
オプティマイザにemp_emp_id_pkの使用を強制するために、INDEXヒントを使用しようとしている。

ヒント使用状況に関するレポートの作成には:

SQL*PlusまたはSQL Developerを起動して、ユーザーhrとしてデータベースにログインします。

employeesの問合せに対する計画をEXPLAINします。

たとえば、次の文を入力します。

EXPLAIN PLAN FOR
  SELECT /*+ INDEX(e emp_emp_id_pk) */ COUNT(*) 
  FROM   employees e 
  WHERE  e.employee_id = 5;

表示関数を使用して、PLAN TABLEを問い合せます。

formatパラメータには、次のいずれかの値を指定できます。

ALL
TYPICAL

次の問合せでは、ヒント使用状況の情報を含む計画のすべてのセクションが表示されます(出力例も示します)。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------

Plan hash value: 2637910222
----------------------------------------------------------------------
|Id | Operation          | Name       |Rows|Bytes | Cost (%CPU)| Time|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT   |               | 1 | 4 | 0   (0)| 00:00:01 |
| 1 |  SORT AGGREGATE    |               | 1 | 4 |        |          |
|*2 |   INDEX UNIQUE SCAN| EMP_EMP_ID_PK | 1 | 4 | 0   (0)| 00:00:01 |
----------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
----------------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / E@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("E"."EMPLOYEE_ID"=5)

Column Projection Information (identified by operation id):
----------------------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

Hint Report (identified by operation id/Query Block Name/Object Alias)
Total hints for statement: 1
----------------------------------------------------------------------

   2 -  SEL$1 / E@SEL$1
           -  INDEX(e emp_emp_id_pk)

「Hint Report」セクションには、INDEX(e emp_emp_id_pk)ヒントの問合せブロックがSEL$1になっていることが示されています。表の識別子は、E@SEL$1です。計画行の行番号は2です。これは、表E@SEL$1がPLAN TABLEに現れる最初の行と一致します。

関連項目:

EXPLAIN PLANについてさらに学習するには、『Oracle Database SQL言語リファレンス』を参照

19.3.3.4 ヒント使用状況レポート: 例

ここでは、様々なタイプのヒント使用状況レポートの例を示します。

次の例では、hrスキーマでの表の問合せをすべて表示します。

例19-2 文レベルの未使用ヒント

次の例では、emp_manager_ix索引に索引範囲ヒントを指定します。

EXPLAIN PLAN FOR
  SELECT /*+ INDEX_RS(e emp_manager_ix) */ COUNT(*)
  FROM   employees e
  WHERE  e.job_id < 5;

次のPLAN TABLEの問合せでは、format値に未使用のヒントのみを示すTYPICALを指定しています。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'TYPICAL'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2731009358

---------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes| Cost (%CPU)| Time  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |    1 |   9  |   1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |            |    1 |    9 |          |          |
|*  2 |   INDEX FULL SCAN| EMP_JOB_IX |    5 |   45 |   1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

   2 - filter(TO_NUMBER("E"."JOB_ID")<5)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   2 -  SEL$1 / E@SEL$1
         U -  INDEX_RS(e emp_manager_ix)

前述のヒント使用状況レポートにあるUは、INDEX_RSヒントが使用されなかったことを示しています。このレポートには、未使用のヒントの合計数が表示されます(U – Unused (1))。

例19-3 競合するヒント

次の例では、2つのヒント(スキップ・スキャンと高速全スキャンのヒント)を指定します。

EXPLAIN PLAN FOR
  SELECT /*+ INDEX_SS(e emp_manager_ix) INDEX_FFS(e) */ COUNT(*)
  FROM   employees e
  WHERE  e.manager_id < 5;

次のPLAN TABLEの問合せでは、format値に未使用のヒントのみを示すTYPICALを指定しています。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'TYPICAL'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2262146496

---------------------------------------------------------------------------
| Id| Operation         | Name           |Rows  |Bytes |Cost (%CPU)|Time  |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |                | 1 |   4 |    1   (0)| 00:00:01 |
| 1 |  SORT AGGREGATE   |                | 1 |   4 |           |          |
|*2 |   INDEX RANGE SCAN| EMP_MANAGER_IX | 1 |   4 |    1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

   2 - access("E"."MANAGER_ID"<5)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------

   2 -  SEL$1 / E@SEL$1
    U -  INDEX_FFS(e) / hint conflicts with another in sibling query block
    U -  INDEX_SS(e emp_manager_ix) / hint conflicts with another in 
         sibling query block

前述のレポートは、INDEX_FFS(e)ヒントとINDEX_SS(e emp_manager_ix)ヒントが相互に競合していることを示しています。索引スキップ・スキャンと高速全索引スキャンは相互に排他的です。オプティマイザは、テキスト「U — Unused (2)」で示されているように、両方のヒント無視しました。オプティマイザは、emp_manager_ix索引を指定するヒントを無視しましたが、この索引自体はコストベースの分析に基づいて使用されています。

例19-4 マルチ表ヒント

次の例では、4つのヒントを指定していますが、そのうちの1つは2つの表を指定しています。

EXPLAIN PLAN FOR
  SELECT /*+ ORDERED USE_NL(t1, t2) INDEX(t2) NLJ_PREFETCH(t2) */ COUNT(*)
  FROM   jobs t1, employees t2
  WHERE  t1.job_id = t2.employee_id;

次のPLAN TABLEの問合せでは、format値にALLを指定しています。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2668549896

---------------------------------------------------------------------------
| Id  | Operation           | Name          |Rows |Bytes |Cost (%CPU)|Time|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |  1 |  12 | 1  (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |               |  1 |  12 |       |          |
|   2 |   NESTED LOOPS      |               | 19 | 228 | 1  (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN  | JOB_ID_PK     | 19 | 152 | 1  (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| EMP_EMP_ID_PK |  1 |   4 | 0  (0)| 00:00:01 |
---------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / T1@SEL$1
   4 - SEL$1 / T2@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T2"."EMPLOYEE_ID"=TO_NUMBER("T1"."JOB_ID"))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (#keys=0)
   3 - "T1"."JOB_ID"[VARCHAR2,10]

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 5 (U - Unused (2))
---------------------------------------------------------------------------

   1 -  SEL$1
           -  ORDERED

   3 -  SEL$1 / T1@SEL$1
         U -  USE_NL(t1, t2)

   4 -  SEL$1 / T2@SEL$1
         U -  NLJ_PREFETCH(t2)
           -  INDEX(t2)
           -  USE_NL(t1, t2)

前述のレポートは、2つのヒントUSE_NL(t1, t2)とNLJ_PREFETCH(t2)が使用されなかったことを示しています。計画のステップ3は、jobs表(別名t1)の全索引スキャンです。このレポートは、オプティマイザがjobsのアクセスにUSE_NL(t1, t2)ヒントを適用しなかったことを示しています。ステップ4は、employees表(別名t2)の索引一意スキャンです。USE_NL(t1, t2)にはUの接頭辞が付いていないことから、オプティマイザがemployeesのヒントを使用したことがわかります。

例19-5 未使用の問合せブロックのヒント

次の例では、副問合せに2つのヒントUNNESTとSEMIJOINを指定します。

EXPLAIN PLAN FOR
  SELECT COUNT(*), manager_id
  FROM   departments
  WHERE  manager_id IN (SELECT /*+ UNNEST SEMIJOIN */ manager_id FROM employees)
  AND    ROWNUM <= 2
GROUP BY manager_id;

次のPLAN TABLEの問合せでは、format値にALLを指定しています。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 173733304
---------------------------------------------------------------------------
| Id  |Operation           | Name          |Rows  |Bytes |Cost (%CPU)|Time|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT     |                |  2 | 14 | 3  (34)| 00:00:01 |
| 1 |  HASH GROUP BY       |                |  2 | 14 | 3  (34)| 00:00:01 |
|*2 |   COUNT STOPKEY      |                |    |    |        |          |
| 3 |    NESTED LOOPS SEMI |                |  2 | 14 | 2   (0)| 00:00:01 |
|*4 |     TABLE ACCESS FULL| DEPARTMENTS    |  2 |  6 | 2   (0)| 00:00:01 |
|*5 |     INDEX RANGE SCAN | EMP_MANAGER_IX | 107|428 | 0   (0)| 00:00:01 |
---------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   4 - SEL$5DA710D3 / DEPARTMENTS@SEL$1
   5 - SEL$5DA710D3 / EMPLOYEES@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=2)
   4 - filter("MANAGER_ID" IS NOT NULL)
   5 - access("MANAGER_ID"="MANAGER_ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) "MANAGER_ID"[NUMBER,22], COUNT(*)[22]
   2 - "MANAGER_ID"[NUMBER,22]
   3 - (#keys=0) "MANAGER_ID"[NUMBER,22]
   4 - "MANAGER_ID"[NUMBER,22]

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------

   0 -  SEL$2
           -  SEMIJOIN
           -  UNNEST

この例では、問合せブロックSEL$2にヒントが指定されていますが、最終計画にSEL$2は現れません。このレポートでは、対応する行番号が0のSEL$2の下にヒントが表示されています。

例19-6 オーバーライドされたヒント

次の例では、同じ問合せブロックの同じ表に2つのFULLヒントを指定します。

EXPLAIN PLAN FOR
  SELECT /*+ INDEX(t1) FULL(@sel$2 t1) */ COUNT(*)
  FROM   jobs t1
  WHERE t1.job_id IN (SELECT /*+ FULL(t1) NO_MERGE */ job_id FROM employees t1);

次のPLAN TABLEの問合せでは、format値にALLを指定しています。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 3101158531

---------------------------------------------------------------------------
| Id  | Operation            | Name      |Rows  |Bytes |Cost (%CPU)|Time  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |  1 |  17 |   3  (34)| 00:00:01 |
|   1 |  SORT AGGREGATE      |           |  1 |  17 |          |          |
|   2 |   NESTED LOOPS       |           | 19 | 323 |   3  (34)| 00:00:01 |
|   3 |    SORT UNIQUE       |           |107 | 963 |   2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMPLOYEES |107 | 963 |   2   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN | JOB_ID_PK |  1 |   8 |   0   (0)| 00:00:01 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   4 - SEL$5DA710D3 / T1@SEL$2
   5 - SEL$5DA710D3 / T1@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T1"."JOB_ID"="JOB_ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (#keys=0)
   3 - (#keys=1) "JOB_ID"[VARCHAR2,10]
   4 - (rowset=256) "JOB_ID"[VARCHAR2,10]

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4 (U - Unused (1))
---------------------------------------------------------------------------

   0 -  SEL$2
           -  NO_MERGE

   4 -  SEL$5DA710D3 / T1@SEL$2
         U -  FULL(t1) / hint overridden by another in parent query block
           -  FULL(@sel$2 t1)

   5 -  SEL$5DA710D3 / T1@SEL$1
           -  INDEX(t1)

指定された3つのヒントのうち、1つのみが未使用でした。問合せブロックSEL$2で指定したヒントFULL(t1)は、問合せブロックSEL$1で指定したヒントFULL(@sel$2 T1)によってオーバーライドされました。問合せブロックSEL$2のNO_MERGEヒントが使用されました。

次のPLAN TABLEの問合せでは、formatの設定に未使用のヒントのみ示すTYPICALを使用しています。

SQL> select * from table(dbms_xplan.display(format => 'TYPICAL'));
Plan hash value: 3101158531

---------------------------------------------------------------------------
| Id | Operation            | Name      |Rows  |Bytes |Cost (%CPU)|Time   |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT     |           |   1 |   17 |  3  (34)| 00:00:01 |
|  1 |  SORT AGGREGATE      |           |   1 |   17 |         |          |
|  2 |   NESTED LOOPS       |           |  19 |  323 |  3  (34)| 00:00:01 |
|  3 |    SORT UNIQUE       |           | 107 |  963 |  2   (0)| 00:00:01 |
|  4 |     TABLE ACCESS FULL| EMPLOYEES | 107 |  963 |  2   (0)| 00:00:01 |
|* 5 |    INDEX UNIQUE SCAN | JOB_ID_PK |   1 |    8 |  0   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T1"."JOB_ID"="JOB_ID")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   4 -  SEL$5DA710D3 / T1@SEL$2
         U -  FULL(t1) / hint overridden by another in parent query block

例19-7 複数のヒント

次のUNION ALL問合せでは、10個の異なるヒントを指定しています。

SELECT /*+ FULL(t3) INDEX(t2) INDEX(t1) MERGE(@SEL$5) PARALLEL(2) */ t1.first_name
FROM   employees t1, jobs t2, job_history t3
WHERE  t1.job_id = t2.job_id
AND    t2.min_salary = 100000
AND    t1.department_id = t3.department_id
UNION ALL
SELECT /*+ INDEX(t3) USE_MERGE(t2) INDEX(t2) FULL(t1) NO_ORDER_SUBQ */ t1.first_name
FROM   departments t3, jobs t2, employees t1
WHERE  t1.job_id = t2.job_id
AND    t2.min_salary = 100000
AND    t1.department_id = t3.department_id;

次の共有SQL領域の問合せでは、ALLのformat値を指定しています(計画の行は読みやすくするために切り詰められています)。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALL'))

...

-------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name      |Rows  |Bytes |Cost (%CPU)|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |             |      |      |  9 (100)|
|   1 |  UNION-ALL                                  |             |      |      |         |
|   2 |   PX COORDINATOR                            |             |      |      |         |
|   3 |    PX SEND QC (RANDOM)                      | :TQ10002    |    5 |  175 |  5   (0)|
|*  4 |     HASH JOIN                               |             |    5 |  175 |  5   (0)|
|   5 |      PX RECEIVE                             |             |    3 |   93 |  3   (0)|
|   6 |       PX SEND BROADCAST                     | :TQ10001    |    3 |   93 |  3   (0)|
|   7 |        NESTED LOOPS                         |             |    3 |   93 |  3   (0)|
|   8 |         NESTED LOOPS                        |             |    6 |   93 |  3   (0)|
|*  9 |          TABLE ACCESS BY INDEX ROWID BATCHED| JOBS        |    1 |   12 |  2   (0)|
|  10 |           BUFFER SORT                       |             |      |      |         |
|  11 |            PX RECEIVE                       |             |   19 |      |  1   (0)|
|  12 |             PX SEND HASH (BLOCK ADDRESS)    | :TQ10000    |   19 |      |  1   (0)|
|  13 |              PX SELECTOR                    |             |      |      |         |
|  14 |               INDEX FULL SCAN               | JOB_ID_PK   |   19 |      |  1   (0)|
|* 15 |          INDEX RANGE SCAN                   | EMP_JOB_IX  |    6 |      |  0   (0)|
|  16 |         TABLE ACCESS BY INDEX ROWID         | EMPLOYEES   |    6 |  114 |  1   (0)|
|  17 |      PX BLOCK ITERATOR                      |             |   10 |   40 |  2   (0)|
|* 18 |       TABLE ACCESS FULL                     | JOB_HISTORY |   10 |   40 |  2   (0)|
|  19 |   PX COORDINATOR                            |             |      |      |         |
|  20 |    PX SEND QC (RANDOM)                      | :TQ20002    |    3 |   93 |  4   (0)|
|* 21 |     HASH JOIN                               |             |    3 |   93 |  4   (0)|
|  22 |      JOIN FILTER CREATE                     | :BF0000     |    1 |   12 |  2   (0)|
|  23 |       PX RECEIVE                            |             |    1 |   12 |  2   (0)|
|  24 |        PX SEND BROADCAST                    | :TQ20001    |    1 |   12 |  2   (0)|
|* 25 |         TABLE ACCESS BY INDEX ROWID BATCHED | JOBS        |    1 |   12 |  2   (0)|
|  26 |          BUFFER SORT                        |             |      |      |         |
|  27 |           PX RECEIVE                        |             |   19 |      |  1   (0)|
|  28 |            PX SEND HASH (BLOCK ADDRESS)     | :TQ20000    |   19 |      |  1   (0)|
|  29 |             PX SELECTOR                     |             |      |      |         |
|  30 |              INDEX FULL SCAN                | JOB_ID_PK   |   19 |      |  1   (0)|
|  31 |      JOIN FILTER USE                        | :BF0000     |  106 | 2014 |  2   (0)|
|  32 |       PX BLOCK ITERATOR                     |             |  106 | 2014 |  2   (0)|
|* 33 |        TABLE ACCESS FULL                    | EMPLOYEES   |  106 | 2014 |  2   (0)|
-------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SET$1
   4 - SEL$1
   9 - SEL$1        / T2@SEL$1
  14 - SEL$1        / T2@SEL$1
  15 - SEL$1        / T1@SEL$1
  16 - SEL$1        / T1@SEL$1
  18 - SEL$1        / T3@SEL$1
  21 - SEL$E0F432AE
  25 - SEL$E0F432AE / T2@SEL$2
  30 - SEL$E0F432AE / T2@SEL$2
  33 - SEL$E0F432AE / T1@SEL$2

Predicate Information (identified by operation id):
-----------------------------------------------------------

   4 - access("T1"."DEPARTMENT_ID"="T3"."DEPARTMENT_ID")
   9 - filter("T2"."MIN_SALARY"=100000)
  15 - access("T1"."JOB_ID"="T2"."JOB_ID")
  18 - access(:Z>=:Z AND :Z<=:Z)
  21 - access("T1"."JOB_ID"="T2"."JOB_ID")
  25 - filter("T2"."MIN_SALARY"=100000)
  33 - access(:Z>=:Z AND :Z<=:Z)
       filter(("T1"."DEPARTMENT_ID" IS NOT NULL AND 
         SYS_OP_BLOOM_FILTER(:BF0000,"T1"."JOB_ID")))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - STRDEF[20]
   2 - "T1"."FIRST_NAME"[VARCHAR2,20]
   3 - (#keys=0) "T1"."FIRST_NAME"[VARCHAR2,20]
   4 - (#keys=1; rowset=256) "T1"."FIRST_NAME"[VARCHAR2,20]
   5 - (rowset=256) "T1"."DEPARTMENT_ID"[NUMBER,22], "T1"."FIRST_NAME"[VARCHAR2,20]
   6 - (#keys=0) "T1"."DEPARTMENT_ID"[NUMBER,22], "T1"."FIRST_NAME"[VARCHAR2,20]
   7 - "T1"."FIRST_NAME"[VARCHAR2,20], "T1"."DEPARTMENT_ID"[NUMBER,22]
   8 - "T1".ROWID[ROWID,10]
   9 - "T2"."JOB_ID"[VARCHAR2,10]
  10 - (#keys=0) "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
  11 - (rowset=256) "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
  12 - (#keys=1) "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
  13 - "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
  14 - "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
  15 - "T1".ROWID[ROWID,10]
  16 - "T1"."FIRST_NAME"[VARCHAR2,20], "T1"."DEPARTMENT_ID"[NUMBER,22]
  17 - (rowset=256) "T3"."DEPARTMENT_ID"[NUMBER,22]
  18 - (rowset=256) "T3"."DEPARTMENT_ID"[NUMBER,22]
  19 - "T1"."FIRST_NAME"[VARCHAR2,20]
  20 - (#keys=0) "T1"."FIRST_NAME"[VARCHAR2,20]
  21 - (#keys=1; rowset=256) "T1"."FIRST_NAME"[VARCHAR2,20]
  22 - (rowset=256) "T2"."JOB_ID"[VARCHAR2,10]
  23 - (rowset=256) "T2"."JOB_ID"[VARCHAR2,10]
  24 - (#keys=0) "T2"."JOB_ID"[VARCHAR2,10]
  25 - "T2"."JOB_ID"[VARCHAR2,10]
  26 - (#keys=0) "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
  27 - (rowset=256) "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
  28 - (#keys=1) "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
  29 - "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
  30 - "T2".ROWID[ROWID,10], "T2"."JOB_ID"[VARCHAR2,10]
  31 - (rowset=256) "T1"."FIRST_NAME"[VARCHAR2,20], "T1"."JOB_ID"[VARCHAR2,10]
  32 - (rowset=256) "T1"."FIRST_NAME"[VARCHAR2,20], "T1"."JOB_ID"[VARCHAR2,10]
  33 - (rowset=256) "T1"."FIRST_NAME"[VARCHAR2,20], "T1"."JOB_ID"[VARCHAR2,10]

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 10 (U - Unused (2), N - Unresolved (1), E - Syntax error (1))
----------------------------------------------------------------------------------------

   0 -  STATEMENT
           -  PARALLEL(2)

   0 -  SEL$5
         N -  MERGE(@SEL$5)

   0 -  SEL$2
         E -  NO_ORDER_SUBQ

   9 -  SEL$1 / T2@SEL$1
           -  INDEX(t2)

  15 -  SEL$1 / T1@SEL$1
           -  INDEX(t1)

  18 -  SEL$1 / T3@SEL$1
           -  FULL(t3)

  21 -  SEL$E0F432AE / T3@SEL$2
         U -  INDEX(t3)

  25 -  SEL$E0F432AE / T2@SEL$2
         U -  USE_MERGE(t2)
           -  INDEX(t2)

  33 -  SEL$E0F432AE / T1@SEL$2
           -  FULL(t1)

Note
-----
   - Degree of Parallelism is 2 because of hint

このレポートには、次の未使用のヒントが示されています。

2つの未使用ヒント(U)

このレポートは、INDEX(t3)とUSE_MERGE(t2)が、問合せブロックSEL$E0F432AEで使用されなかったことを示しています。
1つの未解決ヒント(N)

ヒントMERGEは、問合せブロックSEL$5が存在しないため解決されていません。
1つの構文エラー(E)

SEL$2で指定されたヒントNO_ORDER_SUBQは、有効なヒントではありません。