日本語PDF

16 過去のオプティマイザ統計の管理

この章では、過去の統計の保存、レポート作成およびリストア方法について説明します。

16.1 オプティマイザ統計のリストア

DBMS_STATSを使用して、データ・ディクショナリに格納されている古いバージョンの統計をリストアできます。

16.1.1 オプティマイザ統計のリストア操作について

データ・ディクショナリ内の統計を変更する場合は常に、データベースで古いバージョンの統計が自動的に保存されます。新しく収集した統計によって最適ではない実行計画が作成される場合は、以前の統計に戻すことが必要な場合もあります。

オプティマイザ統計のリストアは、最適ではない計画のトラブルシューティングに役立つ場合があります。次の図は、統計のリストアを時系列で示しています。このグラフでは、統計の収集が8月10日と8月20日に行われます。8月24日に、現行の統計がオプティマイザで最適ではない計画が生成される原因となっている可能性があるとDBAで判定されています。8月25日に、管理者は8月10日に収集された統計をリストアしています。

図16-1 オプティマイザ統計のリストア

「図16-1 オプティマイザ統計のリストア」の説明

16.1.2 オプティマイザ統計のリストアのガイドライン

統計のリストアは、統計のインポートおよびエクスポートに類似しています。

次のような状況では通常、統計のエクスポートではなく統計のリストアを行います。

• 統計の古いバージョンをリカバリする場合。たとえば、オプティマイザ動作を以前の日付までリストアすることが必要な場合があります。

• データベースで統計履歴の保存および消去を管理する場合。

次のような場合は、統計をリストアするのではなく、エクスポートします。

• 複数の統計セットを試験的に使用して値を増減させる場合。

• データベース間で統計を移動する場合。たとえば、本番システムからテスト・システムに統計を移動する場合などです。

• 既知の統計セットを統計のリストアに必要な保存日数よりも長期間にわたって保存する場合。

関連項目:

統計をリストアおよびインポートするためのプロシージャの概要は、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください

16.1.3 オプティマイザ統計のリストアの制限

前のバージョンの統計をリストアするときには、様々な制限が適用されます。

次の制約があります。

• DBMS_STATS.RESTORE_*_STATSプロシージャでは、ユーザー定義統計はリストアできません。

• 統計の収集にANALYZEコマンドが使用された場合、古いバージョンの統計は格納されません。

• 表を削除すると、自動ヒストグラム機能で使用されるワークロード・データとDBMS_STATS.RESTORE_*_STATSで使用される統計履歴が削除されます。このデータなしでは、これらの機能は適切に動作しません。このため、表からすべての行を削除して再移入する場合は、表を削除して再作成するのではなく、TRUNCATEを使用することをお薦めします。

  ノート:

  表がごみ箱にある場合は、表をフラッシュバックすることによっても統計情報が取得されます。

16.1.4 DBMS_STATSを使用したオプティマイザ統計のリストア

DBMS_STATS.RESTORE_*_STATSプロシージャを使用して統計をリストアできます。

次の表にリストされたプロシージャは、引数としてタイムスタンプを受け入れ、指定された日時(as_of_timestamp)の統計をリストアします。

表16-1 DBMS_STATSリストア・プロシージャ

プロシージャ	説明
RESTORE_DICTIONARY_STATS	指定されたタイムスタンプですべてのディクショナリ表(SYS、SYSTEMおよびRDBMSの各コンポーネント・スキーマの表)の統計をリストアします。
RESTORE_FIXED_OBJECTS_STATS	指定されたタイムスタンプですべての固定表の統計をリストアします。
RESTORE_SCHEMA_STATS	指定されたタイムスタンプでスキーマのすべての表の統計情報をリストアします。
RESTORE_SYSTEM_STATS	指定されたタイムスタンプのシステム統計をリストアします。
RESTORE_TABLE_STATS	指定されたタイムスタンプの表の統計をリストアします。また、このプロシージャでは、関連付けられた索引と列の統計がリストアされます。指定されたタイムスタンプで表の統計がロックされていた場合は、プロシージャでその統計がロックされます。

ディクショナリ・ビューには統計の変更日時が表示されています。次のビューを使用して、リストア操作に使用するタイムスタンプを決定できます。

• DBA_OPTSTAT_OPERATIONSビューには、DBMS_STATSを使用してスキーマ・レベルとデータベース・レベルで実行された統計操作の履歴が含まれます。

• DBA_TAB_STATS_HISTORYビューには表の統計の変更履歴が含まれます。

前提条件

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

• oe.orders表に最新の統計を収集した後に、オプティマイザは、その表の問合せに最適ではない計画の選択を開始しました。

• 計画が改善されたかどうかを確認するために、最新の統計収集より前の統計をリストアします。

オプティマイザ統計をリストアするには:

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

2. oe.ordersの統計履歴を問い合せます。

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

   COL TABLE_NAME FORMAT a10
   SELECT TABLE_NAME,
          TO_CHAR(STATS_UPDATE_TIME,'YYYY-MM-DD:HH24:MI:SS') AS STATS_MOD_TIME
   FROM   DBA_TAB_STATS_HISTORY 
   WHERE  TABLE_NAME='ORDERS'
   AND    OWNER='OE'
   ORDER BY STATS_UPDATE_TIME DESC;
   

   次に出力の例を示します。

   TABLE_NAME STATS_MOD_TIME
   ---------- -------------------
   ORDERS     2012-08-20:11:36:38
   ORDERS     2012-08-10:11:06:20
   

3. オプティマイザ統計を以前の変更時にリストアします。

   たとえば、oe.orders表の統計を2012年8月10日にリストアします。

   BEGIN
     DBMS_STATS.RESTORE_TABLE_STATS( 'OE','ORDERS', 
                  TO_TIMESTAMP('2012-08-10:11:06:20','YYYY-MM-DD:HH24:MI:SS') );
   END;
   /
   

   DBMS_STATSによって指定された日時の統計がリストアされるので、8/10から8/20までの間で任意の日付を指定できます。

関連項目:

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

16.2 オプティマイザ統計の保存の管理

デフォルトでは、データベースは、31日間オプティマイザ統計を保存し、その日数より後の統計は消去するようにスケジュールされています。

DBMS_STATSパッケージを使用して、保存期間を決定したり、保存期間を変更したり、古い統計を手動で消去したりすることができます。

16.2.1 オプティマイザ統計の履歴の取得

DBMS_STATSプロシージャを使用して、オプティマイザ統計の履歴情報を取得できます。

履歴情報は、データベースでオプティマイザ統計を保存する期間やその後に統計のリストアが可能な期間を決定する必要がある場合に役立ちます。オプティマイザ統計の履歴に関する情報を取得するには次のプロシージャを使用できます。

• GET_STATS_HISTORY_RETENTION

  このファンクションでは、現行の統計履歴の保存値を取得できます。

• GET_STATS_HISTORY_AVAILABILITY

  このファンクションでは、統計履歴が使用可能な場合に最も古いタイムスタンプを取得します。最も古いタイムスタンプより前のタイムスタンプには、統計をリストアできません。

オプティマイザ統計の履歴情報を取得するには:

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

2. 次のPL/SQLプログラムを実行します。

   DECLARE
     v_stats_retn  NUMBER;
     v_stats_date  DATE;
   BEGIN
     v_stats_retn := DBMS_STATS.GET_STATS_HISTORY_RETENTION;
     DBMS_OUTPUT.PUT_LINE('The retention setting is ' || 
       v_stats_retn || '.');
     v_stats_date := DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY;
     DBMS_OUTPUT.PUT_LINE('Earliest restore date is ' ||
       v_stats_date || '.');
   END;
   /

関連項目:

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

16.2.2 オプティマイザ統計の保存期間の変更

DBMS_STATS.ALTER_STATS_HISTORY_RETENTIONプロシージャを使用して保存期間を構成できます。デフォルトは31日です。

前提条件

このプロシージャを実行するには、SYSDBA権限、またはANALYZE ANY DICTIONARYとANALYZE ANYの両方のシステム権限のいずれかを所有している必要があります。

前提条件

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

• オプティマイザ統計の現行の保存期間は31日です。

• 年次報告書の一環として年に1回問合せを実行します。(現在、最適ではない計画が作成されている場合に)前年の計画にアクセスできるように、統計履歴を366日以上保持しておくには、保存期間を366日に設定します。

• オプティマイザ統計の保存期間の変更に使用できるPL/SQLプロシージャset_opt_stats_retentionを作成します。

オプティマイザ統計の保存期間を変更するには:

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

2. 保存期間を変更するプロシージャを作成します。

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

   CREATE OR REPLACE PROCEDURE set_opt_stats_retention
     ( p_stats_retn   IN NUMBER )
   IS
     v_stats_retn NUMBER;
   BEGIN
     v_stats_retn := DBMS_STATS.GET_STATS_HISTORY_RETENTION;
     DBMS_OUTPUT.PUT_LINE('Old retention setting is ' ||
       v_stats_retn || '.');
     DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(p_stats_retn);
     v_stats_retn := DBMS_STATS.GET_STATS_HISTORY_RETENTION;
     DBMS_OUTPUT.PUT_LINE('New retention setting is ' ||
       v_stats_retn || '.');
   END;
   /
   

3. 保存期間を366日に変更します。

   たとえば、前のステップで作成したプロシージャを実行します(出力例も示します)。

   SQL> EXECUTE set_opt_stats_retention(366)
   
   The old retention setting is 31.
   The new retention setting is 366.
    
   PL/SQL procedure successfully completed.

関連項目:

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

16.2.3 オプティマイザ統計の消去

自動消去は、STATISTICS_LEVEL初期化パラメータをTYPICALまたはALLに設定した場合に有効になります。

(現在の時間 - ALTER_STATS_HISTORY_RETENTION設定)と(最新の統計情報収集の時間 - 1)よりも古い履歴はすべて消去されます。

PURGE_STATSプロシージャを使用すると、古い統計を手動で消去できます。引数を指定しない場合、このプロシージャでは、自動消去ポリシーが使用されます。before_timestampパラメータを指定する場合は、指定されたタイムスタンプより前に保存された統計がデータベースで消去されます。

前提条件

このプロシージャを実行するには、SYSDBA権限、またはANALYZE ANY DICTIONARYとANALYZE ANYの両方のシステム権限のいずれかを所有している必要があります。

前提条件

このチュートリアルでは、1週間より前の統計を消去するものとします。

オプティマイザ統計を消去するには:

1. SQL*Plusで、必要な権限でデータベースにログインします。

2. DBMS_STATS.PURGE_STATSプロシージャを実行します。

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

   EXEC DBMS_STATS.PURGE_STATS( SYSDATE-7 );

関連項目:

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

16.3 過去の統計収集操作のレポート作成

DBMS_STATS関数を使用して、特定の統計収集操作または特定の日時の間に発生した操作のレポートを作成できます。

表16-2にはファンクションがリストされています。

表16-2 DBMS_STATSレポート作成ファンクション

ファンクション	説明
REPORT_STATS_OPERATIONS	2つの時点間で発生したすべての統計操作のレポートを生成します。自動統計収集の実行のみを含めることで、レポートの範囲を絞り込むことができます。また、プラガブル・データベース(PDB)IDのセットを指定することで、指定されたPDBの統計操作のみのレポートがデータベースで作成されます。
REPORT_SINGLE_STATS_OPERATION	指定された操作のレポートを生成します。オプションで、コンテナ・データベース(CDB)内の特定のPDB IDを指定できます。

前提条件

このチュートリアルでは、次のHTMLレポートを生成するものとします。

• 過去1日以内のすべての統計収集操作

• 最新の統計収集操作

過去1日のすべての操作のレポートを作成するには:

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

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

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

   SET LINES 200 PAGES 0
   SET LONG 100000
   COLUMN REPORT FORMAT A200
   
   VARIABLE my_report CLOB;
   BEGIN
     :my_report := DBMS_STATS.REPORT_STATS_OPERATIONS (
        since        => SYSDATE-1
   ,    until        => SYSDATE 
   ,    detail_level => 'TYPICAL' 
   ,    format       => 'HTML'      
   );
   END;
   /
   

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

   
   reporting_past_ops.gifの例の説明

3. 個々の操作についてDBMS_STATS.REPORT_SINGLE_STATS_OPERATIONファンクションを実行します。

   たとえば、次のプログラムを実行して、操作848のレポートを生成します。

   BEGIN
     :my_report :=DBMS_STATS.REPORT_SINGLE_STATS_OPERATION (
        OPID    => 848
   ,    FORMAT  => 'HTML'
   );
   END;
   

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

reporting_past_op.gifの例の説明

関連項目:

• 統計管理用Cloud Control GUIについて学習するには、「オプティマイザ統計管理のグラフィカル・インタフェース」を参照してください

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