16 過去のオプティマイザ統計の管理
この章では、過去の統計の保存、レポート作成およびリストア方法について説明します。
16.1 オプティマイザ統計のリストア
DBMS_STATS
を使用して、データ・ディクショナリに格納されている古いバージョンの統計をリストアできます。
16.1.1 オプティマイザ統計のリストア操作について
データ・ディクショナリ内の統計を変更する場合は常に、データベースで古いバージョンの統計が自動的に保存されます。新しく収集した統計によって最適ではない実行計画が作成される場合は、以前の統計に戻すことが必要な場合もあります。
オプティマイザ統計のリストアは、最適ではない計画のトラブルシューティングに役立つ場合があります。次の図は、統計のリストアを時系列で示しています。このグラフでは、統計の収集が8月10日と8月20日に行われます。8月24日に、現行の統計がオプティマイザで最適ではない計画が生成される原因となっている可能性があるとDBAで判定されています。8月25日に、管理者は8月10日に収集された統計をリストアしています。
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リストア・プロシージャ
プロシージャ | 説明 |
---|---|
|
指定されたタイムスタンプですべてのディクショナリ表( |
|
指定されたタイムスタンプですべての固定表の統計をリストアします。 |
|
指定されたタイムスタンプでスキーマのすべての表の統計情報をリストアします。 |
|
指定されたタイムスタンプのシステム統計をリストアします。 |
|
指定されたタイムスタンプの表の統計をリストアします。また、このプロシージャでは、関連付けられた索引と列の統計がリストアされます。指定されたタイムスタンプで表の統計がロックされていた場合は、プロシージャでその統計がロックされます。 |
ディクショナリ・ビューには統計の変更日時が表示されています。次のビューを使用して、リストア操作に使用するタイムスタンプを決定できます。
-
DBA_OPTSTAT_OPERATIONS
ビューには、DBMS_STATS
を使用してスキーマ・レベルとデータベース・レベルで実行された統計操作の履歴が含まれます。 -
DBA_TAB_STATS_HISTORY
ビューには表の統計の変更履歴が含まれます。
前提条件
このチュートリアルでは、次のことが前提となっています。
-
oe.orders
表に最新の統計を収集した後に、オプティマイザは、その表の問合せに最適ではない計画の選択を開始しました。 -
計画が改善されたかどうかを確認するために、最新の統計収集より前の統計をリストアします。
オプティマイザ統計をリストアするには:
-
SQL*Plusを起動し、管理者権限でデータベースに接続します。
-
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
-
オプティマイザ統計を以前の変更時にリストアします。
たとえば、
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
このファンクションでは、統計履歴が使用可能な場合に最も古いタイムスタンプを取得します。最も古いタイムスタンプより前のタイムスタンプには、統計をリストアできません。
オプティマイザ統計の履歴情報を取得するには:
-
SQL*Plusを起動し、必要な権限でデータベースに接続します。
-
次の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
を作成します。
オプティマイザ統計の保存期間を変更するには:
-
SQL*Plusを起動し、必要な権限でデータベースに接続します。
-
保存期間を変更するプロシージャを作成します。
たとえば、次のプロシージャを作成します。
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; /
-
保存期間を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週間より前の統計を消去するものとします。
オプティマイザ統計を消去するには:
-
SQL*Plusで、必要な権限でデータベースにログインします。
-
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レポート作成ファンクション
ファンクション | 説明 |
---|---|
|
2つの時点間で発生したすべての統計操作のレポートを生成します。自動統計収集の実行のみを含めることで、レポートの範囲を絞り込むことができます。また、プラガブル・データベース(PDB)IDのセットを指定することで、指定されたPDBの統計操作のみのレポートがデータベースで作成されます。 |
|
指定された操作のレポートを生成します。オプションで、コンテナ・データベース(CDB)内の特定のPDB IDを指定できます。 |
前提条件
このチュートリアルでは、次のHTMLレポートを生成するものとします。
-
過去1日以内のすべての統計収集操作
-
最新の統計収集操作
過去1日のすべての操作のレポートを作成するには:
-
SQL*Plusを起動し、管理者権限でデータベースに接続します。
-
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; /
次の図は、サンプル・レポートを示しています。
-
個々の操作について
DBMS_STATS.REPORT_SINGLE_STATS_OPERATION
ファンクションを実行します。たとえば、次のプログラムを実行して、操作
848
のレポートを生成します。BEGIN :my_report :=DBMS_STATS.REPORT_SINGLE_STATS_OPERATION ( OPID => 848 , FORMAT => 'HTML' ); END;
次の図は、サンプル・レポートを示しています。
関連項目:
-
統計管理用Cloud Control GUIについて学習するには、「オプティマイザ統計管理のグラフィカル・インタフェース」を参照してください
-
DBMS_STATS
についてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください