津島博士のパフォーマンス講座　第73回　オプティマイザ統計アドバイザについて

津島博士のパフォーマンス講座 indexページ▶▶ 

皆さんこんにちは、今年も残暑が厳しかったのですが、やっと過ごしやすい気候になってきましたね。
今回は、オプティマイザ統計を正しく収集できていないため、性能問題になっているシステムがまだ多いようですので、「Oracle Databaseにおけるオプティマイザ統計収集のベスト・プラクティス」を実施できるように、Oracle Database 12cR2（Oracle12cR2）から提供されたオプティマイザ統計アドバイザについて説明しますので、参考にしてください。

1. オプティマイザ統計アドバイザのコンポーネント

オプティマイザ統計は、より効果的（短時間で正確）な収集ができるよう継続的に強化を行っています。ただし、その強化を見落としている方、オプティマイザ統計の収集方法を誤解している方がまだ多いようです。そのため、現状の収集方法を診断してくれるオプティマイザ統計アドバイザのコンポーネントから説明します。

（１）アドバイザ・ルール

まずは、オプティマイザ統計アドバイザで最も重要なアドバイザ・ルールについて説明します。 アドバイザ・ルールは、統計アドバイザがベスト・プラクティスをチェックするために、Oracleから提供される標準になります。そのため、ベスト・プラクティスが変更された場合、アドバイザ・ルールも変更されます（現在は、23個のルールが提供されていて、V$STATS_ADVISOR_RULESビューで確認できます）。このルールは、以下のクラスに分類されていて、ユーザーの権限によって確認するルールやオブジェクトが異なります。

• SYSTEM（1～6）
  このクラスは、統計収集のプリファレンス、自動統計収集ジョブのステータス、SQL計画ディレクティブの使用などを確認します。ANALYZE ANYとANALYZE ANY DICTIONARYの権限を持つユーザーが実行できます。
• OPERATION（7～10）
  このクラスは、統計収集操作に対するルールで、デフォルトを使用しているか、SET_%_STATSプロシージャ（ユーザー定義の統計設定）を使用して作成されているかなどを確認します。これもANALYZE ANYとANALYZE ANY DICTIONARYの権限を持つユーザーが、すべてのスキーマの操作に実行できます（ANALYZE ANY DICTIONARY権限のないユーザーはSYS以外のすべてのスキーマ、ANALYZE ANY権限のないユーザーはSYSと自身のスキーマ、両方の権限のないユーザーは自身のスキーマのみになります）。
• OBJECT（11～23）
  このクラスは、オブジェクトに対するルールで、統計の品質、統計の失効、統計の不要な収集などを確認します。統計収集権限を持つすべてのオブジェクトに実行できます。

それぞれのルールが分かりやすいように、以下に簡単な説明を記述しておきます。

（２）その他のコンポーネント

次に、その他のコンポーネントとして結果、推奨事項、アクションについて説明します。

• アドバイザ結果
  オプティマイザ統計アドバイザがデータベースに格納されている統計内容（データ・ディクショナリ上の統計履歴、統計操作ログ、SYSAUXに存在する現在の統計フットプリント）を分析し、ルールに従っていないと判断すると、アドバイザ結果が生成されます。オブジェクト失効などの一部の結果では情報のみを提供します。
• アドバイザ推奨事項
  各アドバイザ結果に基づいて、優れた統計の実現方法に関する推奨事項を生成します。例えば、統計収集時のサンプリング・ルール違反を検出すると、AUTO_SAMPLE_SIZEの指定を推奨します。また、単一の結果に複数の推奨事項が存在する場合、従う推奨事項を調査して決定する必要があります。各推奨事項には、理論的根拠（生成理由の説明）も含まれますが、結果によって生成されない可能性があります
• アドバイザ・アクション
  アドバイザ・アクションは、推奨事項を実装するSQLまたはPL/SQLスクリプトで、実行が可能な場合に含まれます。

このように、オプティマイザ統計収集の様々なことを分析してくれるので、まずは診断レベルからでも使用してみてください。

2. オプティマイザ統計アドバイザの基本タスク

ここでは、オプティマイザ統計アドバイザを実行するための基本タスクについて説明します。
オプティマイザ統計アドバイザは、自動および手動タスク・モードをサポートしており、それぞれのモードの以下の基本タスクと関連する機能について説明していきます。

• 自動アドバイザ・タスク
• アドバイザ・レポートの生成
• アドバイザ推奨事項の実装
• 手動タスクの作成と実行
• アドバイザのフィルター機能
• 統計プリファレンスのオーバーライド機能

（１）自動アドバイザ・タスク

自動アドバイザ・タスクは、メンテナンス・ウィンドウの自動オプティマイザ統計収集ジョブの一部として、事前定義されたタスク’AUTO_STATS_ADVISOR_TASK’が自動的に実行されます。自動タスクは、結果および推奨事項を生成しますが、自動的にアクションまでは実装しません。そのため、アドバイザ・レポートを出力して、推奨事項が存在している場合、アクションを手動で実装する必要があります。以下のSQLで、自動タスクの実行状態を表示して、出力するレポートのEXECUTION_NAME（タスクの実行名）を確認できます。

SQL> SELECT task_name,execution_name,execution_start,execution_end,execution_type,status
2    FROM dba_advisor_executions 
3   WHERE task_name = 'AUTO_STATS_ADVISOR_TASK' AND execution_end >= SYSDATE-2 ORDER BY 3;

TASK_NAME                EXECUTION_NAME EXECUTION_START	   EXECUTION_END	    EXECUTION_TYPE STATUS
------------------------ -------------- ------------------- ------------------- -------------- ---------
AUTO_STATS_ADVISOR_TASK  EXEC_42        2017/08/23 23:00:15 2017/08/23 23:00:20 STATISTICS     COMPLETED
AUTO_STATS_ADVISOR_TASK  EXEC_52        2017/08/24 23:00:27 2017/08/24 23:00:40 STATISTICS     COMPLETED

（２）アドバイザ・レポートの生成

オプティマイザ統計アドバイザは、通常のアドバイザ・フレームワークを使用するため、アドバイザの実行結果はDBA_ADVISOR_%ビューなどで表示できますが、統計アドバイザ・レポート（DBMS_STATSパッケージのREPORT_ADVISOR_TASKファンクション）が提供されているので、これを使用して生成します。オプションでEXECUTION_NAME（タスクの実行名）、TYPE（レポート・タイプ）、SECTION（レポート内のセクション）、LEVEL（レポート形式）パラメータを受け入れますが、通常はデフォルト値で問題ないと思います。以下のSQLは、自動アドバイザ・タスクに関するデフォルト・レポート（最新の実行名、TEXTタイプ、全てのセクション、TYPECAL形式）を表示します。

SQL> SET LINESIZE 200
SQL> SET LONG 1000000
SQL> SET PAGESIZE 0
SQL> SET LONGCHUNKSIZE 100000
SQL> SELECT dbms_stats.REPORT_ADVISOR_TASK('AUTO_STATS_ADVISOR_TASK') AS REPORT FROM dual;

デフォルト・セクション（ALL）では、アドバイザ・レポートに次のセクションが含まれています。

• 一般情報：GENERAL INFORMATION（タスク名、実行名、作成日および変更日）
• サマリー：SUMMARY（アドバイザ結果と違反するルールのまとめ）
• 結果：FINDINGS（各関連するルールと結果、その推奨事項）

以下のアドバイザ・レポートは、3つの結果が示されている例になります（ここでは1つのみ表示しています）。表示されているのは、UseDefaultParamsルールに対する推奨事項「統計収集はデフォルト・パラメータを使用してください」です。

GENERAL INFORMATION
-------------------------------------------------------------------------------
 Task Name       : AUTO_STATS_ADVISOR_TASK
 Execution Name  : EXEC_136	
 Created         : 09-05-16 02:52:34 
 Last Modified   : 09-05-16 12:31:24 
-------------------------------------------------------------------------------
SUMMARY
-------------------------------------------------------------------------------
 For execution EXEC_136 of task AUTO_STATS_ADVISOR_TASK, the Statistics Advisor
 has 3 findings. The findings are related to the following rules: 
 USEDEFAULTPARAMS, USEGATHERSCHEMASTATS, NOTUSEINCREMENTAL. Please refer to the
 finding section for detailed information.
-------------------------------------------------------------------------------
FINDINGS
-------------------------------------------------------------------------------
Rule Name:         UseDefaultParams					
 Rule Description:  Use Default Parameters in Statistics Collection Procedures 
 Finding:  There are 367 statistics operations using nondefault parameters.	
 Recommendation:  Use default parameters for statistics operations.	
 Example:
 -- Gathering statistics for 'SH' schema using all default parameter values:
 BEGIN dbms_stats.gather_schema_stats('SH'); END;			
 Rationale:  Using default parameter values for statistics gathering operations
 is more efficient.
-------------------------------------------------------------------------------

（３）アドバイザ推奨事項の実装

アドバイザの推奨事項は、DBMS_STATSパッケージのIMPLEMENT_ADVISOR_TASKファンクションを使用して、すべてを自動的に実装することができます。以下のスクリプトは、自動タスクの最新の実行に関する推奨事項のすべてを実装して、その結果を表示します。

VARIABLE v_result CLOB
DECLARE
  l_task_name  VARCHAR2(32767) := 'AUTO_STATS_ADVISOR_TASK'; -- 自動タスク名;
BEGIN
  :v_result := dbms_stats.IMPLEMENT_ADVISOR_TASK(task_name => l_task_name);
END;
/
SET LONG 10000
SELECT XMLTYPE(:v_result) AS results FROM dual;

また、DBMS_STATSパッケージのSCRIPT_ADVISOR_TASKファンクションを使用して、アドバイザの推奨事項をスクリプトに変換して、編集してから実行することもできます（単一の結果に複数の推奨事項が存在する場合はこれを使用してください）。

（４）手動タスクの作成と実行

自動タスク以外に、固有のアドバイザ・タスクを作成して、いつでも実行することができます。DBMS_STATSパッケージのCREATE_ADVISOR_TASKファンクションとEXECUTE_ADVISOR_TASKファンクションを使用して、以下のようにアドバイザ・タスクの作成と実行を行います。オプションでTASK_NAMEとEXECUTION_NAMEパラメータを受け入れますが、指定されていない場合システム生成の名前が作成されます。

DECLARE
L_task_name  VARCHAR2(32767) := 'demo'; -- タスク名
BEGIN
L_ask_tname := dbms_stats.CREATE_ADVISOR_TASK((task_name => l_task_name);
END;
/
DECLARE
L_task_name  VARCHAR2(32767) := 'demo'; -- タスク名
L_exec_name  VARCHAR2(32767) := NULL;   -- 実行名
BEGIN
L_exec_name := dbms_stats.EXECUTE_ADVISOR_TASK((task_name => l_task_name);
END;
/

手動タスクも自動タスクと同じように、このタスクに関連するレポートを生成してから、推奨事項が存在する場合にアクションを実装します。

（５）アドバイザのフィルター機能

次に、アドバイザ・タスクに対するフィルター指定ついて説明します。
アドバイザ・タスクは、いくつかのフィルターによって対象を絞り込むことが可能です。アドバイザの範囲は、以下の3種類のフィルター・ファンクションによって指定します（STATS_ADV_OPR_TYPEパラメータで、フィルターを適用するアドバイザ操作タイプも指定でき、NULLですべての操作タイプに適用されます）。

• CONFIGURE_ADVISOR_OBJ_FILTER（オブジェクト・フィルター）
  このプロシージャを使用して、指定されたデータベース・スキーマまたはオブジェクトを含めるか、または除外します。オブジェクト・フィルターは、サポートされているワイルドカード(%)を使用して、所有者名およびオブジェクト名を指定できます。
• CONFIGURE_ADVISOR_RULE_FILTER（ルール・フィルター）
  このプロシージャは、「（１）アドバイザ・ルール」で説明したルール名（NAME）を含めるか、または除外するかを指定します。
• CONFIGURE_ADVISOR_OPR_FILTER（オペレーション・フィルター）
  このプロシージャは、指定されたDBMS_STATS操作（DBMS_STATSのプロシージャ）を含めるか、または除外するかを指定します。操作のIDと名前は、DBA_OPTSTAT_OPERATIONSビューから取得できます。

以下は、特定のオブジェクト（SHスキーマのCOUNTRIES表）を除外する例になります。

-- Turn off validation/reporting… for table SH.COUNTRIES
DECLARE
   tname  VARCHAR2(32767) := 'demo';   -- タスク名
   filter_report clob;                 -- report of operation
BEGIN
  filter_report := dbms_stats.CONFIGURE_ADVISOR_OBJ_FILTER(tname, NULL, NULL,'SH','COUNTRIES', 'DISABLE');
END;
/

（６）統計プリファレンスのオーバーライド機能

最後に、統計プリファレンス・パラメータのオーバーライドについて説明します。
オプティマイザ統計アドバイザが、収集時のパラメータ入力値を変更するようにアドバイしても、いろいろな場所で統計収集していると簡単ではありません。そのため、Oracle12cR2からの統計プリファレンス・パラメータPREFERENCE_OVERRIDES_PARAMETERで、統計収集時にパラメータ入力値のオーバーライドを指定できるようになりました。これにより、データベースが統計収集プロシージャで指定したパラメータ値に従うかどうか制御できるので、アドバイザの推奨値で強制的に収集することが可能になります。つまり、パラメータが設定される優先順位が以下のようになります。

• PREFERENCE_OVERRIDES_PARAMETERがFALSE（デフォルト）に設定されている場合の優先順位
  1. 統計収集（GATHER_%_STATS）プロシージャのパラメータ
  2. 表プリファレンス(特定の表、スキーマ内のすべての表、またはデータベース内のすべての表に設定)
  3. グローバル・レベルのプリファレンス（dbms_stats.SET_GLOBAL_PREFSプロシージャ）
• PREFERENCE_OVERRIDES_PARAMETERがTRUEに設定されている場合の優先順位
  1. 表プリファレンス(特定の表、スキーマ内のすべての表、またはデータベース内のすべての表に設定)
  2. グローバル・レベルのプリファレンス（dbms_stats.SET_GLOBAL_PREFSプロシージャ）
  3. 統計収集（GATHER_%_STATS）プロシージャのパラメータ

3. おわりに

今回は、オプティマイザ統計アドバイザについて説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします。
それでは、次回まで、ごきげんよう。

津島博士のパフォーマンス講座 indexページ▶▶