18 オプティマイザ統計アドバイザを使用した統計の分析
オプティマイザ統計アドバイザは、オプティマイザ統計の収集方法を分析し、推奨事項を生成します。
この章のトピックは、次のとおりです:
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.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プロシージャまたはファンクション | 説明 |
---|---|
|
オプティマイザ統計アドバイザのアドバイザ・タスクを作成します。タスク名がすでに指定されている場合、アドバイザは指定されたタスク名を使用します。それ以外の場合、アドバイザは新しいタスク名を自動的に生成します。 |
|
オプティマイザ統計アドバイザ・タスクおよびすべての結果データを削除します。 |
表18-2 タスクを実行するDBMS_STATS API
PL/SQLプロシージャまたはファンクション | 説明 |
---|---|
|
以前に作成されたオプティマイザ統計アドバイザ・タスクを実行します。 |
|
現在実行しているオプティマイザ統計アドバイザ・タスクを割り込みます。タスクでは、通常終了のように操作を終了するため、ただちに結果にアクセスできます。後でタスクを再開できます。 |
|
オプティマイザ統計アドバイザのタスク実行を取り消し、現在の実行のすべての中間結果を削除します。 |
|
オプティマイザ統計アドバイザ・タスクの実行を初期状態にリセットします。現在実行されていないタスクに対してこのプロシージャ呼び出します。 |
|
最後に割り込まれたオプティマイザ統計アドバイザのタスク実行を再開します。 |
表18-3 アドバイザ・レポートのDBMS_STATS API
PL/SQLプロシージャまたはファンクション | 説明 |
---|---|
|
オプティマイザ統計アドバイザ・タスクの結果をレポートします。 |
|
指定された項目の推奨レポートを生成します。 |
表18-4 タスクおよびフィルタ構成のDBMS_STATS API
PL/SQLプロシージャまたはファンクション | 説明 |
---|---|
|
アドバイザ・タスクの実行、レポート、スクリプト生成および実装のためにオプティマイザ統計アドバイザ・リストを構成します。 |
|
統計操作の操作フィルタを作成します。 |
|
オプティマイザ統計アドバイザ・タスクのルール・フィルタを構成します。 |
|
オプティマイザ統計アドバイザ・タスクの操作フィルタを構成します。 |
|
オプティマイザ統計アドバイザ・タスクのオブジェクト・フィルタを構成します。 |
|
オプティマイザ統計アドバイザ・タスク・パラメータの値を更新します。有効なパラメータは、 |
表18-5 推奨されるアクションを実装するDBMS_STATS API
PL/SQLプロシージャまたはファンクション | 説明 |
---|---|
|
アドバイザが検出した問題の推奨されるアクションを実装するスクリプトを取得します。このスクリプトを確認して、実行するアクションを選択できます。 |
|
指定されたオプティマイザ統計アドバイザの実行結果に基づいて、アドバイザが推奨するアクションを実装します。 |
関連項目:
DBMS_STATS
パッケージについて学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
18.2 オプティマイザ統計アドバイザの基本タスク
この項では、オプティマイザ統計アドバイザを使用するための基本ワークフローについて説明します。すべてのプロシージャとファンクションは、DBMS_STATS
パッケージにあります。
次の図は、ワークフローの自動パスおよび手動パスを示しています。AUTO_STATS_ADVISOR_TASK
がメンテナンス・ウィンドウで自動的に実行される場合、ワークフローはレポートの問合せから開始します。手動のワークフローでは、PL/SQLを使用してタスクを作成および実行する必要があります:
通常、オプティマイザ統計アドバイザのステップは次の表に示す順序で実行します。
表18-6 オプティマイザ統計アドバイザのワークフロー
ステップ | 説明 | さらに学習するには |
---|---|---|
1 |
|
|
2 |
オプションで、 |
|
3 |
オプションで、 |
|
4 |
|
|
5 |
アドバイザ・レポートを生成します。 |
|
6 |
次の方法のいずれかで推奨事項を実装します。
|
「オプティマイザ統計アドバイザで推奨されるアクションの実装」および「オプティマイザ統計アドバイザを使用したスクリプトの生成」 |
例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
(フィルタを無視))です。
EXEC result := DBMS_STATS.SCRIPT_ADVISOR_TASK('tname', execution_name => 'exec', dir_name => 'dir', level => '
filter_lvl
');たとえば、タスク
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_report); 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 -- 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パッケージおよびタイプ・リファレンス』を参照してください。