「オプティマイザ統計の保留」の検証（その１）

「オプティマイザ統計の保留」とは？

今回から「オプティマイザ統計の保留」というOracle 11g以降で使えるようになった機能を紹介・検証していきます。

以下はマニュアルへのリンクです。
13.5.1 統計の保留
Oracle® Databaseパフォーマンス・チューニング・ガイド
11gリリース2 (11.2)
B56312-06

機能概要

  • 統計情報は収集後即時に公開（PUBLISH）される。（デフォルトの動作）
  • 公開された統計はXXXX_TAB_STATISTICSやXXXX_IND_STATISTICSデータ・ディクショナリ・ビューに格納される。
  • オプティマイザは原則的にデータ・ディクショナリ・ビューに格納されている公開済の統計を使用する。
  • 従って即時に公開された新しい統計情報は実行計画に影響を与える可能性がある。
  • 新しい統計を「保留中」として保存することができる。（統計の保留）
  • PUBLISH設定は、スキーマまたはテーブル・レベルで変更することができる。
  • 保留中の統計は、XXXX_TAB_PENDING_STATSやXXXX_IND_PENDING_STATSビューに格納される。
  • 保留された統計情報は実行計画に反映されない。
  • OPTIMIZER_USE_PENDING_STATISTICS初期化パラメータをTRUEに設定（セッション単位で変更可）すると、保留中の統計情報を実行計画に反映することができる。
  • 現在の公開済み統計情報と新しく保留された統計情報を比較することができる。
  • 保留中の統計情報を手動で公開することができる。

関連コマンド等

検証を行う前に、関連するコマンド等を整理しておきます。

1. PUBLISH設定を変更するには

PUBLISH属性は、スキーマあるいはテーブルに関する属性になりますが、「保留中」に変更するには以下の要領でDBMS_STATS.SET_TABLE_PREFSパッケージ（プロシージャ）を使用して行います。（ALTER TABLEは使用しません。）

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS('<スキーマ名>', '<テーブル名>;', 'PUBLISH', 'false');

例：

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS('SH', 'CUSTOMERS', 'PUBLISH', 'false');

2. 現在のPUBLISH設定を確認するには

現在設定されているPUBLISH属性の状態を確認するためには、データ・ディクショナリ・ビューへの問い合わせではなく、DBMS_STATS.GET_PREFSパッケージ（ファンクション）を使用して行います。

SQL> select DBMS_STATS.GET_PREFS('PUBLISH', '<スキーマ名>', '<テーブル名>') prefs from dual;

例：

SQL> select DBMS_STATS.GET_PREFS('PUBLISH', 'SH', 'CUSTOMERS') prefs from dual;

3. 保留中の統計を一時的に使用して実行計画を作成するには

SQL> alter session set OPTIMIZER_USE_PENDING_STATISTICS = TRUE;

このコマンド実行後のセッションではオプティマイザは保留中の統計を使用して実行計画を作成します。

4. 現在の公開済み統計と新しく保留された統計を比較するには

この場合は、DBMS_STATS.DIFF_TABLE_STATS_IN_PENDINGパッケージ（ファンクション）を使用して行います。

SQL> set long 10000
SQL> set pages 9999
SQL> set head off
SQL> select report from table (DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING('<スキーマ名>', '<テーブル名>'));

例：

SQL> select report from table (DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING('SH', 'CUSTOMERS'));

5. 保留中の統計を永続的に公開するには

DBMS_STATS.PUBLISH_PENDING_STATSパッケージ（プロシージャ）を使用して行います。公開された統計はもはや保留中ではありません。オプティマイザは次に実行計画を作成する際にこの統計を使用します。

SQL> exec DBMS_STATS.PUBLISH_PENDING_STATS('<スキーマ名>', '<テーブル名>');

例：

SQL> exec DBMS_STATS.PUBLISH_PENDING_STATS('SH', 'CUSTOMERS');

検証シナリオ

それでは、この機能を検証するためのシナリオを以下に紹介します。

  1. テーブル作成
    • TEST表（ID, VALUE）
  2. 少量データ作成（1,000件）
  3. インデックス作成
    • ID列に対して
  4. クエリー実行①
    • インデックス・レンジ検索（1件取得）
  5. テーブルTRUNCATE
  6. 大量データ作成（50,000件）
  7. クエリー実行②
    • 4.と同じクエリー（ただし、検索レンジが異なる）
  8. 大量データ削除（50,000→1,000件）
  9. クエリー実行③
    • 4, 7と同じクエリー

レコード件数が変化したタイミングで同じクエリーを実行し実行計画を確認しますが、統計情報の取得によって実行計画がどのように変化する（あるいは変化しないか）を検証していきます。

次回へ続く