1. SQLチューニング・ガイド
  2. オプティマイザ統計
  3. オプティマイザ統計収集のオプションの構成

12 オプティマイザ統計収集のオプションの構成

この章では、オプティマイザ統計収集の内容および統計プリファレンスの設定方法について説明します。

この章のトピックは、次のとおりです:

12.1 オプティマイザ統計収集について

Oracle Databaseでは、オプティマイザ統計収集とは、固定オブジェクトを含む、データベース・オブジェクトのオプティマイザ統計を収集することです。

データベースには、オプティマイザ統計が自動的に収集されます。DBMS_STATSパッケージを使用して、手動で収集することもできます。

この項では、次の項目について説明します。

関連トピック

12.1.1 オプティマイザ統計収集の目的

表の内容および関連付けられた索引が頻繁に変更されると、オプティマイザによって最適ではない問合せの実行計画が選択される可能性があります。潜在的なパフォーマンスの問題を回避するには、統計を最新に維持する必要があります。

DBAの介入を最小化するために、Oracleデータベースでは、様々なタイミングでオプティマイザ統計が自動収集されます。AutoTaskを有効化してDBMS_STATSを実行するなど、一部の自動オプションは構成が可能です。

12.1.2 オプティマイザ統計管理のユーザー・インタフェース

Oracle Enterprise Manager Cloud Control(Cloud Control)またはコマンドラインのPL/SQLのいずれかを使用してオプティマイザ統計を管理できます。

この項では、次の項目について説明します。

12.1.2.1 オプティマイザ統計管理のグラフィカル・インタフェース

Cloud Controlの「オプティマイザ統計の管理」ページは、オプティマイザ統計の管理が可能なGUIです。

この項では、次の項目について説明します。

12.1.2.1.1 Cloud Controlのデータベース・ホーム・ページへのアクセス

Oracle Enterprise Manager Cloud Controlでは、GUIベースの単一フレームワーク内で複数のデータベースを管理できます。

Cloud Controlを使用してデータベース・ホーム・ページにアクセスするには:

  1. 適切な資格証明を使用してCloud Controlにログインします。

  2. 「ターゲット」メニューの下で、「データベース」を選択します。

  3. データベース・ターゲットのリストで、管理対象のOracle Databaseインスタンスのターゲットを選択します。

  4. データベースの資格証明の入力を求められた場合は、実行するタスクに必要な最小限の資格証明を入力します。

関連項目:

Cloud Controlオンライン・ヘルプ

12.1.2.1.2 「オプティマイザ統計コンソール」へのアクセス

「オプティマイザ統計コンソール」ページからリンクされている各ページを使用すると、オプティマイザ統計に関連している最も必要なタスクを実行できます。

Cloud Controlを使用してオプティマイザ統計を管理するには:

  1. Cloud Controlで「データベース・ホーム」ページにアクセスします。

  2. 「パフォーマンス」メニューから、「SQL」→「オプティマイザ統計」を選択します。

    「オプティマイザ統計コンソール」が表示されます。

関連項目:

Oracle Enterprise Manager Cloud Controlのオンライン・ヘルプを参照してください。

12.1.2.2 オプティマイザ統計管理のコマンドライン・インタフェース

DBMS_STATSパッケージは、オプティマイザ統計タスクのほとんどを実行します。

自動統計収集を有効化および無効化するには、DBMS_AUTO_TASK_ADMIN PL/SQLパッケージを使用します。

関連項目:

DBMS_STATSおよびDBMS_AUTO_TASK_ADMINの使用方法を学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください

12.2 オプティマイザ統計プリファレンスの設定

このトピックでは、DBMS_STATS.SET_*_PREFSプロシージャを使用してオプティマイザ統計のデフォルトを設定する方法について説明します。

この項では、次の項目について説明します。

12.2.1 オプティマイザ統計プリファレンスについて

オプティマイザ統計プリファレンスでは、自動統計収集およびDBMS_STATS統計収集プロシージャで使用されるパラメータのデフォルト値が設定されます。

表、スキーマ、データベース(すべての表)およびグローバル・レベルでオプティマイザ統計プリファレンスを設定できます。グローバル・プリファレンスは、プリファレンスなしの表および将来作成される表を示します。SET_*_PREFS形式の後にプロシージャ名が続きます。

この項では、次の項目について説明します。

12.2.1.1 オプティマイザ統計プリファレンスの目的

プリファレンスにより、一部のオブジェクトにデフォルトとは異なる設定が必要な場合に、オプティマイザ統計を自動的に維持できます。これにより、Oracle Databaseが統計を収集する方法がより制御されます。

設定可能なプリファレンスの一部を次に示します。

  • ESTIMATE_PERCENT

    このプリファレンスにより、推定する行のパーセントが決定されます。

  • CONCURRENT

    このプリファレンスは、データベースにより統計を複数のオブジェクトで同時に収集するか、または一度に1つずつ順次に収集するかどうかを判別します。

  • STALE_PERCENT

    このプリファレンスは、再収集が必要でデータベースが統計を失効と判断する前に変更する必要がある表の行の割合を判別します。

  • AUTO_STAT_EXTENSIONS

    デフォルト以外の値のONに設定すると、このプリファレンスにより、SQL計画ディレクティブでワークロードの述語の列の使用に基づいて列グループ統計の作成を発生させることができます。

  • INCREMENTAL

    このプリファレンスは、データベースで全表スキャンを実行せずにパーティション表のグローバル統計を維持するかどうかを判別します。設定可能な値はTRUEおよびFALSEです。

    たとえば、デフォルトINCREMENTALのデフォルト設定はFALSEです。いくつかの最新のパーティションを更新する場合は、レンジ・パーティション表のINCREMENTALをTRUEに設定します。また、パーティション化されていない表でパーティション交換操作を実行する場合は、INCREMENTALをTRUEに、INCREMENTAL_LEVELをTABLEに設定することをお薦めします。これらの設定を使用して、DBMS_STATSでは当該の表にある表レベルのシノプシスが収集されます。

  • INCREMENTAL_LEVEL

    INCREMENTALプリファレンスがTRUEに設定されている場合、このプリファレンスはどのシノプシスを収集するかを制御します。TABLEまたはPARTITIONの2つの値を取ります。

  • APPROXIMATE_NDV_ALGORITHM

    このプリファレンスが、増分統計を使用してパーティション表の個別値の数を計算するときに使用するアルゴリズムを制御します。

12.2.1.2 統計プリファレンスを設定するDBMS_STATSプロシージャ

DBMS_STATS.SET_*_PREFSプロシージャは、DBMS_STATS.GATHER_*_STATSプロシージャで使用されるパラメータのデフォルト値を変更します。現在のプリファレンスを問い合せるには、DBMS_STATS.GET_PREFSファンクションを使用します。

統計プリファレンスを設定すると、優先順位は次のようになります。

  1. 表プリファレンス(特定の表、スキーマ内のすべての表、またはデータベース内のすべての表に設定)

  2. グローバル・プリファレンス

  3. デフォルト・プリファレンス

次の表は、関連するDBMS_STATSプロシージャをまとめたものです。

表12-1 オプティマイザ統計プリファレンスを設定するDBMS_STATSプロシージャ

プロシージャ 有効範囲

SET_TABLE_PREFS

指定された表のみ。

SET_SCHEMA_PREFS

指定されたスキーマのすべての既存の表。

このプロシージャは、指定されたスキーマの各表でSET_TABLE_PREFSをコールします。SET_SCHEMA_PREFSをコールすることで、その実行後に作成されたどの新規の表にも影響が及ぶことはありません。新しい表は、GLOBAL_PREF値をすべてのパラメータで使用します。

SET_DATABASE_PREFS

データベースでユーザーが定義したすべてのスキーマ。ADD_SYSパラメータをtrueに設定すると、SYSおよびSYSTEMなどのシステム所有のスキーマを含めることができます。

このプロシージャは、指定されたスキーマの各表でSET_TABLE_PREFSをコールします。SET_DATABASE_PREFSをコールすることで、その実行後に作成されたどの新規オブジェクトにも影響が及ぶことはありません。新しいオブジェクトは、GLOBAL_PREF値をすべてのパラメータで使用します。

SET_GLOBAL_PREFS

既存の表プリファレンスを持たないすべての表。

表プリファレンスを設定するか、DBMS_STATS.GATHER_*_STATS文でパラメータを明示的に設定しないかぎり、すべてのパラメータのデフォルトはグローバル設定です。SET_GLOBAL_PREFSによって変更が行われると、その実行後に作成されたすべての新規オブジェクトに影響が及びます。新しいオブジェクトは、SET_GLOBAL_PREFS値をすべてのパラメータで使用します。

SET_GLOBAL_PREFSを使用して、AUTOSTATS_TARGETパラメータのデフォルト値を設定できます。この追加のパラメータは、夜間のメンテナンス・ウィンドウで実行される自動統計収集ジョブの影響を受けるオブジェクトを制御します。AUTOSTATS_TARGETに使用可能な値は、ALL、ORACLEおよびAUTO (デフォルト)です。

CONCURRENTプリファレンスを設定できるのは、グローバル・レベルのみです。SET_GLOBAL_PREFSを使用してプリファレンスINCREMENTAL_LEVELを設定することはできません。

関連項目:

12.2.1.3 統計プリファレンスのオーバーライド

preference_overrides_parameter統計プリファレンスは、オプティマイザ統計の収集時に統計プリファレンスを使用してパラメータの入力値をオーバーライドするかどうかを決定します。これにより、データベースが統計収集プロシージャに渡されたパラメータ値に従う時期を制御します。

preference_overrides_parameterがFALSE(デフォルト)に設定されている場合、統計収集プロシージャの入力値に従います。TRUEに設定されている場合、入力値が無視されます。

DBMS_STATSのSET_TABLE_PREFS、SET_SCHEMA_PREFSまたはSET_GLOBAL_PREFSプロシージャを使用して、preference_overrides_parameterプリファレンスを設定します。preference_overrides_parameterが設定されているかどうかに関係なく、データベースでは、統計を設定する同じ優先順位を使用します。

  1. 表プリファレンス(特定の表、スキーマ内のすべての表、またはデータベース内のすべての表に設定)

  2. グローバル・プリファレンス

  3. デフォルト・プリファレンス

例12-1 表レベルの統計プリファレンスのオーバーライド

この例では、レガシー・スクリプトは、推奨されるAUTO_SAMPLE_SIZEを使用せずにestimate_percentを明示的に設定します。ユーザーがこれらのスクリプトを使用してsh.costs表でプリファレンスを設定しないようにすることが目標です。

表12-2 表レベルの統計プリファレンスのオーバーライド

アクション 説明 
SQL> SELECT DBMS_STATS.GET_PREFS 
('estimate_percent', 'sh','costs') 
AS "STAT_PREFS" FROM DUAL;
 
STAT_PREFS
----------
DBMS_STATS.AUTO_SAMPLE_SIZE

estimate_percentのプリファレンスがsh.costsまたはグローバル・レベルに設定されていないため、プリファレンスのデフォルトはAUTO_SAMPLE_SIZEです。 

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS 
('sh', 'costs', 
'preference_overrides_parameter', 'true');
 
PL/SQL procedure successfully completed.

デフォルトでは、Oracle Databaseは、GATHER_*_STATSプロシージャに渡されるプリファレンスを受け入れます。これらのパラメータをオーバーライドするには、SET_TABLE_PREFSを使用して、costs表に対してのみpreference_overrides_parameterプリファレンスをtrueに設定します。 

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS
('sh', 'costs', estimate_percent=>100);

PL/SQL procedure successfully completed.

sh.costsの統計を収集する場合、estimate_percentを100に設定します。ただし、この表のpreference_overrides_parameterはtrueになっているため、Oracle Databaseはestimate_percent=>100の設定を無視します。かわりに、データベースはデフォルトのAUTO_SAMPLE_SIZEを使用して統計を収集します。

例12-2 グローバル・レベルの統計プリファレンスのオーバーライド

この例では、グローバル・レベルでestimate_percentを5に設定します。これは、このプリファレンスが表プリファレンス設定のないデータベースの各表に適用されることを意味します。次に、ユーザーがスクリプトのグローバル設定をオーバーライドしないように、表レベルのプリファレンス設定を含まないsh.sales表のオーバーライドを設定します。

表12-3 グローバル・レベルの統計プリファレンスのオーバーライド

アクション 説明 
SQL> SELECT DBMS_STATS.GET_PREFS 
('estimate_percent', 'sh','sales') 
AS "STAT_PREFS" FROM DUAL;
 
STAT_PREFS
----------
DBMS_STATS.AUTO_SAMPLE_SIZE

estimate_percentのプリファレンスがsh.salesまたはグローバル・レベルに設定されていないため、プリファレンスはデフォルトのAUTO_SAMPLE_SIZEになります。 

SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS 
('estimate_percent', '5');

PL/SQL procedure successfully completed.

SET_GLOBAL_PREFSプロシージャを使用して、表プリファレンス設定を含まないデータベースの表ごとにestimate_percentプリファレンスを5に設定します。 

SQL> SELECT DBMS_STATS.GET_PREFS 
('estimate_percent', 'sh','sales') 
AS "STAT_PREFS" FROM DUAL;
 
STAT_PREFS
----------
5

sh.salesにはプリファレンス設定がないため、この表にはグローバル設定が適用されます。sh.salesのプリファレンスについて問い合せると、この時点でestimate_percent設定は5であることが示されます。これは、グローバル設定の値です。 

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS 
('sh', 'sales', 
'preference_overrides_parameter', 'true');
 
PL/SQL procedure successfully completed.

SET_TABLE_PREFSを使用して、sh.sales表に対してのみpreference_overrides_parameterプリファレンスをtrueに設定します。 

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS
('sh', 'sales', estimate_percent=>10);

PL/SQL procedure successfully completed.

sh.salesの統計を収集する場合、estimate_percentを10に設定します。ただし、preference_overrides_parameterがsales表に対してtrueに設定され、グローバル・プリファレンスが定義されているため、実際には、Oracle Databaseはグローバル設定の5を使用して統計を収集します。

関連項目:

オプティマイザ統計の設定に関するDBMS_STATSプロシージャについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください

12.2.1.4 統計プリファレンスの設定: 例

この例は、SET_TABLE_PREFS、SET_SCHEMA_STATSおよびSET_DATABASE_PREFSの関係を示しています。

表12-4 統計収集プロシージャのプリファレンスの変更

アクション 説明 
SQL> SELECT DBMS_STATS.GET_PREFS 
('incremental', 'sh','costs') 
AS "STAT_PREFS" FROM DUAL;
 
STAT_PREFS
----------
TRUE

costsのINCREMENTALプリファレンスを問い合せて、このプリファレンスがtrueに設定されているかどうかを確認します。 

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS 
('sh', 'costs', 'incremental', 'false');
 
PL/SQL procedure successfully completed.

SET_TABLE_PREFSを使用して、costs表に対してのみINCREMENTALプリファレンスをfalseに設定します。 

SQL> SELECT DBMS_STATS.GET_PREFS 
('incremental', 'sh', 'costs') 
AS "STAT_PREFS" FROM DUAL;

STAT_PREFS 
----------
FALSE

costsのINCREMENTALプリファレンスを問い合せて、このプリファレンスがfalseに設定されているかどうかを確認します。 

SQL> EXEC DBMS_STATS.SET_SCHEMA_PREFS 
('sh', 'incremental', 'true');

PL/SQL procedure successfully completed.

SET_SCHEMA_PREFSを使用して、shスキーマ内のすべての表(costsなど)のINCREMENTALプリファレンスをtrueに設定します。 

SQL> SELECT DBMS_STATS.GET_PREFS 
('incremental', 'sh', 'costs') 
AS "STAT_PREFS" FROM DUAL;
 
STAT_PREFS
----------
TRUE

costsのINCREMENTALプリファレンスを問い合せて、このプリファレンスがtrueに設定されているかどうかを確認します。 

SQL> EXEC DBMS_STATS.SET_DATABASE_PREFS 
('incremental', 'false');

PL/SQL procedure successfully completed.

SET_DATABASE_PREFSを使用して、すべてのユーザー定義スキーマのすべての表のINCREMENTALプリファレンスをfalseに設定します。 

SQL> SELECT DBMS_STATS.GET_PREFS 
('incremental', 'sh', 'costs') 
AS "STAT_PREFS" FROM DUAL;

STAT_PREFS
----------
FALSE

costsのINCREMENTALプリファレンスを問い合せて、このプリファレンスがfalseに設定されているかどうかを確認します。

12.2.2 Cloud Controlを使用したグローバル・オプティマイザ統計プリファレンスの設定

グローバル・プリファレンスは、既存の表プリファレンスを持たないデータベース内のすべてのオブジェクトに適用されます。Cloud Controlを使用してグローバル・レベルでオプティマイザ統計プリファレンスを設定できます。

Cloud Controlを使用してグローバル・オプティマイザ統計プリファレンスを設定するには:

  1. Cloud Controlで、「データベース・ホーム」ページにアクセスします。

  2. 「パフォーマンス」メニューから、「SQL」→「オプティマイザ統計」を選択します。

    「オプティマイザ統計コンソール」が表示されます。

  3. 「グローバル統計の採取オプション」をクリックします。

    「グローバル統計の採取オプション」ページが表示されます。

  4. 必要な変更を行い、「適用」をクリックします。

関連項目:

Oracle Enterprise Manager Cloud Controlのオンライン・ヘルプを参照してください。

12.2.3 Cloud Controlを使用したオブジェクト・レベルのオプティマイザ統計プリファレンスの設定

Cloud Controlを使用してデータベース、スキーマおよび表の各レベルでオプティマイザ統計プリファレンスを設定できます。

Cloud Controlを使用してオブジェクト・レベルのオプティマイザ統計プリファレンスを設定するには:

  1. Cloud Controlで、「データベース・ホーム」ページにアクセスします。

  2. 「パフォーマンス」メニューから、「SQL」→「オプティマイザ統計」を選択します。

    「オプティマイザ統計コンソール」が表示されます。

  3. 「オブジェクト・レベルの統計採取プリファレンス」をクリックします。

    「オブジェクト・レベルの統計採取プリファレンス」が表示されます。

  4. 表レベルのプリファレンス・セットがある表のプリファレンスを変更するには、次を行います(変更しない場合は次のステップにスキップします)。

    1. 「スキーマ」および「表名」に値を入力します。「表名」を空白のままにすると、スキーマ内のすべての表を表示できます。

      ページが表の名前で更新されます。

    2. 必要な表を選択して、「プリファレンスの編集」をクリックします。

      「プリファレンスの編集」ページの「一般」サブページが表示されます。

    3. 必要に応じてプリファレンスを変更し、「適用」をクリックします。

  5. 表レベルのプリファレンス・セットがない表のプリファレンスを設定するには、次を実行します(設定しない場合は次のステップにスキップします)。

    1. 「表のプリファレンスの追加」をクリックします。

      「表のプリファレンスの追加」ページの「一般」サブページが表示されます。

    2. 「表名」でスキーマと表名を入力します。

    3. 必要に応じてプリファレンスを変更し、「OK」をクリックします。

  6. スキーマのプリファレンスを設定するには、次を実行します。

    1. 「スキーマ表のプリファレンスの設定」をクリックします。

      「スキーマ・プリファレンスの編集」ページの「一般」サブページが表示されます。

    2. 「スキーマ」でスキーマ名を入力します。

    3. 必要に応じてプリファレンスを変更し、「OK」をクリックします。

関連項目:

Oracle Enterprise Manager Cloud Controlのオンライン・ヘルプを参照してください。

12.2.4 コマンドラインからのオプティマイザ統計プリファレンスの設定

Cloud Controlでオプティマイザ統計プリファレンスを設定していない場合、コマンドラインからDBMS_STATSプロシージャを起動できます。

前提条件

このタスクには次の前提条件があります。

  • グローバル・プリファレンスまたはデータベース・プリファレンスを設定するには、SYSDBA権限、またはANALYZE ANY DICTIONARYシステム権限およびANALYZE ANYシステム権限の両方を所有している必要があります。

  • スキーマ・プリファレンスを設定するには、所有者として接続するか、またはSYSDBA権限、あるいはANALYZE ANYシステム権限を所有している必要があります。

  • 表プリファレンスを設定するには、表の所有者として接続するか、またはANALYZE ANYシステム権限を所有している必要があります。

コマンドラインからオプティマイザ統計プリファレンスを設定するには:

  1. SQL*PlusまたはSQL Developerで、必要な権限を持つユーザーとしてデータベースにログインします。

  2. オプションで、DBMS_STATS.GET_PREFSプロシージャをコールして、オブジェクト・レベルで設定されているプリファレンスを確認します。特定の表が設定されていない場合は、グローバル・レベルで設定されているプリファレンスを確認します。

    たとえば、sh.sales表のSTALE_PERCENTパラメータ設定を次のように取得します。 

    SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', 'SH', 'SALES') 
FROM   DUAL;

  3. 表12-1の適切なプロシージャを、次のパラメータを指定して実行します。

    • ownname - スキーマ名の設定(SET_TAB_PREFSおよびSET_SCHEMA_PREFSのみ)

    • tabname - 表名の設定(SET_TAB_PREFSのみ)

    • pname - パラメータ名の設定

    • pvalue - パラメータ値の設定

    • add_sys - システム表の追加(オプションでSET_DATABASE_PREFSのみ)

    次の例では、表の統計が失効しているとみなされる前に、sh.salesの行の13%が変更される必要があることを指定しています。 

    EXEC DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES', 'STALE_PERCENT', '13');

  4. オプションで、*_TAB_STAT_PREFSビューを問い合せて変更を確認します。

    たとえば、次のようにDBA_TAB_STAT_PREFSを問い合せます。 

    COL OWNER FORMAT a5
COL TABLE_NAME FORMAT a15
COL PREFERENCE_NAME FORMAT a20
COL PREFERENCE_VALUE FORMAT a30
SELECT * FROM DBA_TAB_STAT_PREFS;

    出力例は次のように表示されます。

    OWNER TABLE_NAME      PREFERENCE_NAME      PREFERENCE_VALUE
----- --------------- -------------------- -----------------------------
OE    CUSTOMERS       NO_INVALIDATE        DBMS_STATS.AUTO_INVALIDATE
SH    SALES           STALE_PERCENT        13

関連項目:

プログラム・ユニットのパラメータの名前と値の説明については、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください

12.3 動的統計のオプションの構成

動的統計は、データベースが再帰的SQLを使用して表内のブロックの小さなランダム・サンプルをスキャンする最適化手法です。

サンプル・スキャンは、述語の選択性を見積ります。これらの見積りを使用して、データベースは、分析されていないセグメントのより適切なデフォルト統計を決定し、見積りを検証します。デフォルトでは、オプティマイザ統計が存在しないか、失効したか、不十分な場合に、動的統計によって、解析の間に再帰的SQLが自動実行されて、表ブロックの小さなランダム・サンプルがスキャンされます。

この項では、次の項目について説明します。

関連トピック

12.3.1 動的統計レベルについて

動的統計レベルでは、データベースで動的統計を収集するタイミングと、統計の収集にオプティマイザが使用するサンプル・サイズの両方が制御されます。

動的統計レベルを設定するには、OPTIMIZER_DYNAMIC_SAMPLING初期化パラメータまたは文ヒントのいずれかを使用します。

ノート:

Oracle Database 12cリリース1 (12.1)よりも前のリリースでは、動的統計は動的サンプリングと呼ばれていました。

次の表では、動的統計のレベルについて説明しています。次の点に注意してください。

  • 動的統計が有効な場合、SQL文でパラレル実行が使用されていると、データベースによって動的統計を使用することが選択されることがあります。

  • OPTIMIZER_ADAPTIVE_STATISTICSがTRUEの場合、関連するSQL計画ディレクティブが存在するときは、オプティマイザは動的統計を使用します。データベースは、結果の統計をSQL計画ディレクティブ・ストアに維持して、他の問合せに対して使用可能にしています。

表12-5 動的統計レベル

レベル オプティマイザで動的統計を使用するタイミング サンプル・サイズ(ブロック)

0

動的統計を使用しません。

該当なし

1

次の基準を満たす場合のみ、統計を含まないすべての表に動的統計を使用します。

  • 問合せの少なくとも1つのパーティション化されていない表に統計が含まれていない場合。

  • この表に索引がない場合。

  • この表の動的統計で使用されるブロック数よりこの表のブロック数が多くある場合。

32

2

文の少なくとも1つの表に統計がない場合は、動的統計を使用します。これがデフォルト値です。

64

3

次のいずれかの条件に当てはまる場合は、動的統計を使用します。

  • 文の少なくとも1つの表に統計がない場合。

  • WHERE SUBSTR(CUSTLASTNAME,1,3)など、WHERE句の述語で使用される1つ以上の式が文にある場合。

64

4

次のいずれかの条件に当てはまる場合は、動的統計を使用します。

  • 文の少なくとも1つの表に統計がない場合。

  • WHERE SUBSTR(CUSTLASTNAME,1,3)など、WHERE句の述語で使用される1つ以上の式が文にある場合。

  • 文が複雑な述語を使用している(同じ表で複数の述語をORまたはAND演算子でつないでいる)場合。

64

5

基準はレベル4と同一だが、データベースは異なるサンプル・サイズを使用している場合。

128

6

基準はレベル4と同一だが、データベースは異なるサンプル・サイズを使用している場合。

256

7

基準はレベル4と同一だが、データベースは異なるサンプル・サイズを使用している場合。

512

8

基準はレベル4と同一だが、データベースは異なるサンプル・サイズを使用している場合。

1024

9

基準はレベル4と同一だが、データベースは異なるサンプル・サイズを使用している場合。

4086

10

基準はレベル4と同一だが、データベースは異なるサンプル・サイズを使用している場合。

すべてのブロック

11

オプティマイザが必要と判断したときに、データベースが適応動的サンブリングを自動的に使用する場合。

自動的に決定

関連項目:

12.3.2 動的統計レベルの手動設定

すべてのSQL文に有効なデータベースレベルの設定を決定するのは困難な場合があります。

動的統計のレベルを設定する際、OPTIMIZER_DYNAMIC_SAMPLING初期化パラメータをセッション・レベルで設定することをお薦めします。

前提条件

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

  • 次の問合せに対して選択性の見積りを修正する場合で、2つの相関列にWHERE句の述語があります。 

      SELECT *
  FROM   sh.customers
  WHERE  cust_city='Los Angeles'
  AND    cust_state_province='CA';

  • 前述の問合せはシリアル処理を使用します。

  • sh.customers表は、問合せの条件を満たす932の行を含みます。

  • sh.customers表に収集された統計があります。

  • cust_city列とcust_state_province列に索引を作成しています。

  • OPTIMIZER_DYNAMIC_SAMPLING初期化パラメータが、デフォルト・レベルの2に設定されています。

動的統計レベルを手動設定するには:

  1. SQL*PlusまたはSQL Developerで、必要な権限を持つユーザーとしてデータベースにログインします。

  2. 実行計画を次のようにEXPLAINします。

    EXPLAIN PLAN FOR
  SELECT *
  FROM   sh.customers
  WHERE  cust_city='Los Angeles'
  AND    cust_state_province='CA';

  3. その計画を次のように問い合せます。

    SET LINESIZE 130
SET PAGESIZE 0
SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY);

    出力が次のように表示されます(この例はページに収まるように再フォーマットされています)。

    -------------------------------------------------------------------------------
|Id| Operation                   | Name             |Rows|Bytes|Cost | Time   |
-------------------------------------------------------------------------------
| 0| SELECT STATEMENT            |                   | 53| 9593|53(0)|00:00:01|
| 1|  TABLE ACCESS BY INDEX ROWID|CUSTOMERS          | 53| 9593|53(0)|00:00:01|
|*2|   INDEX RANGE SCAN          |CUST_CITY_STATE_IND| 53| 9593| 3(0)|00:00:01|
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA')

    WHERE句の列には実世界の相関関係がありますが、オプティマイザはロサンゼルスがカリフォルニアにあることを認識せず、両方の述語により、戻される行の数が減少すると想定しています。このように、表には条件を満たす932の行が含まれますが、太字で示すようにオプティマイザは53と見積っています。

    データベースでこの計画に動的統計を使用したとすれば、計画出力のNoteセクションでこの事実が指摘されることになります。オプティマイザで動的統計が使用されなかった理由は、文がシリアルに実行されたことや、標準統計が存在することのほか、OPTIMIZER_DYNAMIC_SAMPLINGパラメータがデフォルトの2に設定されていることがあげられます。

  4. 次の文を使用して、セッションで動的統計レベルを4に設定します。 

    ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING=4;

  5. この計画を再び次のようにEXPLAINします。

    EXPLAIN PLAN FOR
  SELECT *
  FROM   sh.customers
  WHERE  cust_city='Los Angeles'
  AND    cust_state_province='CA';

    この新しい計画では、太字で示されている値932のように、行数の見積りがより正確に示されています。 

    PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Plan hash value: 2008213504
 
------------------------------------------------------------------------
| Id  | Operation         | Name      |Rows |Bytes |Cost (%CPU)|Time   |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 932 | 271K|  406 (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS | 932 | 271K|  406 (1)| 00:00:05 |
------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA')
 
Note
-----
   - dynamic statistics used for this statement (level=4)

    計画の一番下のノートは、サンプリング・レベルが4であることを示します。追加された動的統計によって、オプティマイザにcust_city列とcust_state_province列間の実世界の関係を認識させることで、行数が53ではなく932という、より正確な見積りを生成できるようになります。

関連項目:

12.3.3 動的統計の無効化

一般的には、繰返しのないOLTP問合せなど、コンパイル時間を可能なかぎり速くすることが必要な問合せに対して、動的統計のコストが発生しないようにすることがベスト・プラクティスです。

セッション・レベルの動的統計を無効化するには:

  1. 適切な権限でSQL*Plusをデータベースに接続します。

  2. 動的統計レベルを0に設定します。

    たとえば、次の文を実行します。

    ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING=0;

関連項目:

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

12.4 SQL計画ディレクティブの管理

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

ディレクティブは、データベースに対してオプティマイザが特定のタイプの述語のカーディナリティの見積りを誤り、DBMS_STATSに将来の追加統計の収集をアラートすることを通知します。したがって、ディレクティブに統計収集の影響があります。

データベースでは、SGAでSQL計画ディレクティブを自動的に作成して管理し、定期的にデータ・ディクショナリに書き込みます。ディレクティブは、53週間以内に使用されない場合、自動的に消去されます。

DBMS_SPDのプロシージャおよびファンクションを使用してディレクティブを手動で変更、保存、削除および転送できます。次の表に、一般的に使用されるプロシージャおよびファンクションの一部を示します。

表12-6 DBMS_SPDのプロシージャ

プロシージャ 説明

FLUSH_SQL_PLAN_DIRECTIVE

ディレクティブをメモリーからSYSAUX表領域の永続記憶域に強制的に書き込みます。

DROP_SQL_PLAN_DIRECTIVE

SQL計画ディレクティブを削除します。動的サンプリングを発生させるディレクティブが許容できないパフォーマンス上のオーバーヘッドを生成している場合、手動で削除できます。

SQL計画ディレクティブが手動または自動で削除された場合、データベースはそれを再作成できます。再作成を防止するには、DBMS_SPM.ALTER_SQL_PLAN_DIRECTIVEを使用して次を実行します。

  • ENABLEDをNOに設定することでディレクティブを無効化します。

  • AUTO_DROPをNOに設定することで、ディレクティブが削除されることを防止します。

SQL計画ディレクティブを無効化するには、OPTIMIZER_ADAPTIVE_STATISTICSをFALSEに設定します。

前提条件

DBMS_SPDのAPIを実行するには、ADMINISTER SQL MANAGEMENT OBJECT権限が必要です。

前提条件

このチュートリアルでは、次の操作を行うものとします。

  • shスキーマのすべてのディレクティブを永続記憶域に書き込みます。

  • shスキーマのすべてのディレクティブを削除します。

shスキーマのすべての計画ディレクティブを書き込んでから削除するには:

  1. SQL*PlusまたはSQL Developerで、必要な権限を持つユーザーとしてデータベースにログインします。

  2. SQL計画ディレクティブを強制的にディスクに書き込みます。

    たとえば、次のDBMS_SPDプログラムを実行します。 

    BEGIN 
  DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE;
END;
/

  3. データ・ディクショナリを問い合せて、shスキーマの既存のディレクティブに関する情報を確認します。

    例12-3では、ディレクティブに関する情報をデータ・ディクショナリに問い合せます。

  4. shスキーマの既存のSQL計画ディレクティブを削除します。

    次のPL/SQLプログラム・ユニットでは、ID 1484026771529551585を指定してSQL計画ディレクティブを削除します。 

    BEGIN
  DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE ( directive_id => 1484026771529551585 );
END;
/

例12-3 shスキーマのディレクティブの表示

この例では、SQL計画ディレクティブと、SQL計画ディレクティブの動的サンプリング問合せの結果を示します。

SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OWNER, o.OBJECT_NAME, 
       o.SUBOBJECT_NAME col_name, o.OBJECT_TYPE object, d.TYPE, 
       d.STATE, d.REASON
FROM   DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
WHERE  d.DIRECTIVE_ID=o.DIRECTIVE_ID
AND    o.OWNER IN ('SH')
ORDER BY 1,2,3,4,5;
 
DIR_ID              OWN OBJECT_NA COL_NAME   OBJECT  TYPE     STATE      REASON
------------------- --- --------- ---------- ------- -------- ---------- ------------
1484026771529551585  SH CUSTOMERS COUNTRY_ID  COLUMN DYNAMIC_ SUPERSEDED SINGLE TABLE  
                                                     SAMPLING            CARDINALITY 
                                                                         MISESTIMATE
1484026771529551585  SH CUSTOMERS CUST_STATE_ COLUMN DYNAMIC_ SUPERSEDED SINGLE TABLE
                                  PROVINCE           SAMPLING            CARDINALITY 
                                                                         MISESTIMATE
1484026771529551585  SH CUSTOMERS              TABLE DYNAMIC_ SUPERSEDED SINGLE TABLE 
                                                     SAMPLING            CARDINALITY 
                                                                         MISESTIMATE
9781501826140511330  SH dyg4msnst5           SQL STA DYNAMIC_     USABLE VERIFY
                                             TEMENT  SAMPLING            CARDINALITY 
                                                     _RESULT             ESTIMATE
9872337207064898539  SH TIMES                  TABLE DYNAMIC_     USABLE VERIFY
                                                     SAMPLING            CARDINALITY 
                                                     _RESULT             ESTIMATE
9781501826140511330  SH 2nk1v0fdx0           SQL STA DYNAMIC_     USABLE VERIFY
                                             TEMENT  SAMPLING            CARDINALITY 
                                                     _RESULT             ESTIMATE

関連項目: