オプティマイザ統計アドバイザを使用した統計の分析

18 オプティマイザ統計アドバイザを使用した統計の分析

オプティマイザ統計アドバイザは、オプティマイザ統計の収集方法を分析し、推奨事項を生成します。

この章のトピックは、次のとおりです:

18.1 オプティマイザ統計アドバイザについて

オプティマイザ統計アドバイザは、統計および統計関連タスクの品質を分析する組込みの診断ソフトウェアです。

アドバイザ・タスクがメンテナンス・ウィンドウで自動的に実行されますが、必要に応じて実行することもできます。次に、アドバイザ・レポートを表示できます。アドバイザが推奨事項を生成すると、システム生成スクリプトを実行して実装できる場合があります。

次の図は、オプティマイザ統計アドバイザの概要を示しています。

図18-1 オプティマイザ統計アドバイザ

「図18-1 オプティマイザ統計アドバイザ」の説明

この項では、次の項目について説明します。

18.1.1 オプティマイザ統計アドバイザの目的

オプティマイザ統計アドバイザは、オプティマイザ統計の収集方法を調査します。

アドバイザは、統計を収集するために既存のプラクティスの問題を自動診断します。アドバイザは、オプティマイザ統計の新規または代替セットを収集しません。アドバイザの出力は、結果および推奨事項のレポートで、統計収集のベスト・プラクティスに従うために役立ちます。

オプティマイザ統計は、問合せの実行計画の決定に重要な役割を果たします。そのため、オプティマイザで正確な最新の統計を収集および維持することが重要です。オプティマイザにより、この目的のためにリリースのたびに展開されるDBMS_STATSパッケージが提供されます。通常、ユーザーは、特定のワークロードに基づいて統計を収集する固有の戦略を開発し、独自開発クリプトを使用してこれらの戦略を実装します。

この項では、次の項目について説明します。

18.1.1.1 従来のスクリプト・ベース・アプローチの問題

スクリプト・アプローチの利点は、スクリプトが通常テストおよび確認されることです。ただし、最適ではないレガシー・スクリプトの所有者は計画変更の原因になる恐れがあるためスクリプトを変更しない場合があります。

従来のアプローチには、次の問題があります。

レガシー・スクリプトは、リリースのたびに変更できる新しいベスト・プラクティスに従わない場合があります。

頻繁に、連続するリリースでは、ヒストグラム、サンプリング、ワークロード監視、同時実行性および他のオプティマイザ関連機能の拡張機能が追加されます。たとえば、Oracle Database 12c以降では、割合のかわりにAUTO_SAMPLE_SIZEを設定することをお薦めしています。ただし、レガシー・スクリプトでは通常サンプリング率が指定され、最適ではない実行計画が生成される場合があります。
リソースが不要な統計収集で消費されます。

スクリプトでは、同じ表で毎日複数の統計が収集される場合があります。
自動統計収集ジョブは、正確な最新の統計を保証しません。

たとえば、初期化パラメータの組合せによって無効化されるか、ジョブが終了するため、自動統計収集ジョブが実行されていない場合があります。さらに、リソース制約が原因であるか、非常に多数のオブジェクトが統計収集を必要とするため、自動ジョブ・メンテナンス・ウィンドウが不十分である場合があります。すべての統計を収集する前にジョブの実行を停止すると、統計が存在しなくなるか、一部のオブジェクトの統計が失効し、最適ではない計画が発生する可能性があります。
場合によっては、統計が欠落、失効または正しくない可能性があります。

たとえば、統計が表およびその索引間または主キーと外部キーの関係を使用した表間で一貫していない場合があります。また、統計収集ジョブが誤って無効化されていたり、スクリプトに失敗した時期を認識しない場合があります。
問題の知識不足は、時間がかかり、リソースが集中する可能性があります。

たとえば、サービス・リクエストが、問題が最適ではない統計によって発生していることを認識せずに解決策を追及する場合があります。診断には、問題の問合せのスクリプトのEメール送信や、トレースの有効化およびトレースの調査に非常に長い時間がかかる場合があります。
推奨される解決策を実行できない場合があります。

パフォーマンス・エンジニアが、統計を維持するアプリケーション・コードの変更を薦める場合があります。組織によって、この要件は困難であるか、満たすことができない場合があります。

18.1.1.2 オプティマイザ統計アドバイザの利点

アドバイザ・ベース・アプローチは、従来のアプローチよりも優れたスケーラビリティおよび保守性を提供します。

ベスト・プラクティスが新しいリリースで変更される場合、オプティマイザ統計アドバイザはルールのこれらのプラクティスをエンコードします。このため、アドバイザにより、常に最新の推奨事項が提供されます。

アドバイザは、現在統計を収集している方法(手動のスクリプトの使用、明示的なパラメータの設定など)、既存の統計収集ジョブの効果および収集された統計の品質を分析します。オプティマイザ統計アドバイザは新規または代替セットのオプティマイザ統計を収集しないため、ワークロードに影響を与えません。かわりに、オプティマイザ統計アドバイザは、データ・ディクショナリに格納されている情報を分析し、データベースに結果および推奨事項を格納します。

オプティマイザ統計アドバイザは、従来のアプローチよりも次の利点があります。

理解しやすいレポートを提供します

アドバイザは、結果、推奨事項およびアクションを生成するルールを適用します。
アプリケーション・コードを変更せずに必要な修正を実装するスクリプトを提供します

推奨されるアクションを実装すると、向上した文を実行するたびに利点が得られます。たとえば、サンプル・サイズが最適ではない割合ではなくAUTO_SAMPLE_SIZEであるようにグローバル・プリファレンスを設定する場合、向上した統計に基づく各計画でこの変更から利点を得ることができます。
メンテナンス・ウィンドウで毎日1回、AUTO_STATS_ADVISOR_TASKという事前定義されたタスクを実行します

自動ジョブを実行するには、STATISTICS_LEVEL初期化パラメータをTYPICALまたはALLに設定する必要があります。
手動によるタスクの作成および実行、データ・ディクショナリ・ビューでの結果および推奨事項の保存、タスク用のレポートの生成、および必要時の修正の実装を可能にするDBMS_STATSパッケージのAPIを提供します
既存のツールに統合します

アドバイザはオプティマイザ統計アドバイザ結果を要約するSQLチューニング・アドバイザとAWRを統合します

18.1.2 オプティマイザ統計アドバイザの概念

オプティマイザ統計アドバイザは、自動データベース診断モニター(ADDM)、SQLパフォーマンス・アナライザおよび他のアドバイザと同じアドバイザ・フレームワークを使用します。

この項では、次の項目について説明します。

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

オプティマイザ統計のオプティマイザ・フレームワークは、データ・ディクショナリ・ビューおよび動的パフォーマンス・ビューにメタデータを格納します。

次のベン図に、オプティマイザ統計アドバイザのルール、結果、推奨事項およびアクション間の関係を示します。たとえば、すべての結果はルールから導出されますが、すべてのルールが結果を生成するわけではありません。

図18-2 オプティマイザ統計アドバイザ・コンポーネント

「図18-2 オプティマイザ統計アドバイザ・コンポーネント」の説明

この項では、次の項目について説明します。

18.1.2.1.1 オプティマイザ統計アドバイザのルール

オプティマイザ統計アドバイザ・ルールは、オプティマイザ統計アドバイザがチェックを行う際に従うOracleから提供される標準です。

ルールにより、現在の機能セットに基づいてOracleのベスト・プラクティスを実現します。ベスト・プラクティスがリリースのたびに変更される場合、オプティマイザ統計アドバイザ・ルールも変更されます。

アドバイザは、次のクラスにルールを編成します。

システム

このクラスは、統計収集のプリファレンス、自動統計収集ジョブのステータス、SQL計画ディレクティブの使用などを確認します。このクラスのルールでは、V$STATS_ADVISOR_RULES.RULE_TYPEにSYSTEM値があります。
操作

このクラスは、統計収集がデフォルトを使用しているか、テスト統計がSET_*_STATSプロシージャなどを使用して作成されているかを確認します。このクラスのルールでは、V$STATS_ADVISOR_RULES.RULE_TYPEにOPERATION値があります。
オブジェクト

このクラスは、統計の品質、統計の失効、統計の不要な収集などを確認します。このクラスのルールでは、V$STATS_ADVISOR_RULES.RULE_TYPEにOBJECT値があります。

ルールにより、次の問題が確認されます。

統計の収集方法

たとえば、初期化パラメータの推奨設定を指定するルールがある場合があります。また、スキーマ・レベルでの統計の収集を指定するルールがある場合があります。
統計を収集する時期

たとえば、アドバイザでは、自動統計収集ジョブのメンテナンス・ウィンドウを有効にするか、ウィンドウを拡張することを推奨する場合があります。
統計収集の効率の向上方法

たとえば、デフォルト・パラメータをDBMS_STATSで使用するか、統計を手動で設定しないことを指定するルールがある場合があります。

V$STATS_ADVISOR_RULESで、各ルールには、DBMS_STATSプロシージャおよびレポートで使用できる一意の文字列IDがあります。ルール・フィルタを使用して、オプティマイザ統計アドバイザが確認するルールを指定できます。ただし、新しいルールを書き込むことはできません。

例18-1 V$STATS_ADVISOR_RULESのルールのリスト

サンプル出力を使用した次の問合せでは、V$STATS_ADVISOR_RULESのルールのサブセットをリストしています。ルールは、リリースのたびに変更される可能性があります。

SET LINESIZE 208
SET PAGESIZE 100
COL ID FORMAT 99
COL NAME FORMAT a33
COL DESCRIPTION FORMAT a62

SELECT RULE_ID AS ID, NAME, RULE_TYPE, DESCRIPTION 
FROM   V$STATS_ADVISOR_RULES 
WHERE  RULE_ID BETWEEN 1 AND 12
ORDER BY RULE_ID;

ID NAME                            RULE_TYPE DESCRIPTION
-- ------------------------------- --------- ------------------------------------------
 1 UseAutoJob                      SYSTEM    Use Auto Job for Statistics Collection
 2 CompleteAutoJob                 SYSTEM    Auto Statistics Gather Job should complete 
                                             successfully
 3 MaintainStatsHistory            SYSTEM    Maintain Statistics History
 4 UseConcurrent                   SYSTEM    Use Concurrent preference for Statistics
                                             Collection
 5 UseDefaultPreference            SYSTEM    Use Default Preference for Stats Collection
 6 TurnOnSQLPlanDirective          SYSTEM    SQL Plan Directives should not be disabled
 7 AvoidSetProcedures              OPERATION Avoid Set Statistics Procedures
 8 UseDefaultParams                OPERATION Use Default Parameters in Statistics 
                                             Collection Proc.
 9 UseGatherSchemaStats            OPERATION Use gather_schema_stats procedure
10 AvoidInefficientStatsOprSeq     OPERATION Avoid inefficient statistics operation
                                             sequences
11 AvoidUnnecessaryStatsCollection OBJECT    Avoid unnecessary statistics collection
12 AvoidStaleStats                 OBJECT    Avoid objects with stale or no statistics

12 rows selected.

関連項目:

V$STATS_ADVISOR_RULESについてさらに学習するには、『Oracle Databaseリファレンス』を参照してください

18.1.2.1.2 オプティマイザ統計アドバイザの結果

オプティマイザ統計アドバイザがデータベースに格納されている証拠を調査し、ルールに従っていないと結論付けると、結果が生成されます。

結果を生成するため、オプティマイザ統計アドバイザは、自動または手動で起動するタスクを実行します。このタスクは、データ・ディクショナリに格納されている統計履歴、統計操作ログおよびSYSAUXに存在する現在の統計フットプリントを分析します。たとえば、アドバイザは、DBA_TAB_STATISTICSおよびDBA_IND_STATISTICSを問い合せて、統計が失効しているかどうかまたは矛盾が行数に存在するかどうかを判断します。

通常、特定のルールに従っていないか違反している場合、オプティマイザ統計アドバイザは結果を生成します。ただし、オブジェクト失効などの一部の結果では情報のみを提供します。たとえば、結果により、DBMS_STATS.GATHER_TABLE_STATSでESTIMATE_PERCENT=>0.01を使用し、ESTIMATE_PERCENT=>AUTO_SAMPLE_SIZEルールに違反していることが示される場合があります。

結果は、1つのルールのみに対応します。ただし、ルールにより、多数の結果を生成できます。

関連項目:

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

18.1.2.1.3 オプティマイザ統計アドバイザの推奨事項

各結果に基づいて、オプティマイザ統計アドバイザは、優れた統計の実現方法に関する推奨事項を生成します。

たとえば、アドバイザは、統計の収集時にサンプリングを使用しないルールに対して違反を検出し、かわりにAUTO_SAMPLE_SIZEの指定を推奨する可能性があります。アドバイザは、DBA_ADVISOR_RECOMMENDATIONSに推奨事項を格納します。

単一の結果に複数の推奨事項が存在する場合があります。この場合、従う推奨事項を調査して決定する必要があります。各推奨事項には、オプティマイザ統計アドバイザが推奨事項を生成する理由を説明する1つ以上の理論的根拠が含まれます。場合によっては、結果により、推奨事項が生成されない可能性があります。

関連項目:

サンプル・サイズのガイドラインを学習するには、サンプル・サイズの設定のガイドラインを参照してください
DBA_ADVISOR_RECOMMENDATIONSについて学習するには、『Oracle Databaseリファレンス』を参照してください

18.1.2.1.4 オプティマイザ統計アドバイザのアクション

オプティマイザ統計アドバイザ・アクションは、推奨事項を実装するSQLまたはPL/SQLスクリプトです。実行可能な場合、推奨事項には対応するアクションがあります。アドバイザは、DBA_ADVISOR_ACTIONSにアクションを格納します。

たとえば、オプティマイザ統計アドバイザは、次のステップを行うタスクを実行します。

ルールを確認します

アドバイザは、統計の収集時に失効統計を回避するルールへの準拠を確認します。
結果を生成します

アドバイザは、多数のオブジェクトに統計がないことを発見します。
推奨事項を生成します

アドバイザは、統計がないオブジェクトで統計を収集することを推奨します。
アクションを生成します

アドバイザは、DBMS_STATS.GATHER_DATABASE_STATSを実行して統計収集が必要なオブジェクトのリストを提供するPL/SQLスクリプトを生成します。

関連項目:

統計収集プリファレンスをオーバーライドする方法を学習するには、「統計プリファレンスのオーバーライド」を参照してください
AUTO_SAMPLE_SIZEについてさらに学習するには、「サンプル・サイズの設定のガイドライン」を参照してください
DBA_ADVISOR_ACTIONSについて学習するには、『Oracle Databaseリファレンス』を参照してください

18.1.2.2 オプティマイザ統計アドバイザの操作モード

オプティマイザ統計アドバイザは、自動および手動モードをサポートします。

自動

事前定義されたタスクAUTO_STATS_ADVISOR_TASKは、毎日1回メンテナンス・ウィンドウで自動的に実行されます。このタスクは自動オプティマイザ統計収集クライアントの一部として実行されます。自動タスクは結果および推奨事項を生成しますが、自動的にアクションを実装しません。

他のタスクでは、自動タスクを構成してレポートを生成できます。レポートでアクションを推奨する場合、アクションを手動で実装できます。
手動

DBMS_STATS.CREATE_ADVISOR_TASKファンクションを使用して固有のタスクを作成し、EXECUTE_ADVISOR_TASKプロシージャを使用していつでも実行できます。

自動タスクと異なり、手動タスクはアクションを自動的に実装できます。また、タスクを構成して手動で後で実行できるPL/SQLスクリプトを生成できます。

関連項目:

「自動オプティマイザ統計収集の構成」
DBMS_STATS.CREATE_ADVISOR_TASKについてさらに学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください

18.1.3 オプティマイザ統計アドバイザのコマンドライン・インタフェース

オプティマイザ統計アドバイザのタスクを、DBMS_STATS PL/SQLパッケージを使用して実行します。

表18-1 タスクを作成および削除するDBMS_STATS API

PL/SQLプロシージャまたはファンクション	説明
`CREATE_ADVISOR_TASK`	オプティマイザ統計アドバイザのアドバイザ・タスクを作成します。タスク名がすでに指定されている場合、アドバイザは指定されたタスク名を使用します。それ以外の場合、アドバイザは新しいタスク名を自動的に生成します。
`DROP_ADVISOR_TASK`	オプティマイザ統計アドバイザ・タスクおよびすべての結果データを削除します。

表18-2 タスクを実行するDBMS_STATS API

PL/SQLプロシージャまたはファンクション	説明
`EXECUTE_ADVISOR_TASK`	以前に作成されたオプティマイザ統計アドバイザ・タスクを実行します。
`INTERRUPT_ADVISOR_TASK`	現在実行しているオプティマイザ統計アドバイザ・タスクを割り込みます。タスクでは、通常終了のように操作を終了するため、ただちに結果にアクセスできます。後でタスクを再開できます。
`CANCEL_ADVISOR_TASK`	オプティマイザ統計アドバイザのタスク実行を取り消し、現在の実行のすべての中間結果を削除します。
`RESET_ADVISOR_TASK`	オプティマイザ統計アドバイザ・タスクの実行を初期状態にリセットします。現在実行されていないタスクに対してこのプロシージャ呼び出します。
`RESUME_ADVISOR_TASK`	最後に割り込まれたオプティマイザ統計アドバイザのタスク実行を再開します。

表18-3 アドバイザ・レポートのDBMS_STATS API

PL/SQLプロシージャまたはファンクション	説明
`REPORT_STATS_ADVISOR_TASK`	オプティマイザ統計アドバイザ・タスクの結果をレポートします。
`GET_ADVISOR_RECS`	指定された項目の推奨レポートを生成します。

表18-4 タスクおよびフィルタ構成のDBMS_STATS API

PL/SQLプロシージャまたはファンクション	説明
`CONFIGURE_ADVISOR_TASK`	アドバイザ・タスクの実行、レポート、スクリプト生成および実装のためにオプティマイザ統計アドバイザ・リストを構成します。
`GET_ADVISOR_OPR_FILTER`	統計操作の操作フィルタを作成します。
`CONFIGURE_ADVISOR_RULE_FILTER`	オプティマイザ統計アドバイザ・タスクのルール・フィルタを構成します。
`CONFIGURE_ADVISOR_OPR_FILTER`	オプティマイザ統計アドバイザ・タスクの操作フィルタを構成します。
`CONFIGURE_ADVISOR_OBJ_FILTER`	オプティマイザ統計アドバイザ・タスクのオブジェクト・フィルタを構成します。
`SET_ADVISOR_TASK_PARAMETER`	オプティマイザ統計アドバイザ・タスク・パラメータの値を更新します。有効なパラメータは、`TIME_LIMIT`および`OP_START_TIME`です。

表18-5 推奨されるアクションを実装するDBMS_STATS API

PL/SQLプロシージャまたはファンクション	説明
`SCRIPT_ADVISOR_TASK`	アドバイザが検出した問題の推奨されるアクションを実装するスクリプトを取得します。このスクリプトを確認して、実行するアクションを選択できます。
`IMPLEMENT_ADVISOR_TASK`	指定されたオプティマイザ統計アドバイザの実行結果に基づいて、アドバイザが推奨するアクションを実装します。

関連項目:

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

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

この項では、オプティマイザ統計アドバイザを使用するための基本ワークフローについて説明します。すべてのプロシージャとファンクションは、DBMS_STATSパッケージにあります。

次の図は、ワークフローの自動パスおよび手動パスを示しています。AUTO_STATS_ADVISOR_TASKがメンテナンス・ウィンドウで自動的に実行される場合、ワークフローはレポートの問合せから開始します。手動のワークフローでは、PL/SQLを使用してタスクを作成および実行する必要があります:

図18-3 オプティマイザ統計アドバイザの基本タスク

「図18-3 オプティマイザ統計アドバイザの基本タスク」の説明

通常、オプティマイザ統計アドバイザのステップは次の表に示す順序で実行します。

表18-6 オプティマイザ統計アドバイザのワークフロー

ステップ	説明	さらに学習するには
1	`DBMS_STATS.CREATE_ADVISOR_TASK`を使用して、オプティマイザ・アドバイザ・タスクを作成します(手動ワークフローのみ)。	「オプティマイザ統計アドバイザ・タスクの作成」
2	オプションで、`DBA_ADVISOR_EXECUTIONS`を問い合せて、アドバイザ・タスクの実行をリストします。	「オプティマイザ統計アドバイザ・タスクのリスト」
3	オプションで、`DBMS_STATS.CONFIGURE_ADVISOR_*_FILTER`プロシージャを使用してタスクのフィルタを構成します。	「オプティマイザ・アドバイザ・タスクのフィルタの作成」
4	`DBMS_STATS.EXECUTE_ADVISOR_TASK`を使用して、アドバイザ・タスクを実行します(手動ワークフローのみ)。	「オプティマイザ統計アドバイザ・タスクの実行」
5	アドバイザ・レポートを生成します。	「オプティマイザ統計アドバイザ・タスクのレポートの生成」
6	次の方法のいずれかで推奨事項を実装します。 `DBMS_STATS.IMPLEMENT_ADVISOR_TASK`を使用して、すべての推奨事項を自動的に実装します。 `DBMS_STATS.SCRIPT_ADVISOR_TASK`を使用して推奨事項を実装するPL/SQLスクリプトを生成し、このスクリプトを編集し、手動で実行します。	「オプティマイザ統計アドバイザで推奨されるアクションの実装」および「オプティマイザ統計アドバイザを使用したスクリプトの生成」

例18-2 手動ワークフローでのオプティマイザ統計アドバイザの基本スクリプト

このスクリプトは、基本的なオプティマイザ統計アドバイザ・セッションを示しています。タスクを作成して実行し、レポートを生成し、推奨事項を実装します。

DECLARE
  v_tname   VARCHAR2(128) := 'my_task';
  v_ename   VARCHAR2(128) := NULL;
  v_report  CLOB := null;
  v_script  CLOB := null;
  v_implementation_result CLOB;
BEGIN
  -- create a task
  v_tname := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);

  -- execute the task
  v_ename := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);

  -- view the task report
  v_report := DBMS_STATS.REPORT_ADVISOR_TASK(v_tname);
  DBMS_OUTPUT.PUT_LINE(v_report);

  -- implement all recommendations
  v_implementation_result := DBMS_STATS.IMPLEMENT_ADVISOR_TASK(v_tname);
END;

この項では、次の項目について説明します。

関連項目:

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

18.2.1 オプティマイザ統計アドバイザ・タスクの作成

DBMS_STATS.CREATE_ADVISOR_TASKファンクションでは、オプティマイザ統計アドバイザのタスクを作成します。タスク名を指定しない場合、オプティマイザ統計アドバイザが自動的に生成します。

前提条件

このサブプログラムを実行するには、ADVISOR権限を持つ必要があります。

ノート:

このサブプログラムは実行者権限を使用して実行します。

オプティマイザ統計アドバイザ・タスクを作成するには:

SQL*Plusで、必要な権限を持つユーザーとしてデータベースにログインします。
次の形式でDBMS_STATS.CREATE_ADVISOR_TASKファンクションを実行します。tnameはタスク名で、retは戻された出力を含む変数です。
```
EXECUTE ret := DBMS_STATS.CREATE_ADVISOR_TASK('tname');
```
たとえば、タスクopt_adv_task1を作成するには、次のコードを使用します。
```
DECLARE
  v_tname VARCHAR2(32767);
  v_ret   VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  v_ret := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);
END;
/
```

オプションで、USER_ADVISOR_TASKSを問い合せます。

SELECT TASK_NAME, ADVISOR_NAME, CREATED, STATUS FROM USER_ADVISOR_TASKS;

出力例は次のように表示されます。

TASK_NAME       ADVISOR_NAME         CREATED   STATUS
--------------- -------------------- --------- -----------
OPT_ADV_TASK1   Statistics Advisor   05-SEP-16 INITIAL

関連項目:

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

18.2.2 オプティマイザ統計アドバイザ・タスクのリスト

DBA_ADVISOR_EXECUTIONSビューに、オプティマイザ統計アドバイザのタスクの実行がリストされます。

オプティマイザ統計アドバイザ・タスクをリストするには:

SQL*Plusで、管理者権限を持つユーザーとしてデータベースにログインします。

次のようにして、DBA_ADVISOR_EXECUTIONSを問い合せます。

COL EXECUTION_NAME FORMAT a14

SELECT EXECUTION_NAME, EXECUTION_END, STATUS
FROM   DBA_ADVISOR_EXECUTIONS
WHERE  TASK_NAME = 'AUTO_STATS_ADVISOR_TASK'
ORDER BY 2;

次のサンプル出力では8つの実行が示されています。

EXECUTION_NAME EXECUTION STATUS
-------------- --------- -----------
EXEC_1         27-AUG-16 COMPLETED
EXEC_17        28-AUG-16 COMPLETED
EXEC_42        29-AUG-16 COMPLETED
EXEC_67        30-AUG-16 COMPLETED
EXEC_92        01-SEP-16 COMPLETED
EXEC_117       02-SEP-16 COMPLETED
EXEC_142       03-SEP-16 COMPLETED
EXEC_167       04-SEP-16 COMPLETED

8 rows selected.

関連項目:

DBA_ADVISOR_EXECUTIONSについてさらに学習するには、Oracle Databaseリファレンスを参照してください

18.2.3 オプティマイザ・アドバイザ・タスクのフィルタの作成

フィルタにより、オプティマイザ統計アドバイザ・タスクのオブジェクト、ルールおよび操作が含まれるか除外されるかを指定できます。

この項では、次の項目について説明します。

18.2.3.1 オプティマイザ統計アドバイザのフィルタについて

フィルタでは、DBMS_STATSを使用して、オプティマイザ統計アドバイザ・タスクをルール、スキーマまたは操作のユーザー指定セットに制限します。

フィルタは、特定のセットの結果を含めるか除外するために便利です。たとえば、アドバイザ・タスクを構成してshスキーマの推奨事項のみを含めることができます。また、失効した統計のルールのすべての違反を除外できます。フィルタの主な利点は、興味がない推奨事項を無視してアドバイザ・タスクのオーバーヘッドを削減できることです。

フィルタを作成する最も簡単な方法は、次のDBMS_STATSプロシージャを個別または組合せで使用することです。

CONFIGURE_ADVISOR_OBJ_FILTER

このプロシージャを使用して、指定されたデータベース・スキーマまたはオブジェクトを含めるか除外します。オブジェクト・フィルタは、サポートされているワイルドカード(%)を使用して所有者名およびオブジェクト名を取得します。
CONFIGURE_ADVISOR_RULE_FILTER

このプロシージャを使用して、指定されたルールを含めるか除外します。V$STATS_ADVISOR_RULESを問い合せて、ルール名を取得します。
CONFIGURE_ADVISOR_OPR_FILTER

このプロシージャを使用して、指定されたDBMS_STATS操作を含めるか除外します。DBA_OPTSTAT_OPERATIONSを問い合せて、操作のIDおよび名前を取得します。

前述のファンクションでは、フィルタを適用する操作のタイプ(EXECUTE、REPORT、SCRIPTおよびIMPLEMENT)を指定できます。EXECUTE + REPORTのように、タイプを組み合せることもできます。NULLは、フィルタがすべてのタイプのアドバイザ操作に適用されることを示します。

関連項目:

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

18.2.3.2 オプティマイザ・アドバイザ・タスクのオブジェクト・フィルタの作成

DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTERファンクションは、指定されたオプティマイザ統計アドバイザ・タスクのルール・フィルタを作成します。ファンクションでは、フィルタの更新された値を含むCLOBを戻します。

次の基本戦略のいずれかを使用できます。

すべてのオブジェクトの結果を含めて(デフォルトでは、すべてのオブジェクトが考慮されます)、指定されたオブジェクトの結果を除外します。
すべてのオブジェクトの結果を除外し、指定されたオブジェクトのみの結果を含めます。

前提条件

DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTERファンクションを使用するには、次の前提条件を満たす必要があります。

このサブプログラムを実行するには、ADVISOR権限を持つ必要があります。
タスクの所有者である必要があります。

ノート:

このサブプログラムは実行者権限を使用して実行します。

オブジェクト・フィルタを作成するには:

SQL*PlusまたはSQL Developerで、必要な権限を持つユーザーとしてデータベースにログインします。
DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTERファンクションを使用して、指定されたタスクのオブジェクトを除外するか含めます。

次の形式でファンクションを起動します。プレースホルダは次のように定義されます。
- reportは、戻されたXMLを含むCLOB変数です
- tnameは、タスクの名前です
- opr_typeは、実行する操作のタイプです
- ruleは、ルールの名前です
- ownerは、オブジェクトのスキーマです
- tableは、表の名前です
- actionは、アクションの名前(ENABLE、DISABLE、DELETEまたはSHOW)です
```
BEGIN
  report := DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER(
    task_name          => 'tname'
  , stats_adv_opr_type => 'opr_type'
  , rule_name          => 'rule'
  , ownname            => 'owner'
  , tabname            => 'table'
  , action             => 'action' );
END;
```

例18-3 単一のスキーマのオブジェクトのみを含める

この例では、opt_adv_task1というタスクで、shスキーマにあるオブジェクトを除くすべてのオブジェクトの推奨事項を無効化することが目標です。ユーザー・アカウントshにADVISORおよびREAD ANY TABLE権限が付与されています。次のステップを実行します。

shとしてデータベースにログインします。

opt_adv_task1という既存のタスクを削除します。

DECLARE
  v_tname VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

指定されたタスクをshスキーマのオブジェクトに制限するsh_obj_filterというプロシージャを作成します。

CREATE OR REPLACE PROCEDURE sh_obj_filter(p_tname IN VARCHAR2) IS
   v_retc CLOB;
BEGIN
   -- Filter out all objects that are not in the sh schema
   v_retc := DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER(
               task_name          => p_tname
             , stats_adv_opr_type => 'EXECUTE'
             , rule_name          => NULL
             , ownname            => NULL
             , tabname            => NULL
             , action             => 'DISABLE' );

   v_retc := DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER(
               task_name          => p_tname
             , stats_adv_opr_type => 'EXECUTE'
             , rule_name          => NULL
             , ownname            => 'SH'
             , tabname            => NULL
             , action             => 'ENABLE' );
END;
/
SHOW ERRORS

opt_adv_task1というタスクを作成して、このタスクのsh_obj_filterプロシージャを実行します。

DECLARE
  v_tname VARCHAR2(32767);
  v_ret VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  v_ret   := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);
  sh_obj_filter(v_tname);
END;
/

タスクopt_adv_task1を実行します。

DECLARE
  v_tname VARCHAR2(32767);
  v_ret   VARCHAR2(32767);
begin
  v_tname := 'opt_adv_task1';
  v_ret   := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);
END;
/

関連項目:

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

18.2.3.3 オプティマイザ・アドバイザ・タスクのルール・フィルタの作成

DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTERファンクションは、指定されたオプティマイザ統計アドバイザ・タスクのルール・フィルタを作成します。ファンクションでは、フィルタの更新された値を含むCLOBを戻します。

次の基本戦略のいずれかを使用できます。

すべてのルール(デフォルトでは、すべてのルールが有効化されます)を有効化し、指定されたルールを無効化します。
すべてのルールを無効化し、指定されたルールのみを有効化します。

前提条件

DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTERファンクションを使用するには、次の前提条件を満たす必要があります。

このサブプログラムを実行するには、ADVISOR権限を持つ必要があります。
タスクの所有者である必要があります。

ノート:

このサブプログラムは実行者権限を使用して実行します。

ルール・フィルタを作成するには:

SQL*PlusまたはSQL Developerで、必要な権限を持つユーザーとしてデータベースにログインします。

V$STATS_ADVISOR_RULESを問い合せて、アドバイザ・ルールの名前を取得します。

たとえば、ビューを次のように問い合せます(サンプル出力の一部が含まれます)。

SET LINESIZE 200
SET PAGESIZE 100
COL ID FORMAT 99
COL NAME FORMAT a27
COL DESCRIPTION FORMAT a54

SELECT RULE_ID AS ID, NAME, RULE_TYPE, DESCRIPTION 
FROM   V$STATS_ADVISOR_RULES
ORDER BY RULE_ID;

ID NAME                        RULE_TYPE DESCRIPTION
-- --------------------------- --------- ------------------------------------------
 1 UseAutoJob                  SYSTEM    Use Auto Job for Statistics Collection
 2 CompleteAutoJob             SYSTEM    Auto Statistics Gather Job should complete
                                         successfully
 3 MaintainStatsHistory        SYSTEM    Maintain Statistics History
 4 UseConcurrent               SYSTEM    Use Concurrent preference for Statistics
                                         Collection
...

DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTERファンクションを使用して、指定されたタスクのルールを除外するか含めます。

次の形式でファンクションを起動します。プレースホルダは次のように定義されます。
- tnameは、タスクの名前です
- reportは、戻されたXMLを含むCLOB変数です
- opr_typeは、実行する操作のタイプです
- ruleは、ルールの名前です
- actionは、アクションの名前(ENABLE、DISABLE、DELETEまたはSHOW)です
```
BEGIN
  report := DBMS_STATS.DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
    task_name          => 'tname'
  , stats_adv_opr_type => 'opr_type'
  , rule_name          => 'rule'
  , action             => 'action' );
END;
```

例18-4 失効した統計のルールの除外

この例では、自動統計ジョブが実行されなかったため、統計が失効していることがわかります。opt_adv_task1というタスクのレポートを生成しますが、失効した統計の推奨事項を混同させないようにします。

失効した統計を処理するルールのV$STATS_ADVISOR_RULESを問い合せます(出力例も示します)。

COL NAME FORMAT a15
SELECT RULE_ID AS ID, NAME, RULE_TYPE, DESCRIPTION 
FROM   V$STATS_ADVISOR_RULES
WHERE  DESCRIPTION LIKE '%tale%'
ORDER BY RULE_ID;

 ID NAME            RULE_TYPE DESCRIPTION
--- --------------- --------- -----------------------------------------
 12 AvoidStaleStats OBJECT    Avoid objects with stale or no statistics

CONFIGURE_ADVISOR_RULE_FILTERを使用してフィルタを構成し、タスク実行でルールAvoidStaleStatsを除外して他のすべてのルールに従うことを指定します。

VARIABLE b_ret CLOB
BEGIN
   :b_ret := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
     task_name          => 'opt_adv_task1'
,    stats_adv_opr_type => 'EXECUTE'
,    rule_name          => 'AvoidStaleStats'
,    action             => 'DISABLE' );
END;
/

例18-5 失効した統計を回避するためのルールのみを含める

この例は、前述の例の逆です。opt_adv_task1というタスクのレポートを生成しますが、失効した統計の推奨事項のみを確認します。

失効した統計を処理するルールのV$STATS_ADVISOR_RULESを問い合せます(サンプル出力を含みます)。

COL NAME FORMAT a15

SELECT RULE_ID AS ID, NAME, RULE_TYPE, DESCRIPTION 
FROM   V$STATS_ADVISOR_RULES
WHERE  DESCRIPTION LIKE '%tale%'
ORDER BY RULE_ID;

 ID NAME            RULE_TYPE DESCRIPTION
--- --------------- --------- -----------------------------------------
 12 AvoidStaleStats OBJECT    Avoid objects with stale or no statistics

CONFIGURE_ADVISOR_RULE_FILTERを使用してフィルタを構成し、タスク実行がすべてのルールを除外することを指定します。

VARIABLE b_ret CLOB
BEGIN
   :b_ret := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
     task_name          => 'opt_adv_task1'
,    stats_adv_opr_type => 'EXECUTE'
,    rule_name          => null
,    action             => 'DISABLE' );
END;
/

AvoidStaleStatsルールのみを有効化するフィルタを構成します。

BEGIN
   :b_ret := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
     task_name          => 'opt_adv_task1'
,    stats_adv_opr_type => 'EXECUTE'
,    rule_name          => 'AvoidStaleStats'
,    action             => 'ENABLE' );
END;
/

関連項目:

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

18.2.3.4 オプティマイザ・アドバイザ・タスクの操作フィルタの作成

DBMS_STATS.CONFIGURE_ADVISOR_OPR_FILTERファンクションは、指定されたオプティマイザ統計アドバイザ・タスクの操作フィルタを作成します。ファンクションでは、フィルタの更新された値を含むCLOBを戻します。

次の基本戦略のいずれかを使用できます。

すべての操作を無効化して、指定された操作のみを有効化します。
すべての操作を有効化してから(すべての操作はデフォルトで有効化されています)、指定された操作を無効化します。

DBA_OPTSTAT_OPERATIONSビューには、統計関連操作のIDが含まれます。

前提条件

DBMS_STATS.CONFIGURE_ADVISOR_OPR_FILTERファンクションを使用するには、次の前提条件を満たす必要があります。

このサブプログラムを実行するには、ADVISOR権限を持つ必要があります。

ノート:

このサブプログラムは実行者権限を使用して実行します。
タスクの所有者である必要があります。
DBA_OPTSTAT_OPERATIONSビューを問い合せるには、SELECT ANY TABLE権限が必要です。

操作フィルタを作成するには:

SQL*PlusまたはSQL Developerで、必要な権限を持つユーザーとしてデータベースにログインします。

操作のタイプを問い合せます。

たとえば、DBA_OPTSTAT_OPERATIONSのすべての個別の操作をリストします(出力例も示します)。

SQL> SELECT DISTINCT(OPERATION) FROM DBA_OPTSTAT_OPERATIONS ORDER BY OPERATION;

OPERATION
-----------------------
gather_dictionary_stats
gather_index_stats
gather_schema_stats
gather_table_stats
purge_stats
set_system_stats

DBA_OPTSTAT_OPERATIONSを問い合せて、フィルタする操作のIDを取得します。

たとえば、SYSおよびshスキーマの表および索引のすべての統計収集操作のIDを取得するには、次の問合せを使用します。
```
SELECT ID 
FROM   DBA_OPTSTAT_OPERATIONS
WHERE  (  OPERATION = 'gather_table_stats'
          OR OPERATION = 'gather_index_stats')
AND    (  TARGET LIKE 'SH.%'
          OR TARGET LIKE 'SYS.%');
```
前のステップで取得したIDを指定してDBMS_STATS.CONFIGURE_ADVISOR_OPR_FILTERファンクションを使用し、指定されたタスクのルールを除外するか含めます。

次の形式でファンクションを起動します。プレースホルダは次のように定義されます。
- reportは、戻されたXMLを含むCLOB変数です
- tnameは、タスクの名前です
- opr_typeは、実行する操作のタイプですnullは指定できません。
- ruleは、ルールの名前です
- opr_idは、実行する操作のID (DBA_OPTSTAT_OPERATIONS.IDから)です。nullは指定できません。
- actionは、アクションの名前(ENABLE、DISABLE、DELETEまたはSHOW)です
```
BEGIN
  report := DBMS_STATS.CONFIGURE_ADVISOR_OPR_FILTER(
    task_name          => 'tname'
  , stats_adv_opr_type => 'opr_type'
  , rule_name          => 'rule'
  , operation_id       => 'op_id'
  , action             => 'action' );
END;
```

例18-6 表統計を収集する操作の除外

この例では、目標はhrスキーマ内の表統計を収集する操作を除外することです。ユーザー・アカウントstatsにはDBAロール、ADVISOR権限およびSELECT ON DBA_OPTSTAT_OPERATIONS権限が付与されています。次のステップを実行します。

statsとしてデータベースにログインします。

opt_adv_task1という既存のタスクを削除します。

DECLARE
  v_tname VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

opr_filterという名前のプロシージャを作成します。このプロシージャでは、hrスキーマ内の表に対する統計の収集以外、すべての操作についてアドバイスするタスクを構成します。

CREATE OR REPLACE PROCEDURE opr_filter(p_tname IN VARCHAR2) IS
   v_retc CLOB;
BEGIN
   -- For all rules, prevent the advisor from operating 
   -- on the operations selected in the following query
   FOR rec IN 
     (SELECT ID FROM DBA_OPTSTAT_OPERATIONS WHERE OPERATION = 'gather_table_stats' AND TARGET LIKE 'HR.%')
   LOOP
     v_retc := DBMS_STATS.CONFIGURE_ADVISOR_OPR_FILTER(
                 task_name          => p_tname
               , stats_adv_opr_type => NULL
               , rule_name          => NULL
               , operation_id       => rec.id
               , action             => 'DISABLE');
   END LOOP;
END;
/
SHOW ERRORS

opt_adv_task1というタスクを作成して、このタスクのopr_filterプロシージャを実行します。

DECLARE
  v_tname VARCHAR2(32767);
  v_ret VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  v_ret   := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);
  opr_filter(v_tname);
END;
/

タスクopt_adv_task1を実行します。

DECLARE
  v_tname VARCHAR2(32767);
  v_ret   VARCHAR2(32767);
begin
  v_tname := 'opt_adv_task1';
  v_ret   := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);
END;
/

レポートを印刷します。

SPOOL /tmp/rep.txt
SET LONG 1000000
COLUMN report FORMAT A200
SET LINESIZE 250
SET PAGESIZE 1000

SELECT DBMS_STATS.REPORT_ADVISOR_TASK(
         task_name      => 'opt_adv_task1'
       , execution_name => NULL
       , type           => 'TEXT'
       , section        => 'ALL' 
       ) AS report
FROM   DUAL;
SPOOL OFF

関連項目:

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

18.2.4 オプティマイザ統計アドバイザ・タスクの実行

DBMS_STATS.EXECUTE_ADVISOR_TASKファンクションは、オプティマイザ統計アドバイザのタスクを実行します。実行名を指定しない場合、オプティマイザ統計アドバイザが自動的に生成します。

このタスクの実行結果は、実行しているユーザーの権限に依存します。

SYSTEMレベル

ANALYZE ANYおよびANALYZE ANY DICTIONARYの両方の権限を持つユーザーのみが、このタスクをシステム・レベルのルールで実行できます。
操作レベル

結果は次の権限に依存します。
- ANALYZE ANYおよびANALYZE ANY DICTIONARYの両方の権限を持つユーザーが、このタスクをすべての統計操作に実行できます。
- ANALYZE ANY権限はあるがANALYZE ANY DICTIONARY権限はないユーザーが、このタスクをSYS以外のすべてのスキーマに関連した統計操作に実行できます。
- ANALYZE ANY DICTIONARY権限はあるがANALYZE ANY DICTIONARY権限はないユーザーが、このタスクを独自のスキーマおよびSYSスキーマに関連した統計操作に実行できます。
- ANALYZE ANY権限もANALYZE ANY DICTIONARY権限もないユーザーのみが、この操作を独自のスキーマに関連した統計操作に実行できます。
オブジェクト・レベル

ユーザーは、統計収集権限を持つすべてのオブジェクトにこのタスクを実行できます。

前提条件

このタスクには次の前提条件があります。

このサブプログラムを実行するには、ADVISOR権限を持つ必要があります。
タスクの所有者である必要があります。
実行名を指定する場合、この名前を既存の実行と競合させないでください。

ノート:

このサブプログラムは実行者権限を使用して実行します。

オプティマイザ統計アドバイザ・タスクを実行するには:

SQL*PlusまたはSQL Developerで、必要な権限を持つユーザーとしてデータベースにログインします。
次の形式でDBMS_STATS.EXECUTE_ADVISOR_TASKファンクションを実行します。tnameはタスク名で、execnameは実行のオプション名で、retは戻された出力を含む変数です。
```
EXECUTE ret := DBMS_STATS.EXECUTE_ADVISOR_TASK('tname','execname');
```
たとえば、タスクopt_adv_task1を実行するには、次のコードを使用します。
```
DECLARE
  v_tname VARCHAR2(32767);
  v_ret   VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  v_ret := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);
END;
/
```

オプションで、USER_ADVISOR_EXECUTIONSを問い合せて、実行の詳細を取得します。

SELECT TASK_NAME, EXECUTION_NAME,
       EXECUTION_END, EXECUTION_TYPE AS TYPE, STATUS 
FROM   USER_ADVISOR_EXECUTIONS;

出力例は次のように表示されます。

TASK_NAME       EXECUTION_NAME       EXECUTION TYPE       STATUS
--------------- -------------------- --------- ---------- -----------
OPT_ADV_TASK1   EXEC_136             23-NOV-15 STATISTICS COMPLETED

関連項目:

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

18.2.5 オプティマイザ統計アドバイザ・タスクのレポートの生成

DBMS_STATS.REPORT_ADVISOR_TASKファンクションは、オプティマイザ統計アドバイザ・タスクのレポートを生成します。

レポートには次のセクションが含まれています。

一般情報

このセクションでは、タスク名、実行名、作成日および変更日について説明します。
サマリー

このセクションでは、結果および結果に違反するルールをまとめます。
結果

各結果セクションには、関連するルールおよび結果が示されます。アドバイザに推奨事項が含まれる場合、推奨事項が示されます。場合によっては、推奨事項に理論的根拠も含まれます。

自動オプティマイザ・アドバイザ・タスクの名前は、AUTO_STATS_ADVISOR_TASKです。自動ワークフローに従うと、自動生成レポートを問い合せるのみで済みます。

前提条件

DBMS_STATS.REPORT_ADVISOR_TASKファンクションでレポートを生成するには、次の前提条件を満たす必要があります。

このサブプログラムを実行するには、ADVISOR権限を持つ必要があります。
タスクの所有者である必要があります。

ノート:

このサブプログラムは実行者権限を使用して実行します。

このタスクの実行結果は、実行しているユーザーの権限に依存します。

SYSTEMレベル

ANALYZE ANYおよびANALYZE ANY DICTIONARYの両方の権限を持つユーザーのみが、このタスクをシステム・レベルのルールで実行できます。
操作レベル

結果は次の権限に依存します。
- ANALYZE ANYおよびANALYZE ANY DICTIONARYの両方の権限を持つユーザーが、このタスクをすべての統計操作に実行できます。
- ANALYZE ANY権限はあるがANALYZE ANY DICTIONARY権限はないユーザーが、このタスクをSYS以外のすべてのスキーマに関連した統計操作に実行できます。
- ANALYZE ANY DICTIONARY権限はあるがANALYZE ANY DICTIONARY権限はないユーザーが、このタスクを独自のスキーマおよびSYSスキーマに関連した統計操作に実行できます。
- ANALYZE ANY権限もANALYZE ANY DICTIONARY権限もないユーザーのみが、この操作を独自のスキーマに関連した統計操作に実行できます。
オブジェクト・レベル

ユーザーは、統計収集権限を持つすべてのオブジェクトにこのタスクを実行できます。

オプティマイザ統計アドバイザ・レポートを生成するには:

SQL*Plusで、ADVISOR権限を持つユーザーとしてデータベースにログインします。

DBMS_STATS.REPORT_ADVISOR_TASK function output.ファンクション出力を問い合せます。

次の問合せを使用します。ここでプレースホルダの定義は次のとおりです。

tnameは、タスクの名前です
execは実行の名前です。
typeは出力のタイプ(TEXT、HTMLまたはXML)です。
sectは、レポートのセクション(SUMMARY、FINDINGS、ERRORSおよびALL)です。
lvlは、レポートの形式(BASIC、TYPICAL、ALLまたはSHOW_HIDDEN)です。

SET LINESIZE 3000
SET LONG 500000
SET PAGESIZE 0
SET LONGCHUNKSIZE 100000

SELECT DBMS_STATS.REPORT_ADVISOR_TASK('tname', 'exec', 'type', 
       'sect', 'lvl') AS REPORT
FROM   DUAL;

たとえば、AUTO_STATS_ADVISOR_TASKのレポートを印刷するには、次の問合せを使用します。

SELECT DBMS_STATS.REPORT_ADVISOR_TASK('AUTO_STATS_ADVISOR_TASK', NULL,
       'TEXT', 'ALL', 'ALL') AS REPORT 
FROM   DUAL;

次のレポート例では4つの結果が示されています。

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 4 findings. The findings are related to the following rules: 
 AVOIDSETPROCEDURES, USEDEFAULTPARAMS, USEGATHERSCHEMASTATS, NOTUSEINCREMENTAL. 
Please refer to the finding section for detailed information.

-------------------------------------------------------------------------------
FINDINGS
-------------------------------------------------------------------------------
 Rule Name:	    AvoidSetProcedures
 Rule Description:  Avoid Set Statistics Procedures				
 Finding:  There are 5 SET_[COLUMN|INDEX|TABLE|SYSTEM]_STATS procedures being	
           used for statistics gathering.
 Recommendation:  Do not use SET_[COLUMN|INDEX|TABLE|SYSTEM]_STATS procedures.
                  Gather statistics instead of setting them.			
 Rationale:  SET_[COLUMN|INDEX|TABLE|SYSTEM]_STATS will cause bad plans due to
             wrong or inconsistent statistics.				   
----------------------------------------------------
 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.	
----------------------------------------------------
 Rule Name:	    UseGatherSchemaStats
 Rule Description:  Use gather_schema_stats procedure
 Finding:  There are 318 uses of GATHER_TABLE_STATS.
 Recommendation:  Use GATHER_SCHEMA_STATS instead of GATHER_TABLE_STATS.
 Example:

 -- Gather statistics for 'SH' schema:
 BEGIN dbms_stats.gather_schema_stats('SH'); END;
 Rationale:  GATHER_SCHEMA_STATS has more options available, including checking
             for staleness and gathering statistics concurrently. Also it is
             more maintainable for new tables added to the schema. If you only
             want to gather statistics for certain tables in the schema, specify
             them in the obj_filter_list parameter of GATHER_SCHEMA_STATS.						
----------------------------------------------------
 Rule Name:	    NotUseIncremental

 Rule Description:  Statistics should not be maintained incrementally when it is not
 Finding:  Incremental option has been turned on for 10 tables, which will not benefit
           from using the incremental option.
 Schema:
 SH
 Objects:
 CAL_MONTH_SALES_MV    
 CAL_MONTH_SALES_MV    
 CHANNELS
 COUNTRIES   
 CUSTOMERS    
 DIMENSION_EXCEPTIONS    
 FWEEK_PSCAT_SALES_MV    
 FWEEK_PSCAT_SALES_MV    
 PRODUCTS
 PROMOTIONS
 SUPPLEMENTARY_DEMOGRAPHICS 
 TIMES

 Recommendation:  Do not use the incremental option for statistics gathering on these 
                  objects.						
 Example:									
 --
 Turn off the incremental option for 'SH.SALES':
 dbms_stats.set_table_prefs('SH', 'SALES', 'INCREMENTAL', 'FALSE');
 Rationale:  The overhead of using the incremental option on these tables
             outweighs the benefit of using the incremental option.

関連項目:

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

18.2.6 オプティマイザ統計アドバイザ推奨事項の実装

DBMS_STATS.IMPLEMENT_ADVISOR_TASK_を使用してすべての推奨事項を自動的に実装するか、DBMS_STATS.SCRIPT_ADVISOR__TASKを使用して編集可能なスクリプトを生成できます。

この項では、次の項目について説明します。

18.2.6.1 オプティマイザ統計アドバイザで推奨されるアクションの実装

DBMS_STATS.IMPLEMENT_ADVISOR_TASKファンクションは、指定されたオプティマイザ統計アドバイザ・タスクの推奨事項を実装します。実行名を指定しない場合、オプティマイザ統計アドバイザは最新の実行を使用します。

推奨事項を実装する最も簡単な手段は、DBMS_STATS.IMPLEMENT_ADVISOR_TASKを使用することです。この場合、スクリプトの生成は必要ありません。アドバイザが既存のフィルタを無視するか(level=>'ALL')、既存のフィルタに従うデフォルト(level=>'TYPICAL')を使用することを指定できます。

前提条件

DBMS_STATS.IMPLEMENT_ADVISOR_TASKを使用するには、次の前提条件を満たす必要があります。

このサブプログラムを実行するには、ADVISOR権限を持つ必要があります。
タスクの所有者である必要があります。

ノート:

このサブプログラムは実行者権限を使用して実行します。

このタスクの実行結果は、実行しているユーザーの権限に依存します。

SYSTEMレベル

ANALYZE ANYおよびANALYZE ANY DICTIONARYの両方の権限を持つユーザーのみが、このタスクをシステム・レベルのルールで実行できます。
操作レベル

結果は次の権限に依存します。
- ANALYZE ANYおよびANALYZE ANY DICTIONARYの両方の権限を持つユーザーが、このタスクをすべての統計操作に実行できます。
- ANALYZE ANY権限はあるがANALYZE ANY DICTIONARY権限はないユーザーが、このタスクをSYS以外のすべてのスキーマに関連した統計操作に実行できます。
- ANALYZE ANY DICTIONARY権限はあるがANALYZE ANY DICTIONARY権限はないユーザーが、このタスクを独自のスキーマおよびSYSスキーマに関連した統計操作に実行できます。
- ANALYZE ANY権限もANALYZE ANY DICTIONARY権限もないユーザーのみが、この操作を独自のスキーマに関連した統計操作に実行できます。
オブジェクト・レベル

ユーザーは、統計収集権限を持つすべてのオブジェクトにこのタスクを実行できます。

アドバイザ・アクションを実装するには:

SQL*Plusで、必要な権限を持つユーザーとしてデータベースにログインします。
次の形式でDBMS_STATS.IMPLEMENT_ADVISOR_TASKファンクションを実行します。プレースホルダには、次の定義が含まれます。
- tnameは、タスクの名前です
- resultは、正常に実装された推奨事項のリストを含むCLOB変数です
- fltr_lvlは、実装のレベル(TYPICAL(既存のフィルタに従う)またはALL(フィルタを無視))です。
```
BEGIN
  result := DBMS_STATS.IMPLEMENT_ADVISOR_TASK('tname', level => fltr_lvl);
END;
```
たとえば、タスクopt_adv_task1のすべての推奨事項を実装するには、次のコードを使用します。
```
VARIABLE b_ret CLOB
DECLARE
  v_tname VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  :b_ret := DBMS_STATS.IMPLEMENT_ADVISOR_TASK(v_tname);
END;
/
```

オプションで、XML出力を印刷して実装されたアクションを確認します。

たとえば、前のステップで戻されたXMLを印刷するには、次のコードを使用します(出力例も示します)。

SET LONG 10000
SELECT XMLType(:b_ret) AS imp_results FROM DUAL;

IMP_RESULTS
------------------------------------
<implementation_results>
  <rule NAME="AVOIDSETPROCEDURES">
    <implemented>yes</implemented>
  </rule>
  <rule NAME="USEGATHERSCHEMASTATS">
    <implemented>yes</implemented>
  </rule>
  <rule NAME="AVOIDSETPROCEDURES">
    <implemented>yes</implemented>
  </rule>
  <rule NAME="USEGATHERSCHEMASTATS">
    <implemented>yes</implemented>
  </rule>
  <rule NAME="USEDEFAULTPARAMS">
    <implemented>no</implemented>
  </rule>
  <rule NAME="USEDEFAULTPARAMS">
    <implemented>yes</implemented>
  </rule>
  <rule NAME="NOTUSEINCREMENTAL">
    <implemented>yes</implemented>
  </rule>
</implementation_results>

関連項目:

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

18.2.6.2 オプティマイザ統計アドバイザを使用したスクリプトの生成

DBMS_STATS.SCRIPT_ADVISOR_TASKファンクションは、指定されたオプティマイザ統計アドバイザ・タスクの推奨事項を使用して編集可能なスクリプトを生成します。

IMPLEMENT_ADVISOR_TASKと異なり、SCRIPT_ADVISOR_TASKは、実行前に編集できるスクリプトを生成します。出力スクリプトには、コメントおよび実行可能なコードが含まれます。IMPLEMENT_ADVISOR_TASKと同様に、アドバイザが既存のフィルタを無視するか(level=>'ALL')、既存のフィルタに従うデフォルト(level=>'TYPICAL')を使用することを指定できます。ファンクションがCLOBおよびファイルまたはCLOBのみとしてスクリプトを戻すことを指定できます。

前提条件

DBMS_STATS.SCRIPT_ADVISOR_TASKファンクションを使用するには、次の前提条件を満たす必要があります。

このサブプログラムを実行するには、ADVISOR権限を持つ必要があります。
タスクの所有者である必要があります。

ノート:

このサブプログラムは実行者権限を使用して実行します。

このタスクの実行結果は、実行しているユーザーの権限に依存します。

SYSTEMレベル

ANALYZE ANYおよびANALYZE ANY DICTIONARYの両方の権限を持つユーザーのみが、このタスクをシステム・レベルのルールで実行できます。
操作レベル

結果は次の権限に依存します。
- ANALYZE ANYおよびANALYZE ANY DICTIONARYの両方の権限を持つユーザーが、このタスクをすべての統計操作に実行できます。
- ANALYZE ANY権限はあるがANALYZE ANY DICTIONARY権限はないユーザーが、このタスクをSYS以外のすべてのスキーマに関連した統計操作に実行できます。
- ANALYZE ANY DICTIONARY権限はあるがANALYZE ANY DICTIONARY権限はないユーザーが、このタスクを独自のスキーマおよびSYSスキーマに関連した統計操作に実行できます。
- ANALYZE ANY権限もANALYZE ANY DICTIONARY権限もないユーザーのみが、この操作を独自のスキーマに関連した統計操作に実行できます。
オブジェクト・レベル

ユーザーは、統計収集権限を持つすべてのオブジェクトにこのタスクを実行できます。

アドバイザ・スクリプトを生成するには:

SQL*Plusで、ADVISOR権限を持つユーザーとしてデータベースにログインします。
次の形式でDBMS_STATS.SCRIPT_ADVISOR_TASKファンクションを実行します。プレースホルダには、次の定義が含まれます。
- tnameは、タスクの名前です
- execは実行の名前です(デフォルトはnullです)
- dirはディレクトリの名前です(デフォルトはnullです)
- resultは、正常に実装された推奨事項のリストを含むCLOB変数です
- filter_lvlは、実装のレベル(TYPICAL(既存のフィルタに従う)またはALL(フィルタを無視))です。
```
BEGIN
  result := DBMS_STATS.SCRIPT_ADVISOR_TASK('tname', 
            execution_name => 'exec', dir_name => 'dir', 
            level => 'filter_lvl');
END;
```
たとえば、タスクopt_adv_task1の推奨事項を含むスクリプトを生成するには、次のコードを使用します。
```
VARIABLE b_script CLOB
DECLARE
  v_tname VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  :b_script := DBMS_STATS.SCRIPT_ADVISOR_TASK(v_tname);
END;
/
```
ノート:

実行名を指定しない場合、オプティマイザ統計アドバイザは最新の実行を使用します。

スクリプトを印刷します。

たとえば、前のステップで戻されたスクリプトを印刷するには、次のコードを使用します(出力例も示します)。

DECLARE
  v_len    NUMBER(10);
  v_offset NUMBER(10) :=1;
  v_amount NUMBER(10) :=10000;
BEGIN
  v_len := DBMS_LOB.getlength(:b_script);
  WHILE (v_offset < v_len)
  LOOP
    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(:b_script,v_amount,v_offset));
    v_offset := v_offset + v_amount;
  END LOOP;
END;
/

次の例はスクリプトのサンプルです。

-- Script generated for the recommendations from execution EXEC_23
-- in the statistics advisor task OPT_ADV_TASK1
-- Script version 12.2

-- No scripts will be provided for the rule AVOIDSETPROCEDURES.  Please check the 
-- report for more details.
-- No scripts will be provided for the rule USEGATHERSCHEMASTATS. Please check the 
-- report for more details.
-- No scripts will be provided for the rule AVOIDINEFFICIENTSTATSOPRSEQ. Please check 
-- the report for more details.
-- No scripts will be provided for the rule AVOIDUNNECESSARYSTATSCOLLECTION. Please
-- check the report for more details.
-- No scripts will be provided for the rule GATHERSTATSAFTERBULKDML. Please check the 
-- report for more details.
-- No scripts will be provided for the rule AVOIDDROPRECREATE. Please check the report 
-- for more details.
-- No scripts will be provided for the rule AVOIDOUTOFRANGE. Please check the report 
-- for more details.
-- No scripts will be provided for the rule AVOIDANALYZETABLE. Please check the report 
-- for more details.
-- No scripts will be provided for the rule AVOIDSETPROCEDURES. Please check the 
-- report for more details.
-- No scripts will be provided for the rule USEGATHERSCHEMASTATS. Please check the 
-- report for more details.
-- No scripts will be provided for the rule AVOIDINEFFICIENTSTATSOPRSEQ. Please 
-- check the report for more details.
-- No scripts will be provided for the rule AVOIDUNNECESSARYSTATSCOLLECTION. Please check 
-- the report for more details.
-- No scripts will be provided for the rule GATHERSTATSAFTERBULKDML. Please check the 
-- report for more details.
-- No scripts will be provided for the rule AVOIDDROPRECREATE. Please check the report 
-- for more details.
-- No scripts will be provided for the rule AVOIDOUTOFRANGE. Please check the report 
-- for more details.
-- No scripts will be provided for the rule AVOIDANALYZETABLE. Please check the report 
-- for more details.

-- Scripts for rule USEDEFAULTPARAMS
-- Rule Description: Use Default Parameters in Statistics Collection Procedures
-- Use the default preference value for parameters

begin dbms_stats.set_global_prefs('PREFERENCE_OVERRIDES_PARAMETER', 'TRUE'); end;
/

-- Scripts for rule USEDEFAULTOBJECTPREFERENCE
-- Rule Description: Use Default Object Preference for statistics collection
-- Setting object-level preferences to default values
-- setting CASCADE to default value for object level preference
-- setting ESTIMATE_PERCENT to default value for object level preference
-- setting METHOD_OPT to default value for object level preference
-- setting GRANULARITY to default value for object level preference
-- setting NO_INVALIDATE to default value for object levelpreference

-- Scripts for rule USEINCREMENTAL
-- Rule Description: Statistics should be maintained incrementally when it is
-- beneficial.
-- Turn on the incremental option for those objects for which using incremental is
-- helpful.

-- Scripts for rule UNLOCKNONVOLATILETABLE
-- Rule Description: Statistics for objects with non-volatile should not be locked
-- Unlock statistics for objects that are not volatile.

-- Scripts for rule LOCKVOLATILETABLE
-- Rule Description: Statistics for objects with volatile data should be locked
-- Lock statistics for volatile objects.

-- Scripts for rule NOTUSEINCREMENTAL
-- Rule Description: Statistics should not be maintained incrementally when it is not
   beneficial
-- Turn off incremental option for those objects for which using incremental is not
-- helpful.

begin dbms_stats.set_table_prefs('SH', 'CAL_MONTH_SALES_MV', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'CHANNELS', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'COUNTRIES', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'CUSTOMERS', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'DIMENSION_EXCEPTIONS', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'FWEEK_PSCAT_SALES_MV', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'PRODUCTS', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'PROMOTIONS', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'SUPPLEMENTARY_DEMOGRAPHICS', 'INCREMENTAL', 'FALSE'); end;
/
begin dbms_stats.set_table_prefs('SH', 'TIMES', 'INCREMENTAL', 'FALSE'); end;
/

-- Scripts for rule USEAUTODEGREE
-- Rule Description: Use Auto Degree for statistics collection
-- Turn on auto degree for those objects for which using auto degree is helpful.

-- Scripts for rule AVOIDSTALESTATS
-- Rule Description: Avoid objects with stale or no statistics
-- Gather statistics for those objcts that are missing or have no statistics.

-- Scripts for rule MAINTAINSTATSCONSISTENCY
-- Rule Description: Statistics of dependent objects should be consistent
-- Gather statistics for those objcts that are missing or have no statistics.

関連項目:

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