X

A blog about Oracle Technology Network Japan

  • September 24, 2019

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

Guest Author

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

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

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

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

(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)
    このクラスは、オブジェクトに対するルールで、統計の品質、統計の失効、統計の不要な収集などを確認します。統計収集権限を持つすべてのオブジェクトに実行できます。

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

ID NAME (DESCRIPTION) 説明
1 UseAutoJob (Use Auto Job for Statistics Collection) 自動統計収集ジョブを使用してください(特別な場合は手動で管理できますが、自動統計収集ジョブが推奨です)
2 CompleteAutoJob (Auto Statistics Gather Job should complete successfully) 自動統計収集ジョブ実行が失敗しています(アドバイザは推定原因を検出します)
3 MaintainStatsHistory (Maintain Statistics History) 統計履歴はリグレッションの場合に有効なので、正しく格納できるようにしてください(保存期間が大き過ぎず、パージが発生するかSYSAUXが拡張するかの確認)
4 UseConcurrent (Use Concurrent preference for Statistics Collection) 統計収集の実行が速いほど収集できる統計は多くなるので、すべてのリソースを使用できるように、Concurrentプリファレンスを使用してください
5 UseDefaultPreference (Use Default Preference for Stats Collection) グローバル・プリファレンスのデフォルトは、ほとんどの場合に最適だと考えられたものなので、デフォルト設定を使用してください
6 TurnOnSQLPlanDirective (SQL Plan Directives should not be disabled) SQL計画ディレクティブを無効にしないでください(これはデフォルトが無効になる前に導入されたものです)
7 AvoidSetProcedures (Avoid Set Statistics Procedures) ユーザー定義の統計設定(Set Statisticsプロシージャ)を回避策として使用している場合がありますが、一般的な方法ではないので避けてください
8 UseDefaultParams (Use Default Parameters in Statistics Collection Procedures) デフォルト・パラメータは、ほとんどの場合に最適だと考えられたものなので、統計収集はデフォルト値を使用してください
9 UseGatherSchemaStats (Use gather_schema_stats procedure) スキーマに対して実行して(gather_schema_statsプロシージャを使用して)、新しく作成したテーブルを確実に含めるようにしてください
10 AvoidInefficientStatsOprSeq (Avoid inefficient statistics operation sequences) 非効率的な統計収集(オンライン統計収集が行われている表に対する手動での統計収集)を避けてください
11 AvoidUnnecessaryStatsCollection (Avoid unnecessary statistics collection) 不要な統計収集(何も変わっていないときの統計収集)を避けてください
12 AvoidStaleStats (Avoid objects with stale or no statistics) 古くなった統計や統計のないオブジェクトを避けてください(全テーブルの統計が必要です)
13 GatherStatsAfterBulkDML (Do not gather statistics right before bulk DML) バルクDMLの直前に統計を収集しないでください(バルク挿入前に収集した統計はすぐに古くなります)
14 LockVolatileTable (Statistics for objects with volatile data should be locked) 揮発性データを持つオブジェクト(TRUNCATEと挿入の間のテーブル)で自動統計収集を実行したくない場合、そのオブジェクトの統計はロックしてください
15 UnlockNonVolatileTable (Statistics for objects with non-volatile should not be locked) 不揮発性のオブジェクトの統計はロックしないでください(しかし、大きな変更がないテーブルに対してはロックするのは良いことです)
16 MaintainStatsConsistency (Statistics of dependent objects should be consistent) 従属オブジェクトの統計は一貫するようにしてください
17 AvoidDropRecreate (Avoid drop and recreate object seqauences) テーブルを削除して再作成するよりも、TRUNCATEとIndex Unusableのダイレクト・パス・インサートをしてください(統計が削除されません)
18 UseIncremental (Statistics should be maintained incrementally when it is beneficial) 有益な場合は、増分統計収集を行ってください(統計アドバイザは、パーティション表に対して増分統計収集が効率的であることを検出します)
19 NotUseIncremental (Statistics should not be maintained incrementally when it is not beneficial) 有益でない場合は、増分統計収集を行わないでください
20 AvoidOutOfRange (Avoid Out of Range Histogram endpoints) 範囲外(古い統計)は過小見積りにつながる可能性があるので、避けてください
21 UseAutoDegree (Use Auto Degree for statistics collection) 大きなテーブルはパラレル・スキャンすることができるので、統計収集に自動パラレル度を使用してください(デフォルトのパラレル度が推奨)
22 UseDefaultObjectPreference (Use Default Object Preference for statistics collection) 統計収集にデフォルトのオブジェクト・プリファレンスを使用してください(表プリファレンスは、グローバル・プリファレンスより優先順位が高いので、これもデフォルト設定を使用してください)
23 AvoidAnalyzeTable (Avoid using analyze table commands for statistics collection) dbms_statsは、より確実にオプティマイザ統計を収集できるので、analyze tableコマンドを使用しないでください

 

(2)その他のコンポーネント

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

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

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

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

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

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

 

(1)自動アドバイザ・タスク

自動アドバイザ・タスクは、メンテナンス・ウィンドウの自動オプティマイザ統計収集ジョブの一部として、事前定義されたタスク'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

 

(2)アドバイザ・レポートの生成

オプティマイザ統計アドバイザは、通常のアドバイザ・フレームワークを使用するため、アドバイザの実行結果は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.
-------------------------------------------------------------------------------

 

(3)アドバイザ推奨事項の実装

アドバイザの推奨事項は、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ファンクションを使用して、アドバイザの推奨事項をスクリプトに変換して、編集してから実行することもできます(単一の結果に複数の推奨事項が存在する場合はこれを使用してください)。

(4)手動タスクの作成と実行

自動タスク以外に、固有のアドバイザ・タスクを作成して、いつでも実行することができます。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;
/

 

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

(5)アドバイザのフィルター機能

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

  • CONFIGURE_ADVISOR_OBJ_FILTER(オブジェクト・フィルター)
    このプロシージャを使用して、指定されたデータベース・スキーマまたはオブジェクトを含めるか、または除外します。オブジェクト・フィルターは、サポートされているワイルドカード(%)を使用して、所有者名およびオブジェクト名を指定できます。
  • CONFIGURE_ADVISOR_RULE_FILTER(ルール・フィルター)
    このプロシージャは、「(1)アドバイザ・ルール」で説明したルール名(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;
/

 

(6)統計プリファレンスのオーバーライド機能

最後に、統計プリファレンス・パラメータのオーバーライドについて説明します。
オプティマイザ統計アドバイザが、収集時のパラメータ入力値を変更するようにアドバイしても、いろいろな場所で統計収集していると簡単ではありません。そのため、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ページ▶▶ 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.