13 オプティマイザ統計の収集

この章では、DBMS_STATS.GATHER_*_STATSプログラム・ユニットの使用方法について説明します。

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

関連項目:

• 「オプティマイザ統計の概念」

• 「問合せオプティマイザの概念」

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

13.1 自動オプティマイザ統計収集の構成

Oracle Databaseでは、オプティマイザ統計を自動的に収集できます。

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

13.1.1 自動オプティマイザ統計収集について

自動化メンテナンス・タスク・インフラストラクチャ(AutoTask)は、メンテナンス・ウィンドウと呼ばれるOracle Schedulerウィンドウで自動的に実行するタスクをスケジュールします。

デフォルトでは、1つのウィンドウが週の各曜日にスケジュールされます。自動オプティマイザ統計収集はAutoTaskの一部として実行されます。この収集は、デフォルトでは事前に定義されたすべてのメンテナンス・ウィンドウで実行されます。

ノート:

自動オプティマイザ統計収集をプラガブル・データベースとともに使用すると、データの可視性と権限の要件が一致しない場合があります。

オプティマイザ統計を収集する場合、データベースで内部プロシージャがコールされます。このプロシージャは、GATHER_DATABASE_STATSプロシージャをGATHER AUTOオプションを指定して実行する場合と同様に動作します。自動統計収集はデータベース内のすべてのプリファレンス・セットに従います。

手動収集と自動収集の主な違いは、自動収集では統計が必要となるデータベース・オブジェクトが優先される点です。自動収集では、メンテナンス・ウィンドウがクローズされる前にすべてのオブジェクトが評価され、統計がないか、非常に古い統計があるオブジェクトが優先されます。

ノート:

手動で統計を収集する場合は、自動収集のオブジェクト優先付けをDBMS_AUTO_TASK_IMMEDIATEパッケージを使用して再現できます。このパッケージでは、自動夜間統計収集ジョブで実行されるものと同じ統計収集ジョブが実行されます。

関連項目:

コンテナ・データベース(CDB)内での管理機能の動作をまとめた表が記載されている『Oracle Database管理者ガイド』を参照してください

13.1.2 Cloud Controlを使用した自動オプティマイザ統計収集の構成

Cloud Controlを使用して、自動オプティマイザ統計収集など、すべての自動メンテナンス・タスクを有効化および無効化できます。

ほとんどの場合、デフォルトのウィンドウ・タイミングで問題なく機能します。しかし、バルク・ロードなどの操作を、ウィンドウ内で行うことも考えられます。そのような場合、自動統計収集と同時に操作を行うことにより競合が発生するのを回避するには、必要に応じてウィンドウを変更することをお薦めします。

前提条件

「Cloud Controlのデータベース・ホーム・ページへのアクセス」の説明に従って、「データベース・ホーム」ページにアクセスします。

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

1. 「管理」メニューから、「Oracle Scheduler」を選択し、「自動化メンテナンス・タスク」を選択します。

   自動化メンテナンス・タスク・ページが表示されます。

   このページには、事前定義されたタスクが表示されます。各タスクに関する情報を取得するには、タスクに対応するリンクをクリックします。

2. 「構成」をクリックします。

   「自動化メンテナンス・タスク構成」ページが表示されます。

   デフォルトでは、自動オプティマイザ統計収集はMAINTENANCE_WINDOW_GROUP内の事前に定義されたすべてのメンテナンス・ウィンドウで実行されます。

3. 次のステップを実行します。

   1. 「オプティマイザ統計の収集」の「タスク設定」セクションで、「有効」または「無効」のいずれかを選択して自動化タスクを有効化または無効化します。

      ノート:

      自動統計収集は、ディクショナリ・オブジェクトおよびユーザー・オブジェクトに対する問合せの最適な計画をオプティマイザが生成する上で重要であるため、無効にしないことを強くお薦めします。自動収集を無効にする場合は、ディクショナリ・スキーマおよびユーザー・スキーマに対して適切な自動統計収集の方針を立てるようにしてください。

   2. 週の特定の日に統計収集を無効化するには、ウィンドウ名の横にある該当するチェック・ボックスを選択します。

   3. ウィンドウ・グループの特性を変更するには、「ウィンドウ・グループの編集」をクリックします。

   4. ウィンドウの時間を変更するには、ウィンドウの名前(「MONDAY_WINDOW」など)をクリックして、「スケジュール」セクションで「編集」をクリックします。

      ウィンドウの編集ページが表示されます。

      
      図edit_window.jpgの説明

      このページでは、ウィンドウ実行の期間や開始時間などのパラメータを変更できます。

   5. 「適用」をクリックします。

関連項目:

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

13.1.3 コマンドラインからの自動オプティマイザ統計収集の構成

Cloud Controlを使用せずに自動オプティマイザ統計収集を構成する場合は、コマンドラインを使用する必要があります。

ここでは次のオプションを指定できます。

• DBMS_AUTO_TASK_ADMIN PL/SQLパッケージに含まれるENABLEまたはDISABLEプロシージャを実行します。

  コマンドラインで使用する手法としては、このパッケージをお薦めします。ENABLEおよびDISABLEプロシージャのどちらに対しても、window_nameパラメータを使用して特定のメンテナンス・ウィンドウを指定できます。

• STATISTICS_LEVEL(初期化レベル)をBASICに設定して、自動化SQLチューニング・アドバイザなどすべてのアドバイザおよび統計の収集を無効化します。

  ノート:

  監視機能と多くの自動機能が無効になるため、STATISTICS_LEVELはBASICに設定しないことを強くお薦めします。

DBMS_AUTO_TASK_ADMINを使用して自動統計収集を管理するには:

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

2. 次のいずれかの操作を行います。

   • 自動タスクを有効にするには、次のPL/SQLブロックを実行します。

     BEGIN
       DBMS_AUTO_TASK_ADMIN.ENABLE (  
         client_name  => 'auto optimizer stats collection'
     ,   operation    => NULL
     ,   window_name  => NULL 
     );
     END;
     /
     

   • 自動タスクを無効にするには、次のPL/SQLブロックを実行します。

     BEGIN
       DBMS_AUTO_TASK_ADMIN.DISABLE (  
         client_name  => 'auto optimizer stats collection'
     ,   operation    => NULL
     ,   window_name  => NULL 
     );
     END;
     /
     

3. データ・ディクショナリを問い合せて、変更を確認します。

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

   COL CLIENT_NAME FORMAT a31
   
   SELECT CLIENT_NAME, STATUS
   FROM   DBA_AUTOTASK_CLIENT
   WHERE  CLIENT_NAME = 'auto optimizer stats collection';
   

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

   CLIENT_NAME                     STATUS
   ------------------------------- --------
   auto optimizer stats collection ENABLED
   

自動統計収集のウィンドウ属性を変更するには:

1. 管理者権限でSQL*Plusをデータベースに接続します。

2. 必要に応じてメンテナンス・ウィンドウの属性を変更します。

   たとえば、月曜日のメンテナンス・ウィンドウが午前5時に開始されるように変更するには、次のPL/SQLプログラムを実行します。

   BEGIN 
     DBMS_SCHEDULER.SET_ATTRIBUTE (
       'MONDAY_WINDOW'
   ,   'repeat_interval'
   ,   'freq=daily;byday=MON;byhour=05;byminute=0;bysecond=0'
   );
   END;
   /

関連項目:

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

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

13.2 オプティマイザ統計の手動収集

自動統計収集の代替または補足として、DBMS_STATSパッケージを使用してオプティマイザ統計を手動で収集できます。

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

関連項目:

• 「自動オプティマイザ統計収集の構成」

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

13.2.1 DBMS_STATSを使用した手動統計収集について

DBMS_STATSパッケージを使用してオプティマイザ統計を操作します。オブジェクト、スキーマおよびデータベースなどの様々なレベルに細分化してオブジェクトおよび列の統計を収集できます。また、物理システムの統計を収集できます。

次の表では、オプティマイザ統計収集のDBMS_STATSプロシージャの概要が示されています。このパッケージでは、表クラスタの統計は収集されません。ただし、表クラスタの個々の表の統計は収集できます。

表13-1 オプティマイザ統計収集のDBMS_STATSプロシージャ

プロシージャ	目的
GATHER_INDEX_STATS	索引統計を収集します
GATHER_TABLE_STATS	表、列および索引の各統計を収集します。
GATHER_SCHEMA_STATS	スキーマのすべてのオブジェクトの統計を収集します。
GATHER_DICTIONARY_STATS	SYSやSYSTEMなどのすべてのシステム・スキーマのほか、CTXSYSやDRSYSなどのオプション・スキーマの統計を収集します。
GATHER_DATABASE_STATS	データベース内ですべてのオブジェクトの統計を収集します。

OPTIONSパラメータがGATHER STALEまたはGATHER AUTOに設定されている場合は、失効した統計を含むすべての表および統計がないすべての表の統計がGATHER_SCHEMA_STATSプロシージャおよびGATHER_DATABASE_STATSプロシージャで収集されます。監視対象の表の変更が10%を超えた場合は、これらの統計が失効していると判断され、再び統計が収集されます。

ノート:

「自動オプティマイザ統計収集の構成」の説明に従って、夜間に統計を自動収集するジョブを構成できます。

関連項目:

• 「システム統計の手動収集」

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

13.2.2 オプティマイザ統計の手動収集のガイドライン

適度な速度で変更されるデータベース・オブジェクトに対しては、ほとんどの場合、自動収集で十分です。

次の表に示すように、自動収集が適切でないか使用できない場合があります。

表13-2 手動で統計を収集する理由

問題	さらに学習するには
特定のタイプのバルク・ロードを実行する際に、問合せを即座に実行する必要があるために、統計収集のメンテナンス・ウィンドウを待機できない場合。	「バルク・ロードのためのオンライン統計収集」
代表的ではないワークロードを実行しているときに、固定表の統計を自動統計収集で収集する場合。	「固定オブジェクトの統計情報の収集」
自動統計収集はシステム統計を収集しません。	「システム統計の手動収集」
日中に削除または切り捨てられて再作成される変化しやすい表。	「動的統計を使用した変化しやすい表の統計の収集」

この項では、手動での統計収集を選択することになる一般的な状況に対してガイドラインを提供します。

13.2.2.1 サンプル・サイズの設定のガイドライン

オプティマイザ統計において、サンプリングとは表の行のランダムなサブセットから統計を収集することです。サンプリングでは、データベースによる全表スキャンと表全体のソートを避けることができるため、統計収集に必要なリソースが最小化されます。

データベースでは、表内のすべての表が処理される場合、つまり100%のサンプルが取得される場合に最も正確な統計が収集されます。ただし、サンプル・サイズが大きくなるほど、統計収集操作にかかる時間が増加します。課題は、妥当な時間で正確な統計を取得できるようなサンプル・サイズを決定することです。

DBMS_STATSでは、サンプリングする表内の行の割合を制御するESTIMATE_PERCENTパラメータをユーザーが指定した場合にサンプリングが使用されます。パフォーマンスの向上を最大化するとともに必要な統計の正確性を達成するには、ESTIMATE_PERCENTパラメータでデフォルト設定のDBMS_STATS.AUTO_SAMPLE_SIZEを使用することをお薦めします。この場合、Oracle Databaseは、サンプル・サイズを自動的に選択します。この設定により、次の使用が有効になります。

• サンプリングよりはるかに高速なハッシュ・ベースのアルゴリズム

  このアルゴリズムではすべての行が読み取られ、100%のサンプルから生成された統計と同じぐらい正確な統計が生成されます。この手法を使用して計算された統計は確定的です。

• 増分統計

• 同時統計

• 新規のヒストグラム・タイプ

DBA_TABLES.SAMPLE_SIZE列は、統計を収集するために使用される実際のサンプル・サイズを示します。

関連項目:

• ハイブリッド・ヒストグラム

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

13.2.2.2 パラレル統計収集のガイドライン

デフォルトでは、表または索引レベルに指定された並列度で統計が収集されます。

この設定は、DBMS_STATS収集プロシージャのdegree引数で上書きできます。degreeをDBMS_STATS.AUTO_DEGREEに設定することをお薦めします。この設定を使用すると、オブジェクト・サイズおよびパラレル化関連の初期化パラメータに基づいて適切な並列度が選択されます。

データベースでは、ほとんどの統計をシリアルまたはパラレルで収集できます。ただし、クラスタ索引、ドメイン索引およびビットマップ結合索引などの一部の索引統計は、データベースによってパラレルで収集されることはありません。データベースでは、パラレルでの統計収集時にサンプリングを使用できます。

ノート:

統計のパラレル収集と同時収集を混同しないでください。

関連項目:

• 「同時統計収集について」

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

13.2.2.3 パーティション・オブジェクトのガイドライン

パーティション表および索引に対して、DBMS_STATSは、各パーティションの個別の統計を収集できます。また、全表または全索引のグローバル統計も収集できます。

コンポジット・パーティションについても同様に、DBMS_STATSはサブパーティション、パーティション、全表および全索引の個別の統計を収集できます。

収集するパーティション統計のタイプを決定するには、DBMS_STATSプロシージャのgranularity引数を指定します。granularityをデフォルト値のAUTOに設定し、パーティションのタイプに応じてサブパーティション統計、パーティション統計またはグローバル統計を収集することをお薦めします。ALL設定では、すべてのタイプの統計が収集されます。

関連項目:

「パーティション・オブジェクトの増分統計の収集」

13.2.2.4 変更頻度の高いオブジェクトのガイドライン

表の変更が頻繁に行われる場合は、表が失効しないように十分な回数の統計を収集しますが、収集のオーバーヘッドでパフォーマンスが低下しない回数にします。

新しい統計の収集が週に一度または月に一度しか必要がない場合もあります。ベスト・プラクティスは、スクリプトまたはジョブ・スケジューラを使用して、DBMS_STATS.GATHER_SCHEMA_STATSプロシージャおよびDBMS_STATS.GATHER_DATABASE_STATSプロシージャを定期的に実行することです。

13.2.2.5 外部表のガイドライン

外部表に対するデータ操作は許可されないため、データベースで外部表の統計が失効としてマークされることはありません。たとえば基礎となるデータファイルが変更されたなどの理由で、外部表に新しい統計が必要な場合は、統計を再収集します。

外部表の場合、内部表に使用する同じDBMS_STATSプロシージャを使用します。DBMS_STATS.SET_TABLE_STATSおよびDBMS_STATS.GET_TABLE_STATSのscanrateパラメータは、Oracle Databaseが表のデータをスキャンする速度(MB/秒)を指定し、外部表にのみ関係します。SCAN_RATE_列は、DBA_TAB_STATISTICSおよびDBA_TAB_PENDING_STATSデータ・ディクショナリ・ビューに表示されます。

関連項目:

• 「テスト用の人為的なオプティマイザ統計の作成」

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

• DBA_TAB_STATISTICSビューについて学習するには、Oracle Databaseリファレンスを参照してください

13.2.3 オプティマイザ統計が失効するタイミングの判別

表に失効した統計があるということは、表のデータを正確に反映していないということになります。データベース・オブジェクトに新しいデータベース統計が必要かどうかの判断に役立つように、データベースには表監視機能が提供されています。

監視では、最新の統計収集以降の、表でのDML操作の概数と、表が切り捨てられたかどうかが追跡されます。統計が失効しているかどうかを確認するには、DBA_TAB_STATISTICSおよびDBA_IND_STATISTICSのSTALE_STATS列を問い合せます。この列は、DBA_TAB_MODIFICATIONSビューおよびDBMS_STATSのSTALE_PERCENTプリファレンスのデータに基づきます。

ノート:

Oracle Database 12cリリース2 (12.2)から、DBMS_STATS.FLUSH_DATABASE_MONITORING_INFOを使用することでビュー・メタデータが最新であることを確認する必要がなくなりました。DBA_TAB_STATISTICS、DBA_IND_STATISTICSおよびDBA_TAB_MODIFICATIONSビューに示される統計は、ディスクおよびメモリーの両方から取得されています。

STALE_STATS列の値は、次のとおりです。

• YES

  統計が失効しています。

• NO

  統計は失効していません。

• null

  統計が収集されていません。

GATHER AUTOオプションを指定してGATHER_SCHEMA_STATSまたはGATHER_DATABASE_STATSを実行すると、統計がないか失効しているオブジェクトのみの統計が収集されます。

失効した統計を判別するには:

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

2. データ・ディクショナリを問い合せて失効している統計を確認します。

   次の例では、sh.sales表で失効している統計を問い合せます(出力の一部を含む)。

   COL PARTITION_NAME FORMAT a15
   
   SELECT PARTITION_NAME, STALE_STATS
   FROM   DBA_TAB_STATISTICS
   WHERE  TABLE_NAME = 'SALES'
   AND    OWNER = 'SH'
   ORDER BY PARTITION_NAME;
    
   PARTITION_NAME  STA
   --------------- ---
   SALES_1995      NO
   SALES_1996      NO
   SALES_H1_1997   NO
   SALES_H2_1997   NO
   SALES_Q1_1998   NO
   SALES_Q1_1999   NO
   .
   .
   .

関連項目:

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

13.2.4 スキーマ統計および表統計の収集

表統計を収集するにはGATHER_TABLE_STATSを使用し、スキーマ内のすべてのオブジェクトの統計を収集するにはGATHER_SCHEMA_STATSを使用します。

DBMS_STATSを使用してスキーマ統計を収集するには:

1. SQL*Plusを起動し、実行するプロシージャに対する適切な権限でデータベースに接続します。

2. GATHER_TABLE_STATSプロシージャまたはGATHER_SCHEMA_STATSプロシージャを実行して、必要なパラメータを指定します。

   一般的には次のパラメータがあります。

   • 所有者 - ownname

   • オブジェクト名 - tabname、indname、partname

   • 並列度 - degree

例13-1 表統計の収集

この例では、DBMS_STATSパッケージを使用して、並列設定が2のsh.customers表の統計を収集します。

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS (  
    ownname => 'sh'
,   tabname => 'customers'
,   degree  => 2  
);
END;
/

関連項目:

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

13.2.5 固定オブジェクトの統計情報の収集

固定オブジェクトとは、動的パフォーマンス表とその索引です。これらのオブジェクトは、現在のデータベース・アクティビティを記録します。

他のデータベース表とは異なり、オプティマイザ統計が見つからない場合にX$表を参照するSQL文の動的統計が自動的に使用されることはありません。かわりに、オプティマイザは事前定義されたデフォルト値を使用します。このデフォルト値は代表的なものではない場合があり、最適ではない実行計画につながる可能性があります。したがって、固定オブジェクトの統計を最新のものにしておくことが重要です。

Oracle Databaseは、固定オブジェクトの統計が以前に収集されていない場合、自動統計収集の一部として自動的に収集します。DBMS_STATS.GATHER_FIXED_OBJECTS_STATSをコールして手動で固定オブジェクトの統計を収集することもできます。統計は、代表的なアクティビティがデータベースで行われているときに収集することをお薦めします。

前提条件

このプロシージャを実行するには、SYSDBAまたはANALYZE ANY DICTIONARYシステム権限が必要です。

GATHER_FIXED_OBJECTS_STATSを使用してスキーマ統計を収集するには:

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

2. 必要なパラメータを指定して、DBMS_STATS.GATHER_FIXED_OBJECTS_STATSプロシージャを実行します。

   一般的には次のパラメータがあります。

   • 現在の統計情報の保存場所を示す表の識別子: stattab

   • stattab内の統計情報を関連付ける識別子(オプション): statid

   • stattabを含んだスキーマ(現在のスキーマと異なる場合): statown

例13-2 表統計の収集

この例では、DBMS_STATSパッケージを使用して固定オブジェクトの統計を収集します。

BEGIN
  DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/

関連項目:

• 「自動オプティマイザ統計収集の構成」

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

13.2.6 動的統計を使用した変化しやすい表の統計の収集

変化しやすい表(日中の間に著しく変更される表)の統計はすぐに失効します。たとえば、表は削除または切捨てが行われたり、再構築されたりする可能性があります。

変化しやすいオブジェクトの統計をNULLに設定した場合は、Oracle Databaseでは動的統計を使用して最適化中に必要な統計が動的に収集されます。OPTIMIZER_DYNAMIC_SAMPLING初期化パラメータでこの機能を制御します。

前提条件

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

• oe.orders表は非常に変化しやすい表です。

• orders表の統計を削除した上でロックし、データベースでその表の統計が収集されないようにします。この方法では、データベースによって、必要な統計を問合せ最適化の一部として動的に収集できます。

• oeユーザーは、DBMS_XPLAN.DISPLAY_CURSORの問合せに必要な権限を持っています。

オプティマイザ統計を削除およびロックするには:

1. oeユーザーとしてデータベースに接続した上でoe表の統計を削除します。

   たとえば、次のプロシージャを実行します。

   BEGIN
     DBMS_STATS.DELETE_TABLE_STATS('OE','ORDERS');
   END;
   /
   

2. oe表の統計をロックします。

   たとえば、次のプロシージャを実行します。

   BEGIN
     DBMS_STATS.LOCK_TABLE_STATS('OE','ORDERS');
   END;
   /
   

3. orders表を問い合せます。

   たとえば、次の文を使用します。

   SELECT COUNT(order_id) FROM orders;
   

4. カーソル内の計画を問い合せます。

   次のコマンドを実行します(出力の一部を含む)。

   SET LINESIZE 150
   SET PAGESIZE 0
   
   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
   
   SQL_ID  aut9632fr3358, child number 0
   -------------------------------------
   SELECT COUNT(order_id) FROM orders
    
   Plan hash value: 425895392
    
   ---------------------------------------------------------------------
   | Id  | Operation          | Name   | Rows  | Cost (%CPU)| Time     |
   ---------------------------------------------------------------------
   |   0 | SELECT STATEMENT   |        |       |     2 (100)|          |
   |   1 |  SORT AGGREGATE    |        |     1 |            |          |
   |   2 |   TABLE ACCESS FULL| ORDERS |   105 |     2   (0)| 00:00:01 |
   ---------------------------------------------------------------------
    
   Note
   -----
      - dynamic statistics used for this statement (level=2)
   

   前述の実行計画のノートには、データベースでSELECT文の動的統計を使用したことが示されています。

関連項目:

• 「動的統計のオプションの構成」

• 代表的な統計を収集してロックする処理(変化しやすい表の統計が失効しないようにするための代替的な手法)方法を学習するには、「オプティマイザ統計のロックおよびロック解除」を参照してください

13.2.7 オプティマイザ統計の同時収集

Oracle Databaseでは、複数の表またはパーティションの統計を同時に収集できます。

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

13.2.7.1 同時統計収集について

デフォルトでは、パーティション表の各パーティションは順に収集されます。

同時統計収集モードを有効にすると、スキーマ内の複数の表、表内の複数のパーティションまたはサブパーティションに対して、オプティマイザ統計をデータベースで同時に収集できます。データベースで複数のプロセッサをすべて使用可能にすることで、同時性により、統計の収集に必要な全体の時間を減らすことができます。

ノート:

同時統計収集モードはパラレル問合せ処理に依存しませんが、その使用は可能です。

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

13.2.7.1.1 DBMS_STATSによる統計の同時収集の方法

Oracle Databaseでは、複数のツールおよびテクノロジを使用して、複数の統計収集ジョブを同時に作成および管理します。

データベースでは次を使用します。

• Oracle Scheduler

• Oracle Database Advanced Queuing(AQ)

• Oracle Database Resource Manager(Resource Manager)

DBMS_STATS.SET_GLOBAL_PREFを使用してCONCURRENTプリファレンスを設定することで同時統計収集を有効化します。

データベースでは可能なかぎり多くの同時ジョブが実行されます。Job Schedulerは、同時に実行するジョブの数およびキューに入れるジョブの数を決定します。ジョブの実行が完了すると、データベースですべての表、パーティションおよびサブパーティションの統計の収集が完了するまで、スケジューラによってさらにジョブがデキューされ実行されます。ジョブの最大数は、JOB_QUEUE_PROCESSES初期化パラメータと使用可能なシステム・リソースによる制約を受けます。

ほとんどの場合、DBMS_STATSプロシージャによって表のパーティションまたはサブパーティションごとに別のジョブが作成されます。ただし、パーティションまたはサブパーティションが空または非常に小さい場合には、ジョブ・メンテナンスのオーバーヘッドを減らすために、データベースで自動的にそのオブジェクトと他の小さなオブジェクトを1つのジョブに一括する場合があります。

次の図には異なるレベルでのジョブの作成が示されています。表3はパーティション化された表で、その他の表はパーティション化されていません。ジョブ3は、表3のコーディネータ・ジョブとして動作し、その表のパーティションごとにジョブを作成するほか、表3のグローバル統計用に別のジョブを作成します。次の例では、増分統計収集が有効化されていることを前提としています。無効化されている場合、パーティションのジョブの完了後にグローバル統計パーティション・レベルの統計からグローバル統計が導出されます。

図13-1 同時統計収集ジョブ

「図13-1 同時統計収集ジョブ」の説明

関連項目:

• 同時統計収集の有効化

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

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

13.2.7.1.2 同時統計収集とリソースの管理

DBMS_STATSパッケージでは、ユーザーが開始した統計収集コールの一部である同時統計収集ジョブによって使用されるリソースを明示的に管理することはありません。

したがって、データベースでは、同時統計収集の間にシステム・リソースをすべて使用する場合があります。この状況に対処するには、Resource Managerを使用して同時統計収集ジョブで消費するリソースに上限を設けます。Resource Managerで同時統計収集を有効にしておく必要があります。

システムによって提供されるコンシューマ・グループであるORA$AUTOTASKには、すべての統計収集ジョブが登録されています。ORA$AUTOTASKに対して適切なリソースが割り当てられているリソース計画を作成して、同時統計収集で使用可能なリソースがすべて消費されることを回避できます。独自のリソース計画が存在せず、かつそれを作成しない場合は、システムによって提供されるDEFAULT_PLANを含むResource Managerのアクティブ化を検討してください。

ノート:

ORA$AUTOTASKコンシューマ・グループは、メンテナンス・ウィンドウ中に自動実行されるメンテナンス・タスクと共有されます。したがって、自動統計収集で同時実行性がアクティブ化されている場合は、追加のステップを要することなく、データベースでリソースが自動管理されます。

関連項目:

Resource Managerについて学習するには、Oracle Database管理者ガイドを参照してください

13.2.7.2 同時統計収集の有効化

同時統計収集を有効化するには、DBMS_STATS.SET_GLOBAL_PREFSプロシージャを使用してCONCURRENTプリファレンスを設定します。

有効な値は次のとおりです。

• MANUAL

  手動統計収集でのみ、同時実行性が有効化されます。

• AUTOMATIC

  自動統計収集でのみ、同時実行性が有効化されます。

• ALL

  手動と自動の両方の統計収集で、同時実行性が有効化されます。

• OFF

  手動と自動の両方の統計収集で、同時実行性が無効化されます。これはデフォルト値です。

この項のこのチュートリアルでは、同時統計収集を有効化する方法について説明します。

前提条件

このチュートリアルの前提条件は、次のとおりです。

• 統計収集の標準的な権限に加えて、次の権限を所有している必要があります。

  • CREATE JOB

  • MANAGE SCHEDULER

  • MANAGE ANY QUEUE

• SYSAUX表領域をオンラインにする必要があるのは、スケジューラでその内部表とビューをこの表領域に格納するためです。

• JOB_QUEUE_PROCESSES初期化パラメータを少なくとも4に設定する必要があります。

• Resource Managerを有効化する必要があります。

  デフォルトでは、Resource Managerは無効になっています。リソース計画が存在しない場合は、システムによって提供されるDEFAULT_PLANを含むResource Managerの有効化を検討してください。

前提条件

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

• 同時統計収集の有効化

• shスキーマの統計の収集

• sh統計の収集の監視

同時統計収集を有効化するには:

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

   次の例では、Resource Managerのデフォルトの計画が使用されています。

   ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DEFAULT_PLAN';
   

2. JOB_QUEUE_PROCESSES初期化パラメータをCPUのコア数の少なくとも2倍に設定します。

   Oracle Real Application Clustersでは、JOB_QUEUE_PROCESSES設定が各ノードに適用されます。

   システムに4個のCPUコアがあると想定します。次の例では、パラメータを8(コア数の2倍)に設定します。

   ALTER SYSTEM SET JOB_QUEUE_PROCESSES=8;
   

3. パラメータの変更が有効になったことを確認します。

   たとえば、SQL*Plusに次のコマンドを入力します(出力例も示します)。

   SHOW PARAMETER PROCESSES;
   
   NAME                             TYPE        VALUE
   -------------------------------- ----------- -----
   _high_priority_processes         string      VKTM
   aq_tm_processes                  integer     1
   db_writer_processes              integer     1
   gcs_server_processes             integer     0
   global_txn_processes             integer     1
   job_queue_processes              integer     8
   log_archive_max_processes        integer     4
   processes                        integer     100
   

4. 同時統計を有効化します。

   たとえば、次のPL/SQL無名ブロックを実行します。

   BEGIN
     DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','ALL');
   END;
   /
   

5. 統計が有効化されたことを確認します。

   たとえば、次の問合せを実行します(出力例も示します)。

   SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL;
   
   DBMS_STATS.GET_PREFS('CONCURRENT')
   ----------------------------------
   ALL
   

6. SHスキーマの統計を収集します。

   たとえば、次のプロシージャを実行します。

   EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SH');
   

7. 別のセッションでは、DBA_OPTSTAT_OPERATION_TASKSを問い合せることでジョブの進行状況を監視します。

   たとえば、次の問合せを実行します(出力例も示します)。

   SET LINESIZE 1000
    
   COLUMN TARGET FORMAT a8
   COLUMN TARGET_TYPE FORMAT a25
   COLUMN JOB_NAME FORMAT a14
   COLUMN START_TIME FORMAT a40
    
   SELECT TARGET, TARGET_TYPE, JOB_NAME, 
          TO_CHAR(START_TIME, 'dd-mon-yyyy hh24:mi:ss')
   FROM   DBA_OPTSTAT_OPERATION_TASKS 
   WHERE  STATUS = 'IN PROGRESS' 
   AND    OPID = (SELECT MAX(ID) 
                  FROM   DBA_OPTSTAT_OPERATIONS 
                  WHERE  OPERATION = 'gather_schema_stats');
   
   TARGET    TARGET_TYPE               JOB_NAME       TO_CHAR(START_TIME,'
   --------- ------------------------- -------------- --------------------
   SH.SALES  TABLE (GLOBAL STATS ONLY) ST$T292_1_B29  30-nov-2012 14:22:47
   SH.SALES  TABLE (COORDINATOR JOB)   ST$SD290_1_B10 30-nov-2012 14:22:08
   

8. 元のセッションでは、同時統計収集を無効化します。

   たとえば、次の問合せを実行します。

   EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','OFF');

関連項目:

• 「統計収集操作の監視」

• Oracle Database管理者ガイド

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

13.2.7.3 統計収集操作の監視

データ・ディクショナリ・ビューを使用して、統計収集ジョブを監視できます。

次のビューが関連します。

• DBA_OPTSTAT_OPERATION_TASKS

  このビューには、統計収集操作の一部として実行されるまたは現在実行されているタスクの履歴が含まれています(DBA_OPTSTAT_OPERATIONSに記録されます)。各タスクは、親操作に対応して処理されるターゲット・オブジェクトを示します。

• DBA_OPTSTAT_OPERATIONS

  このビューには、DBMS_STATSパッケージを使用して表、スキーマおよびデータベース・レベルで実行されるまたは現在実行されている統計操作の履歴が含まれています。

前述のビューのTARGET列には、統計収集ジョブのターゲット・オブジェクトが次の形式で表示されます。

OWNER.TABLE_NAME.PARTITION_OR_SUBPARTITION_NAME

統計収集ジョブの名前はすべて文字列のST$で開始されます。

現在実行されている統計タスクおよびジョブを表示するには:

• 現在実行されている統計収集タスクをすべてのユーザー・セッションからリストするには、次のSQL文を使用します(出力例も示します)。

  SELECT OPID, TARGET, JOB_NAME, 
         (SYSTIMESTAMP - START_TIME) AS elapsed_time
  FROM   DBA_OPTSTAT_OPERATION_TASKS
  WHERE  STATUS = 'IN PROGRESS';
  
  OPID TARGET                    JOB_NAME      ELAPSED_TIME
  ---- ------------------------- ------------- --------------------------
   981 SH.SALES.SALES_Q4_1998    ST$T82_1_B29  +000000000 00:00:00.596321
   981 SH.SALES                  ST$SD80_1_B10 +000000000 00:00:27.972033
  

完了した統計タスクおよびジョブを表示するには:

• 特定の操作の完了したタスクおよびジョブのみをリストするには、まず統計収集操作の名前、ターゲットおよび開始時刻に基づいてDBA_OPTSTAT_OPERATIONSビューで操作IDを特定します。操作IDを特定したら、DBA_OPTSTAT_OPERATION_TASKSビューを問い合せて、その操作で対応するタスクを検索します。

  たとえば、ID 981の操作をリストするには、SQL*Plusで次のコマンドを使用します(出力例も示します)。

  VARIABLE id NUMBER
  EXEC :id := 981
  
  SELECT TARGET, JOB_NAME, (END_TIME - START_TIME) AS ELAPSED_TIME
  FROM   DBA_OPTSTAT_OPERATION_TASKS
  WHERE  STATUS <> 'IN PROGRESS'
  AND    OPID = :id;
  
  TARGET                    JOB_NAME      ELAPSED_TIME
  ------------------------- ------------- --------------------------
  SH.SALES_TRANSACTIONS_EXT               +000000000 00:00:45.479233
  SH.CAL_MONTH_SALES_MV     ST$SD88_1_B10 +000000000 00:00:45.382764 
  SH.CHANNELS               ST$SD88_1_B10 +000000000 00:00:45.307397
  

失敗した統計収集タスクおよびジョブを表示するには:

• 次のSQL文を使用します(出力例の一部も示します)。

  SET LONG 10000
  
  SELECT TARGET, JOB_NAME AS NM,
         (END_TIME - START_TIME) AS ELAPSED_TIME, NOTES
  FROM   DBA_OPTSTAT_OPERATION_TASKS
  WHERE  STATUS = 'FAILED';
  
  TARGET             NM ELAPSED_TIME               NOTES
  ------------------ -- -------------------------- -----------------
  SYS.OPATCH_XML_INV    +000000007 02:36:31.130314 <error>ORA-20011:
                                                   Approximate NDV
                                                   failed: ORA-29913:
                                                   error in

関連項目:

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

13.2.8 パーティション・オブジェクトの増分統計の収集

増分統計では、変更されたパーティションのみがスキャンされます。パーティション・レベルの統計からグローバル統計を導出して大規模パーティション表の統計を収集する場合、増分統計メンテナンスによってパフォーマンスが向上します。

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

13.2.8.1 増分統計の目的

一般的な例では、アプリケーションによってレンジ・パーティション表の新規パーティションにデータがロードされます。アプリケーションで新しいパーティションの追加とデータのロードが行われると、データベースでは、新しいパーティションの統計を収集し、グローバル統計を最新の状態に維持することが必要になります。

通常、データ・ウェアハウス・アプリケーションは、大規模パーティション表にアクセスします。多くの場合、これらの表は日付列でパーティション化され、頻繁なDML変更の影響を受けるのは最近のパーティションのみです。増分統計を使用しない場合、通常は、統計収集で次の2段階アプローチが使用されます。

1. データベースで表をスキャンしてグローバル統計を収集する。

   グローバル統計収集のための全表スキャンでは、表のサイズによっては非常に負荷がかかる可能性があります。表にパーティションが追加されるに従い、GATHER_TABLE_STATSの実行時間が長くなります。これは、グローバル統計には全表スキャンが必要になるためです。パーティションの小規模なサブセットのみを変更する場合でも、データベースで表全体のスキャンを実行する必要があります。

2. データベースで変更されたパーティションをスキャンしてパーティション・レベルの統計を収集する。

増分メンテナンスでは、次の理由でデータ・ウェアハウス・アプリケーションの場合に大きなパフォーマンス上の利点があります。

• データベースでは、表を1回のみスキャンしてパーティション統計を収集し、パーティション・レベルの統計を集計することによりグローバル統計を導出する必要があります。これにより、データベースは、増分統計を使用しない場合に必要な2つの全スキャン(パーティション・レベルの統計スキャンとグローバル・レベルの統計スキャン)を回避します。

• 後続の統計収集で、データベースは、失効したパーティションのスキャンと統計(シノプシスを含む)の更新のみが必要です。データベースは、新しいパーティション統計からグローバル統計を導出できるため、全表スキャンが不要です。

増分統計を使用する場合、データベースは、グローバルまたは表レベルの統計を変更するパーティションで引き続き統計を収集する必要があります。増分統計メンテナンスは、最初からの表統計の収集と同じ統計が得られますが、パフォーマンスが向上します。

13.2.8.2 DBMS_STATSによるパーティション表のグローバル統計の導出方法

増分統計メンテナンスが有効な場合、DBMS_STATSでは、変更されたパーティションのみの統計を収集し、シノプシスを作成します。また、データベースは、パーティション・レベルのシノプシスをグローバル・シノプシスに自動的にマージし、パーティション・レベルの統計およびグローバル・シノプシスからグローバル統計を導出します。

データベースでパーティション・レベルの統計からグローバル統計を導出して計算する際に全表スキャンが回避されます。たとえば、グローバル・レベルの行数はパーティションの行数の合計です。グローバル・ヒストグラムもパーティションのヒストグラムから導出できます。

ただし、列の NDVなど、パーティション・レベルの統計からすべての統計を導出することはできません。次の例は、表の2つのパーティションのNDVを示しています。

表13-2 3つのパーティションのNDV

オブジェクト	列値	NDV
Partition 1	1,3,3,4,5	4
Partition 2	2,3,4,5,6	5

個々のパーティションのNDVを加算して表のNDVを計算するとNDVは9となりますが、これは正しくありません。したがって、より正確な手法のシノプシスが必要です。

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

13.2.8.2.1 パーティション・レベルのシノプシス

シノプシスは、パーティションの列ごとに個別値の数(NDV)を追跡する特殊なタイプの統計です。個別値をサンプリングする内部管理構造としてシノプシスをみなすことができます。

パーティション・レベルのシノプシスをマージすることにより、各列のグローバル・レベルのNDVを正確に導出できます。表13-3の例では、データベースでシノプシスを使用して列のNDVを6として計算できます。

各パーティションは、増分モードでシノプシスを維持します。表に新しいパーティションが追加された場合は、新しいパーティションの統計のみを収集する必要があります。データベースは、既存のパーティションのシノプシスとともに新しいパーティションのシノプシスを集計することにより、グローバル統計を自動的に更新します。後続の統計収集操作は、シノプシスを使用しない場合よりも高速です。

データベースでは、SYSAUX表領域のデータ・ディクショナリ表WRI$_OPTSTAT_SYNOPSIS_HEAD$およびWRI$_OPTSTAT_SYNOPSIS$にシノプシスを格納します。DBA_PART_COL_STATISTICSディクショナリ・ビューには、パーティションの列統計の情報が含まれます。NOTES列にキーワードINCREMENTALが含まれている場合、この列にシノプシスが含まれています。

関連項目:

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

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

Oracle Database 12cリリース2 (12.2)から、HyperLogLogアルゴリズムにより、NDV (個別値の数)計算パフォーマンスを改善できます。また、シノプシスに必要な記憶域も削減できます。

NDVを計算するレガシー・アルゴリズムでは、適応サンプリングを使用します。シノプシスは、個別値のサンプルです。NDVを計算する場合、データベースは最初に各個別値をハッシュ表に格納します。個別値は個別のハッシュ・バケットを占有するため、5000の個別値を持つ列には5000のハッシュ・バケットがあります。データベースは次にハッシュ・バケットの数を半分にし、少数のバケットが残るまで継続して結果を半分にします。ハッシュ表分割の数に基づいてサンプリング・レートが変更されるため、アルゴリズムは適応しています。

列のNDVを計算するため、データベースは次の式を使用します。Bは、すべての分割が実行された後に残るハッシュ・バケットの数です。Sは分割の数です。

NDV = B * 2^S

適応サンプリングは正確なNDV統計を生成しますが、次の影響があります。

• 特に表に多数の列とパーティションが含まれ、各列のNDVが高い場合、シノプシスは大量のディスク領域を占有します。

  たとえば、列ごとの平均NDVが5,000の60列の表に300,000パーティションがあります。この例では、各パーティションに300,000エントリ(60 x 5000)が含まれます。合計で、シノプシス表には、少なくとも720GBの記憶域を占有する900億エントリ(300,000の二乗)が含まれます。

• シノプシスの一括処理は、パフォーマンスに悪影響を与える可能性があります。

  データベースが失効したパーティションの統計を再収集する前に、関連付けられたシノプシスを削除する必要があります。大量のUNDOおよびREDOデータが生成されるため、一括削除は遅くなる可能性があります。

動的サンプリングとは対照的に、HyperLogLogアルゴリズムはランダム化手法を使用します。アルゴリズムは複雑ですが、基本的な理解はランダム値のストリームでn個別値が平均1/n離れているということです。したがって、ストリームの最小値がわかっている場合、個別値の数を大まかに見積もることができます。たとえば、値が0から1の範囲であり、確認されている最小値が.2である場合、数値は平均して.2ずつ均等に離れるため、NDVの見積りは5です。

HyperLogLogアルゴリズムは、元の見積りを拡張して修正します。データベースはハッシュ関数を各列値に適用するため、一連のハッシュ値が列と同じカーディナリティになります。基本的な見積りの場合、NDVは2ⁿと等しくなります。nはバイナリ表現のハッシュ値で確認された後続のゼロの最大数です。データベースでは、出力の一部を使用して値を異なるハッシュ・バケットに分割して、NDV見積りを絞り込みます。

適応サンプリングと比較したHyperLogLogアルゴリズムの利点は次のとおりです。

• 新しいアルゴリズムの正確性は、元のアルゴリズムと似ています。

• 必要なメモリーは非常に少なく、一般的にシノプシス・サイズの大幅な削減につながります。

  多数のパーティションが存在し、NDVが高い多数の列がある場合、シノプシスが大きくなる可能性があります。HyperLogLogアルゴリズムを使用するシノプシスは、よりコンパクトです。シノプシスを作成および削除するとバッチの実行時間に影響があります。パーティションを管理する操作プロシージャにより、実行時間が削減されます。

DBMS_STATSプリファレンスAPPROXIMATE_NDV_ALGORITHMにより、データベースがNDV計算に使用するアルゴリズムが決定されます。

関連項目:

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

13.2.8.2.3 シノプシスを使用したグローバル統計の集計: 例

この例では、データベースでsales表の最初の6個のパーティションの統計が収集され、各パーティションのシノプシス(S1、S2など)が作成されます。データベースでは、パーティション・レベルの統計およびシノプシスを集計してグローバル統計が作成されます。

図13-2 集計統計

「図13-2 集計統計」の説明

次の図は、sales表に追加される新しいパーティションを示しています。このパーティションには5月24日のデータが格納されます。データベースでは、新しく追加されたパーティションの統計の収集、他のパーティションのシノプシスの取得、グローバル統計を作成するためのシノプシスの集計が行われます。

図13-3 パーティション追加後の集計統計

「図13-3 パーティション追加後の集計統計」の説明

13.2.8.3 パーティション表の統計の収集: 基本ステップ

この項では、パーティション表のオプティマイザ統計の収集方法について説明します。

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

13.2.8.3.1 増分統計メンテナンスの考慮事項

増分統計メンテナンスを有効にすると、様々な影響があります。

特に、次の点に注意してください。

• 表がコンポジット・パーティションを使用する場合は、データベースは変更されたサブパーティションの統計のみを収集します。データベースは、未変更のサブパーティションに対しては、サブパーティション・レベルの統計を収集しません。このようにして、データベースは未変更のパーティションをスキップして作業を減らします。

• 表が増分統計を使用しており、この表にローカルでパーティション化された索引がある場合は、データベースは、変更された(未変更でない)索引パーティションに対してグローバル・レベルで索引統計を収集します。データベースは、パーティション・レベルの統計からグローバル索引統計を生成することはしません。そのかわりに、データベースは全索引スキャンを実行してグローバル索引統計を収集します。

• SYSAUX表領域では、パーティション表のグローバル統計を維持するために追加の領域が使用されます。

関連項目:

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

13.2.8.3.2 SET_TABLE_PREFSを使用した増分統計の有効化

パーティション表の増分統計メンテナンスを有効化するには、DBMS_STATS.SET_TABLE_PREFSを使用してINCREMENTAL値をtrueに設定します。INCREMENTALをfalse(デフォルト)に設定すると、データベースでは全表スキャンを使用してグローバル統計が維持されます。

変更されたパーティションのみをスキャンしてグローバル統計を増分的に更新するデータベースでは、次の条件を満たす必要があります。

• パーティション表のPUBLISH値がtrueです。

• パーティション表のINCREMENTAL値がtrueです。

• 統計収集プロシージャでは、ESTIMATE_PERCENTにAUTO_SAMPLE_SIZE、GRANULARITYにAUTOを指定する必要があります。

例13-3 増分統計の有効化

パーティション表sh.salesのPUBLISH値がtrueであるとします。次のプログラムにより、この表の増分統計が有効になります。

EXEC DBMS_STATS.SET_TABLE_PREFS('sh', 'sales', 'INCREMENTAL', 'TRUE');

13.2.8.3.3 APPROXIMATE_NDV_ALGORITHM設定について

DBMS_STATS.APPROXIMATE_NDV_ALGORITHMプリファレンスは、シノプシス生成アルゴリズム(HyperLogLogまたは適応サンプリング)を指定します。INCREMENTAL_STALENESSプリファレンスは、適応サンプリング形式を使用するシノプシスを再フォーマットする時期を制御します。

APPROXIMATE_NDV_ALGORITHMプリファレンスには、次の指定可能な値があります。

• REPEAT OR HYPERLOGLOG

  これはデフォルトです。INCREMENTALが表で有効である場合、データベースは適応サンプリング・アルゴリズムを使用する既存のシノプシスの形式を保存します。ただし、データベースはHyperLogLog形式で新しいシノプシスを作成します。既存のパフォーマンスが許容範囲であり、レガシー・コンテンツを再フォーマットするパフォーマンス・コストを発生させたくない場合、この方法が有益です。

• ADAPTIVE SAMPLING

  データベースは、すべてのシノプシスの適応サンプリング・アルゴリズムを使用します。これは最も保守的なオプションです。

• HYPERLOGLOG

  データベースでは、新しいシノプシスおよび失効したシノプシスのすべてにHyperLogLogアルゴリズムを使用します。

INCREMENTAL_STALENESSプリファレンスは、シノプシスが失効とみなされる時期を制御します。APPROXIMATE_NDV_ALGORITHMプリファレンスがHYPERLOGLOGに設定されている場合、次のINCREMENTAL_STALENESS設定が適用されます。

• ALLOW_MIXED_FORMAT

  これはデフォルトです。この値が指定され、次の条件が満たされている場合、データベースは既存の適応サンプリング・シノプシスを失効とみなしません。

  • シノプシスが新規です。

  • 手動で統計を収集します。

  したがって、レガシー形式およびHyperLogLog形式のシノプシスは共存できます。ただし、一定期間で、自動統計収集ジョブは、古い形式を使用するシノプシスの統計を再収集し、HyperLogLog形式のシノプシスに置き換えます。これにより、自動統計収集ジョブは徐々に古い形式を除去します。手動統計収集ジョブは、適応サンプリング形式を使用するシノプシスを再フォーマットしません。

• Null

  レガシー形式のシノプシスを使用したパーティションは失効とみなされ、ただちにデータベースは失効したシノプシスの統計を再収集します。利点は、パフォーマンス・コストの発生が1回のみであることです。短所は、大きい表のすべての統計を再収集するとリソース集中型になる可能性があることです。

13.2.8.3.4 シノプシス生成の構成: 例

これらの例は、シノプシスを新しいHyperLogLog形式に切り替える積極的および保守的の2つの異なる方法を示します。

例13-4 シノプシスを再フォーマットする保守的な方法

この例では、sh.sales表に対して複合フォーマットのシノプシスの共存を許可します。複合フォーマットでは、統計の正確性が低下します。ただし、表のすべてのパーティションの統計を再収集する必要はありません。

新しいシノプシスおよび失効したシノプシスのすべてにHyperLogLogアルゴリズムを使用するようにするには、APPROXIMATE_NDV_ALGORITHMプリファレンスをHYPERLOGLOGに設定します。自動統計収集ジョブが一定期間に失効したシノプシスを徐々に再フォーマットするようにするには、INCREMENTAL_STALENESSプリファレンスをALLOW_MIXED_FORMATに設定します。

BEGIN
  DBMS_STATS.SET_TABLE_PREFS
    (   ownname => 'sh'
    ,   tabname => 'sales'
    ,   pname   => 'approximate_ndv_algorithm'
    ,   pvalue  => 'hyperloglog' );

  DBMS_STATS.SET_TABLE_PREFS
    (  ownname  => 'sh'
    ,  tabname  => 'sales'
    ,  pname    => 'incremental_staleness'
    ,  pvalue   => 'allow_mixed_format' );
END;

例13-5 シノプシスを再フォーマットする積極的な方法

この例では、すべてのシノプシスで強制的にsh.sales表にhyperloglogアルゴリズムを使用します。この場合、データベースは表のすべてのパーティションの統計を再収集する必要があります。

新しいシノプシスおよび失効したシノプシスのすべてにHyperLogLogアルゴリズムを使用するようにするには、APPROXIMATE_NDV_ALGORITHMプリファレンスをHYPERLOGLOGに設定します。データベースで表のすべてのパーティションの統計をただちに再収集し、新しい形式で格納するように強制するには、INCREMENTAL_STALENESSプリファレンスをnullに設定します。

BEGIN
  DBMS_STATS.SET_TABLE_PREFS
    (   ownname => 'sh'
    ,   tabname => 'sales'
    ,   pname   => 'approximate_ndv_algorithm'
    ,   pvalue  => 'hyperloglog' );

  DBMS_STATS.SET_TABLE_PREFS
    (  ownname  => 'sh'
    ,  tabname  => 'sales'
    ,  pname    => 'incremental_staleness'
    ,  pvalue   => 'null' );
END;

13.2.8.4 パーティション・メンテナンス操作の増分統計の維持

パーティション・メンテナンス操作は、表パーティションの追加、交換、マージ、分割など、パーティション関連の操作です。

Oracle Databaseにより、増分統計メンテナンスの次のサポートが提供されます。

• パーティション・メンテナンス操作により統計の収集がトリガーされた場合、データベースでは、以前は古いセグメントで削除されていたシノプシスを再利用できるようになりました。

• DBMS_STATSによって非パーティション表にシノプシスを作成できるようになりました。シノプシスによって、データベースでは増分統計をパーティション交換操作の一部として維持できます。パーティション交換後にパーティションの統計を明示的に収集する必要はありません。

表でDBMS_STATSプリファレンスのINCREMENTALをtrueに設定した場合は、INCREMENTAL_LEVELプリファレンスによって、どのシノプシスをいつ収集するのかが制御されます。このプリファレンスの値は、次のいずれかです。

• TABLE

  DBMS_STATSによって当該の表にある表レベルのシノプシスが収集されます。INCREMENTAL_LEVELに設定できるのは、表レベルのTABLEのみです。スキーマ、データベースまたはグローバル・レベルには設定できません。

• PARTITION (デフォルト)

  DBMS_STATSによってパーティション表のパーティション・レベルのシノプシスのみが収集されます。

パーティション交換の実行時に、交換対象のパーティションを交換した後でシノプシスを使用する場合は、パーティションと交換する表でINCREMENTALをtrueに、INCREMENTAL_LEVELをTABLEに設定することをお薦めします。

前提条件

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

• 空のパーティションp_sales_01_2010をsales表にロードします。

• ステージング表t_sales_01_2010を作成し、その表に値を移入します。

• データベースで、増分統計をパーティション交換操作の一部として維持します。パーティション交換後にパーティションの統計を明示的に収集する必要はありません。

増分統計をパーティション交換操作の一部として維持するには:

1. ステージング表t_sales_01_2010に対する増分統計のプリファレンスを設定します。

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

   BEGIN
     DBMS_STATS.SET_TABLE_PREFS (   
         ownname  =>  'sh'
   ,     tabname  =>  't_sales_01_2010'
   ,     pname    =>  'INCREMENTAL'
   ,     pvalue   =>  'true'
   ); 
     DBMS_STATS.SET_TABLE_PREFS (  
         ownname  =>  'sh'
   ,     tabname  =>  't_sales_01_2010'
   ,     pname    =>  'INCREMENTAL_LEVEL'
   ,     pvalue   =>  'table'
   );
   END;
   

2. ステージング表t_sales_01_2010の統計を収集します。

   たとえば、次のPL/SQLコードを実行します。

   BEGIN
     DBMS_STATS.GATHER_TABLE_STATS ( 
         ownname  => 'SH'
   ,     tabname  => 'T_SALES_01_2010'
   );
   END;
   /
   

   DBMS_STATSによってt_sales_01_2010の表レベルのシノプシスが収集されます。

3. sh.sales表でINCREMENTALプリファレンスがtrueになるようにします。

   たとえば、次のPL/SQLコードを実行します。

   BEGIN
     DBMS_STATS.SET_TABLE_PREFS (
         ownname  =>  'sh'
   ,     tabname  =>  'sales'
   ,     pname    =>  'INCREMENTAL'
   ,     pvalue   =>  'true'
   );
   END;
   /
   

4. INCREMENTALをtrueに設定する前にsh.salesの統計を収集していない場合は、交換するパーティションの統計を収集します。

   たとえば、次のPL/SQLコードを実行します。

   BEGIN
     DBMS_STATS.GATHER_TABLE_STATS (
         ownname  =>  'sh'
   ,     tabname  =>  'sales'
   ,     pname    =>  'p_sales_01_2010'
   ,     pvalue   =>  granularity=>'partition'
   );
   END;
   /
   

5. パーティション交換を実行します。

   たとえば、次のSQL文を使用します。

   ALTER TABLE sales EXCHANGE PARTITION p_sales_01_2010 WITH TABLE t_sales_01_2010;
   

   交換後は、パーティション化された表に、パーティションp_sales_01_2010の統計とシノプシスの両方が存在するようになります。

   Oracle Database 12cより前のリリースでは、前述の文によってp_sales_01_2010とt_sales_01_2010のセグメント・データおよび統計を入れ替えていました。データベースでは、t_sales_01_2010などのパーティション化されていない表のシノプシスを維持していませんでした。パーティション化された表のグローバル統計を収集するには、p_sales_01_2010のパーティションを再スキャンしてそのシノプシスを取得する必要がありました。

関連項目:

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

13.2.8.5 失効したまたはロックされたパーティション統計のある表の増分統計の維持

Oracle Database 12cから、たとえパーティションまたはサブパーティションの統計が失効したりロックされても、パーティション化された表のグローバル統計を増分統計によって自動計算できるようになりました。

Oracle Database 12cより前のリリースで増分統計が有効化されている場合は、パーティションでDMLが実行されると、オプティマイザは、そのパーティションの統計が失効したと判断します。したがって、グローバル統計を正確に集計するには、DBMS_STATSで再び統計を収集する必要があります。さらに、統計がロックされたパーティションでDMLが実行される場合は、DBMS_STATSでそのパーティションの統計を再収集できないため、全表スキャンのみがグローバル統計を収集する手段となります。統計の再収集では、パフォーマンスにオーバーヘッドが生じます。

Oracle Database 12cでは、パーティションまたはサブパーティションの統計が失効しているかどうかを判別する方法が、統計プリファレンスのINCREMENTAL_STALENESSで制御されます。このプリファレンスの値は、次のいずれかです。

• USE_STALE_PERCENT

  DMLの変更が表に対して指定したSTALE_PERCENTプリファレンスを下回る場合はパーティションまたはサブパーティションが失効しているとはみなされません。STALE_PERCENTのデフォルト値は10です。これはDMLによって10%を超える行で変更が生じると、その表が失効しているとみなされることを意味します。

• USE_LOCKED_STATS

  ロックされたパーティションまたはサブパーティションの統計は、DMLの変更にかかわらず、失効しているとみなされることはありません。

• NULL (デフォルト)

  DMLに変更が生じた場合は、パーティションまたはサブパーティションが失効しているとみなされます。この動作はOracle Database 11gの動作と同じです。デフォルト値を使用する場合、増分モードで収集される統計は非増分モードで収集される統計と同じであることが保証されます。デフォルト値以外が使用されると、増分モードで収集された統計は、非増分モードで収集された統計よりも精度が低くなる可能性があります。

USE_STALE_PERCENTとUSE_LOCKED_STATSは合わせて指定できます。たとえば、次のような無名ブロックを記述できます。

BEGIN
  DBMS_STATS.SET_TABLE_PREFS (
    ownname     => null
,   table_name  => 't'
,   pname       => 'incremental_staleness'
,   pvalue      => 'use_stale_percent,use_locked_stats'
);
END;

前提条件

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

• パーティション化された表のSTALE_PERCENTが10に設定されています。

• INCREMENTAL値はtrueに設定されています。

• 表には、以前にINCREMENTALモードで収集された統計があります。

• INCREMENTAL_STALENESSの設定や統計のロックの有無、DMLの変更割合に応じて統計収集がどのように変化するのかを確認します。

失効したまたはロックされたパーティション統計のある表をテストするには:

1. INCREMENTAL_STALENESSをNULLに設定します。

   その後、DMLアクティビティのため、1つのパーティションの5%の行が変更されます。

2. DBMS_STATSを使用して表の統計を収集します。

   DBMS_STATSによって、5%のDMLアクティビティが含まれたパーティションの統計が再収集され、グローバル統計が増分的に維持されます。

3. INCREMENTAL_STALENESSをUSE_STALE_PERCENTに設定します。

   その後、DMLアクティビティのため、1つのパーティションの5%の行が変更されます。

4. DBMS_STATSを使用して表の統計を収集します。

   DBMS_STATSによって、DMLアクティビティが含まれていたパーティションの統計が再収集されることはなく(変更が10%の失効のしきい値を下回るため)、グローバル統計が増分的に維持されます。

5. パーティション統計をロックします。

   その後、DMLアクティビティのため、1つのパーティションの20%の行が変更されます。

6. DBMS_STATSを使用して表の統計を収集します。

   統計がロックされているため、DBMS_STATSによってパーティションの統計が再収集されることはありません。データベースでは、全表スキャンを使用してグローバル統計が収集されます。

   その後、DMLアクティビティのため、1つのパーティションの5%の行が変更されます。

7. DBMS_STATSを使用して表の統計を収集します。

   この表の統計が収集される場合は、統計が失効しているとみなされないため、DBMS_STATSではパーティションの統計が再収集されません。データベースでは、このパーティションの既存の統計を使用してグローバル統計が増分的に維持されます。

8. INCREMENTAL_STALENESSをUSE_LOCKED_STATSおよびUSE_STALE_PERCENTに設定します。

   その後、DMLアクティビティのため、1つのパーティションの20%の行が変更されます。

9. DBMS_STATSを使用して表の統計を収集します。

   USE_LOCKED_STATSが設定されているため、DBMS_STATSは、統計が失効している状態を無視し、ロックされた統計を使用します。データベースでは、このパーティションの既存の統計を使用してグローバル統計が増分的に維持されます。

関連項目:

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

13.3 システム統計の手動収集

システム統計は、オプティマイザに対してハードウェア特性(I/OとCPUのパフォーマンスおよび使用率など)を記述します。

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

13.3.1 システム統計について

システム統計によってCPUおよび記憶域のパフォーマンスが測定され、オプティマイザでは計画を評価するときにこれらの入力を使用できます。

問合せが実行されるときにはCPUが消費されます。多くの場合、問合せによって、記憶域サブシステムのリソースも消費されます。一般的な問合せでは、計画ごとに、CPUとI/Oを消費する割合が異なる場合があります。オプティマイザは、コスト・メトリックを使用して、最も高速に実行されると見積もった計画を選択します。オプティマイザがCPUおよび記憶域の速度を認識している場合は、各代替計画のコストをより的確に判断できます。

次の図に、3つの使用可能な計画がある問合せを示します。プランごとに、使用するCPUおよびI/Oの量が異なります。この例では、オプティマイザはPlan 1に最も低いコストを割り当てています。

図mm-3242_tgsql_pb_001b.epsの説明

データベースは、インスタンスの初回起動時に、非作業負荷統計と呼ばれる重要なシステム統計を自動的に収集します。通常、これらの特性が変更されるのは、ハードウェア構成の一部がアップグレードされた場合のみです。

次の図は、パフォーマンスが高い記憶域を追加した後の同じデータベースを示しています。システム統計を収集することで、オプティマイザが記憶域のパフォーマンスを考慮できます。この例では、パフォーマンスが高い記憶域によって、Plan 2とPlan 3の相対コストが大幅に低くなっています。Plan 1は使用するI/Oが少ないため、パフォーマンスの向上はごくわずかです。ここでは、Plan 3に最も低いコストが割り当てられています。

図mm-3242_tgsql_pb_002b.epsの説明

高速I/Oインフラストラクチャを備えたシステムでは、システム統計によって、問合せが索引アクセス方式よりも表スキャンを選択する可能性が高くなります。

13.3.2 システム統計収集のガイドライン

手動で収集する十分な理由がないかぎり、システム統計のデフォルトを使用することをお薦めします。

システム統計は、データベースで実行されるすべてのSQL文に影響を及ぼすため、パフォーマンスにとって重要です。システム統計を変更すると、予期しない、または望ましくない方法で、SQL実行計画が変更される可能性があります。そのため、システム統計を変更する前にオプションを慎重に検討することをお薦めします。

システム統計の手動収集を検討する場合

Oracle Exadataを使用し、かつ、データベースが純粋なデータ・ウェアハウス負荷を実行している場合は、表スキャンの方が優先度が高いため、EXADATAオプションを使用してシステム統計を収集することで、パフォーマンスが向上する可能性があります。ただし、Exadataでも、ほとんどの作業負荷にデフォルトが最適です。

Oracle Exadataを使用しない場合に、システム統計を手動で収集するときには、次のことをお薦めします。

• CPUの高速化、メモリーの増設、ディスク記憶域の交換など、環境に物理的な変化が生じたときにシステム統計を収集します。他のどの表領域でも使用されていない記憶域に新しい表領域を作成した場合は、非作業負荷統計を収集することをお薦めします。

• システムの作業負荷が最も一般的であるときに統計を取得します。作業負荷統計の収集には、さらなるオーバーヘッドは発生しません。

デフォルト統計の使用を検討する場合

ほとんどの場合、システム統計のデフォルトを使用することをお薦めします。システム統計をデフォルト値に戻すには、DBMS_STATS.DELETE_SYSTEM_STATSを実行し、データベースを停止した後、再度オープンします。適切なデフォルトが確実に使用されるようにするには、新しく作成したデータベースでもこのステップを実行することをお薦めします。

13.3.3 DBMS_STATSを使用したシステム統計の収集

システム統計を手動で収集するには、DBMS_STATS.GATHER_SYSTEM_STATSプロシージャを使用します。

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

13.3.3.1 GATHER_SYSTEM_STATSプロシージャについて

DBMS_STATS.GATHER_SYSTEM_STATSプロシージャは、指定された期間のアクティビティを分析するか(作業負荷統計)、作業負荷をシミュレートします(非作業負荷統計)。

DBMS_STATS.GATHER_SYSTEM_STATSへの入力引数には次があります。

• NOWORKLOAD

  オプティマイザによって、作業負荷とは無関係に、システムの特性のみに基づいた統計が収集されます。

• INTERVAL

  指定された時間(分)が経過すると、オプティマイザによって、データ・ディクショナリまたは代替の表のいずれか(stattabで指定)のシステム統計が更新されます。統計は、指定された時間間隔のシステム・アクティビティに基づきます。

• STARTおよびSTOP

  STARTは統計収集を初期化します。STOPは、(START以降の)経過期間の統計を算出し、データ・ディクショナリまたは代替の表(stattabで指定)をリフレッシュします。オプティマイザではINTERVALは無視されます。

• EXADATA

  システム統計では、大きなI/Oサイズや高いI/Oスループットなど、Exadataを使用することで提供されるすぐれた機能が考慮されます。オプティマイザでは、CPU速度に加えて、マルチブロック読取りカウントやI/Oスループットの統計が設定されます。

次の表に、DBMS_STATSで収集されるオプティマイザのシステム統計と、特定のシステム統計を収集または手動設定するためのオプションを示します。

表13-4 DBMS_STATSパッケージ内のオプティマイザのシステム統計

パラメータ名	説明	初期化	統計の収集または設定のオプション	単位
cpuspeedNW	作業負荷のない場合のCPU速度を表します。CPU速度は、1秒当たりのCPU平均サイクル数です。	システム起動時	gathering_mode = NOWORKLOADに設定するか、または統計を手動で設定します。	100万/秒
ioseektim	ディスク・ヘッドがデータの読取り位置に移動する時間を表します。I/Oシーク時間は、シーク時間、待機時間およびオペレーティング・システム・オーバーヘッド時間を合計したものです。	システム起動時 10(デフォルト)	gathering_mode = NOWORKLOADに設定するか、または統計を手動で設定します。	ミリ秒
iotfrspeed	1回の読取りリクエストでOracleデータベースによるデータの読取りが可能な速度を表します。	システム起動時 4096(デフォルト)	gathering_mode = NOWORKLOADに設定するか、または統計を手動で設定します。	バイト/ミリ秒
cpuspeed	作業負荷をかけた場合のCPU速度を表します。CPU速度は、1秒当たりのCPU平均サイクル数です。	なし	gathering_mode = NOWORKLOAD、INTERVAL、START|STOPに設定するか、統計を手動で設定します。	100万/秒
maxthr	最大I/Oスループットは、I/Oサブシステムが発揮できる最大スループットです。	なし	gathering_mode = NOWORKLOAD、INTERVAL、START|STOPに設定するか、統計を手動で設定します。	バイト/秒
slavethr	スレーブI/Oスループットは、パラレル実行サーバーの平均I/Oスループットです。	なし	gathering_mode = INTERVALまたはSTART|STOPに設定するか、統計を手動で設定します。	バイト/秒
sreadtim	単一ブロック読取り時間は、単一ブロックをランダムに読み取る平均時間です。	なし	gathering_mode = INTERVALまたはSTART|STOPに設定するか、統計を手動で設定します。	ミリ秒
mreadtim	マルチブロック読取りは、マルチブロックを順に読み取る平均時間です。	なし	gathering_mode = INTERVALまたはSTART|STOPに設定するか、統計を手動で設定します。	ミリ秒
mbrc	マルチブロック・カウントは、平均マルチブロック順次読取りカウントです。	なし	gathering_mode = INTERVALまたはSTART|STOPに設定するか、統計を手動で設定します。	ブロック

関連項目:

システム統計を収集および削除するためのDBMS_STATSパッケージのプロシージャの詳細は、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください

13.3.3.2 作業負荷統計の収集

データベースの作業負荷が最も一般的であるときにDBMS_STATS.GATHER_SYSTEM_STATSを使用して統計を取得することをお薦めします。

たとえば、データベースのアプリケーションで日中にOLTPトランザクションを処理し、夜間にOLAPレポートを生成できます。

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

13.3.3.2.1 作業負荷統計について

作業負荷統計は指定時間内のアクティビティを分析します。

作業負荷統計には、表13-4にリストされている次の統計が含まれています。

• 単一ブロック(sreadtim)およびマルチブロック(mreadtim)の読取り時間

• マルチブロック・カウント(mbrc)

• CPU速度(cpuspeed)

• 最大システム・スループット(maxthr)

• パラレル実行の平均スループット(slavethr)

データベースでは、作業負荷の開始から終了までの間の、物理的な順次およびランダム読取りの数を比較して、sreadtim、mreadtimおよびmbrcを計算します。これらの値は、バッファ・キャッシュが同期読取りリクエストを完了したときに変更されるカウンタを通して実装されます。

このカウンタはバッファ・キャッシュ内にあるため、これらにはI/O遅延のほかに、ラッチの競合およびタスク・スイッチングに関連する待機も含まれます。したがって、作業負荷統計は、作業負荷ウィンドウ中のシステム・アクティビティに依存します。システムがI/Oバウンド(ラッチ競合およびI/Oスループットの両方)の場合は、データベースで統計を使用した後に、統計によってI/O集中を低減化する計画が促進されます。

図13-4に示すように、作業負荷統計を収集する場合には、オプティマイザでは、作業負荷統計のために収集されたmbrc値が使用されて、全表スキャンのコストが見積られます。

図13-4 作業負荷統計カウンタ

「図13-4 作業負荷統計カウンタ」の説明

作業負荷統計を収集する際に、一連の作業負荷の間に表のスキャンが行われない(OLTPシステムではよく発生します)場合は、mbrc値およびmreadtim値がデータベースで収集されない可能性があります。しかし、全表スキャンはDSSシステムで頻繁に行われています。これらのスキャンは並列に実行され、バッファ・キャッシュを経由しない場合があります。このようなケースでは、索引検索でバッファ・キャッシュを使用するため、データベースでもsreadtimが収集されています。

mbrcまたはmreadtim値を収集できないか、または収集してもそれらを検証できない場合、sreadtimおよびcpuspeed値が収集されていると、sreadtimおよびcpuspeedのみがコスト計算に使用されます。この場合、オプティマイザは初期化パラメータDB_FILE_MULTIBLOCK_READ_COUNTの値を使用して全表スキャンを見積ることができます。ただし、DB_FILE_MULTIBLOCK_READ_COUNTが0であるか、設定されていない場合は、オプティマイザで値8を使用してコストを計算します。

作業負荷統計を収集するには、DBMS_STATS.GATHER_SYSTEM_STATSプロシージャを使用します。GATHER_SYSTEM_STATSプロシージャでは、経過した期間に応じた統計を使用してデータ・ディクショナリまたはステージング表をリフレッシュします。収集する期間を設定するには、次の手法のいずれかを使用します。

• 作業負荷ウィンドウの開始にSTART 指定し、作業負荷ウィンドウの終了にSTOPを指定します。

• 統計収集が自動的に停止する前に、INTERVALとその時間間隔(分単位)を指定します。必要な場合は、GATHER_SYSTEM_STATS (gathering_mode=>'STOP')を使用することでスケジュールよりも早く収集を終了できます。

関連項目:

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

13.3.3.2.2 システム統計収集の開始および停止

このチュートリアルでは、GATHER_SYSTEM_STATSのSTARTパラメータとSTOPパラメータを使用した作業負荷の時間間隔を設定する方法について説明します。

前提条件

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

• 10 a.m.と11 a.m.の間の時間が毎日の作業負荷の標準時間です。

• データ・ディクショナリにあるシステム統計を直接収集します。

STARTおよびSTOPを使用して作業負荷統計を収集するには:

1. SQL*Plusを起動し、管理者権限でデータベースに接続します。

2. 統計収集を開始します。

   たとえば、10 a.m.に次のプロシージャを実行して収集を開始します。

   EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode => 'START' );
   

3. 作業負荷を生成します。

4. 統計収集を終了します。

   たとえば、11 a.m.に次のプロシージャを実行して収集を終了します。

   EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode => 'STOP' );
   

   ここで、オプティマイザでは、作業負荷統計を使用して、日々の通常の作業負荷の間に有効な実行計画を生成できるようになります。

5. オプションで、システム統計を問い合せます。

   たとえば、次の問合せを実行します。

   COL PNAME FORMAT a15
   SELECT PNAME, PVAL1 
   FROM   SYS.AUX_STATS$ 
   WHERE  SNAME = 'SYSSTATS_MAIN';

関連項目:

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

13.3.3.2.3 指定された間隔でのシステム統計の収集

このチュートリアルでは、GATHER_SYSTEM_STATSのINTERVALパラメータを使用した作業負荷の時間間隔を設定する方法について説明します。

前提条件

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

• データベースのアプリケーションで日中にOLTPトランザクションを処理し、夜間にOLAPレポートを実行します。代表的な統計を収集するには、日中の2時間と夜間の2時間で収集します。

• workload_statsという名前の表に統計を格納します。

• 収集された統計間の切替えを行います。

INTERVALを使用して作業負荷統計を収集するには:

1. SQL*Plusを起動して、本番データベースに管理者dba1として接続します。

2. 本番統計を保持するための表を作成します。

   たとえば、次のPL/SQLプログラムを実行して、ユーザー統計表workload_statsを作成します。

   BEGIN
     DBMS_STATS.CREATE_STAT_TABLE (
         ownname  =>  'dba1'
   ,     stattab  =>  'workload_stats'
   );
   END;
   /
   

3. JOB_QUEUE_PROCESSESが0でないことを確認して、DBMS_JOBジョブとOracle Schedulerジョブが実行されるようにします。

   ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 1;
   

4. 日中の間に統計を収集します。

   たとえば、次のプログラムを使用して2時間分の統計を収集します。

   BEGIN
     DBMS_STATS.GATHER_SYSTEM_STATS ( 
         interval  =>  120
   ,     stattab   => 'workload_stats'
   ,     statid    => 'OLTP' 
   );
   END;
   /
   

5. 夜の間に統計を収集します。

   たとえば、次のプログラムを使用して2時間分の統計を収集します。

   BEGIN
     DBMS_STATS.GATHER_SYSTEM_STATS (
         interval  =>  120
   ,     stattab   => 'workload_stats' 
   ,     statid    => 'OLAP' 
   );
   END;
   /
   

6. 日中または夜に、適切な統計をデータ・ディクショナリにインポートします。

   たとえば、日中に次のプログラムを使用して、ステージング表からディクショナリにOLTP統計をインポートできます。

   BEGIN
     DBMS_STATS.IMPORT_SYSTEM_STATS (
         stattab  =>  'workload_stats'
   ,     statid   =>  'OLTP' 
   );
   END;
   /
   

   たとえば、夜の間に次のプログラムを使用して、ステージング表からディクショナリにOLAP統計をインポートできます。

   BEGIN
     DBMS_STATS.IMPORT_SYSTEM_STATS (
         stattab  =>  'workload_stats'
   ,     statid   =>  'OLAP' 
   );
   END;
   /

関連項目:

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

13.3.3.3 非作業負荷統計の収集

非作業負荷統計ではI/Oシステムの特性を取得します。

デフォルトでは、Oracle Databaseで非作業負荷統計とCPUコスト・モデルが使用されます。非作業負荷統計の値は、インスタンスの初回起動時にデフォルトで初期化されます。また、DBMS_STATS.GATHER_SYSTEM_STATSプロシージャを使用して、非作業負荷統計を手動で収集できます。

非作業負荷統計には、表13-4にリストされている次のシステム統計が含まれています。

• I/O転送速度(iotfrspeed)

• I/Oシーク時間(ioseektim)

• CPU速度(cpuspeednw)

作業負荷統計と非作業負荷統計の主な違いは、収集方法にあります。非作業負荷統計ではすべてのデータ・ファイルに対してランダムな読取りを実行してデータを収集する一方で、作業負荷統計ではデータベース・アクティビティが発生した際に更新されるカウンタを使用します。作業負荷統計を収集する場合には、Oracle Databaseでは非作業負荷統計ではなく作業負荷統計が使用されます。

非作業負荷統計を収集するには、引数を含まないか、収集モードをnoworkloadに設定した状態でDBMS_STATS.GATHER_SYSTEM_STATSを実行します。非作業負荷統計の収集プロセスの際に、I/Oシステムにオーバーヘッドが発生します。この収集プロセスは、I/Oのパフォーマンスおよびデータベースのサイズによって数秒から数分かかることがあります。

非作業負荷統計を収集した場合は、データベースでその情報が分析され、その整合性が検証されます。場合によっては、非作業負荷統計の値がデフォルト値のままになることがあります。再び統計を収集するか、またはSET_SYSTEM_STATSを使用してその値をI/Oシステムの仕様に手動設定するかのいずれが可能です。

前提条件

このチュートリアルでは、非作業負荷統計を手動で収集するものとします。

非作業負荷統計を手動収集するには:

1. SQL*Plusを起動し、管理者権限でデータベースに接続します。

2. 非作業負荷統計を収集します。

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

   BEGIN 
     DBMS_STATS.GATHER_SYSTEM_STATS ( 
       gathering_mode => 'NOWORKLOAD' 
   );
   END;
   

3. オプションで、システム統計を問い合せます。

   たとえば、次の問合せを実行します。

   COL PNAME FORMAT a15
   
   SELECT PNAME, PVAL1 
   FROM   SYS.AUX_STATS$
   WHERE  SNAME = 'SYSSTATS_MAIN';

関連項目:

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

13.3.4 システム統計の削除

DBMS_STATS.DELETE_SYSTEM_STATSプロシージャはシステム統計を削除します。

このプロシージャではINTERVALまたはSTARTおよびSTOPの各オプションを使用して収集された作業負荷統計が削除され、デフォルト値が非作業負荷統計にリセットされます。ただし、stattabパラメータで統計を格納する表が指定されている場合は、サブプログラムによって、関連するstatidを持つすべてのシステム統計が統計表から削除されます。

データベースを新しく作成した場合は、システム統計を削除し、データベースを停止した後、データベースを再度オープンすることをお薦めします。この一連のステップによって、システム統計の適切なデフォルトがデータベースで確実に設定されます。

前提条件

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

• 特定の集中型の作業負荷の統計を収集しましたが、オプティマイザでそれらの統計が使用されないようにします。

• ユーザーが指定した表ではなく、デフォルトの場所に作業負荷統計が格納されています。

システム統計を削除するには:

1. SQL*Plusで、管理権限を持つユーザーとしてデータベースにログインします。

2. システム統計を削除します。

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

   EXEC DBMS_STATS.DELETE_SYSTEM_STATS;

関連項目:

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

13.4 レポート作成モードでの統計収集関数の実行

DBMS_STATS統計収集プロシージャをレポート作成モードで実行できます。

REPORT_*プロシージャを使用した場合、オプティマイザは実際には統計を収集しません。かわりに、指定された統計収集関数を使用した場合に処理されるオブジェクトがレポートされます。

次の表に、DBMS_STATS.REPORT_GATHER_*_STATSファンクションを示します。追加パラメータのdetail_levelとformatを使用すると、すべてのファンクションで、入力パラメータが、対応するGATHER_*_STATSプロシージャと同じものになります。サポートされている形式は、XML、HTMLおよびTEXTです。

表13-5 DBMS_STATSレポート作成モード・ファンクション

ファンクション	説明
REPORT_GATHER_TABLE_STATS	レポート作成モードでGATHER_TABLE_STATSを実行します。このプロシージャでは統計は収集されませんが、GATHER_TABLE_STATSを起動することで影響を受ける可能性があるすべてのオブジェクトがレポートされます。
REPORT_GATHER_SCHEMA_STATS	レポート作成モードでGATHER_SCHEMA_STATSを実行します。このプロシージャでは実際に統計は収集されませんが、GATHER_SCHEMA_STATSを起動することで影響を受ける可能性があるすべてのオブジェクトがレポートされます。
REPORT_GATHER_DICTIONARY_STATS	レポート作成モードでGATHER_DICTIONARY_STATSを実行します。このプロシージャでは実際に統計は収集されませんが、GATHER_DICTIONARY_STATSを起動することで影響を受ける可能性があるすべてのオブジェクトがレポートされます。
REPORT_GATHER_DATABASE_STATS	レポート作成モードでGATHER_DATABASE_STATSを実行します。このプロシージャでは実際に統計は収集されませんが、GATHER_DATABASE_STATSを起動することで影響を受ける可能性があるすべてのオブジェクトがレポートされます。
REPORT_GATHER_FIXED_OBJ_STATS	レポート作成モードでGATHER_FIXED_OBJ_STATSを実行します。このプロシージャでは実際に統計は収集されませんが、GATHER_FIXED_OBJ_STATSを起動することで影響を受ける可能性があるすべてのオブジェクトがレポートされます。
REPORT_GATHER_AUTO_STATS	レポート作成モードで自動統計収集ジョブを実行します。このプロシージャでは実際に統計は収集されませんが、ジョブを実行することで影響を受ける可能性があるすべてのオブジェクトがレポートされます。

前提条件

このチュートリアルでは、oeスキーマのGATHER_SCHEMA_STATSを実行することで影響を受ける可能性があるオブジェクトのHTMLレポートを生成するものとします。

GATHER_SCHEMA_STATSの実行によって影響を受けるオブジェクトのレポートを作成するには:

1. SQL*Plusを起動し、管理者権限でデータベースに接続します。

2. DBMS_STATS.REPORT_GATHER_SCHEMA_STATSファンクションを実行します。

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

   SET LINES 200 PAGES 0
   SET LONG 100000
   COLUMN REPORT FORMAT A200
   
   VARIABLE my_report CLOB;
   BEGIN
     :my_report :=DBMS_STATS.REPORT_GATHER_SCHEMA_STATS(
       ownname      => 'OE'       , 
       detail_level => 'TYPICAL'  ,
       format       => 'HTML'     );
   END;
   /
   

次の図は、サンプル・レポートの一部を示しています。

report_gs_oe.pngの例の説明

関連項目:

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