1. データベース・パフォーマンス・チューニング・ガイド
  2. システム・リソースの管理
  3. 自動マテリアライズド・ビューの使用

17 自動マテリアライズド・ビューの使用

Oracle Databaseリリース21c以降では、マテリアライズド・ビューを自動的に作成およびメンテナンスできます。

Oracle Databaseでは、問合せパフォーマンスを最適化するために、マテリアライズド・ビューを自動的に作成および管理できます。DBAがほとんどまたはまったく操作することなく、バックグラウンド・タスクによってワークロードの特性が監視および分析され、マテリアライズド・ビューでSQLパフォーマンスが向上する可能性があるのはどこかが特定されます。候補のマテリアライズド・ビューのパフォーマンス上の利点は、ワークロードで確認する前に(ワークロード問合せを使用して)バックグラウンドで測定されます。

ノート:

自動マテリアライズド・ビューでは、パーティション化された実表とパーティション化されていない実表がサポートされます。マテリアライズド・ビューの増分リフレッシュがサポートされています。また、パーティション表では、パーティション・チェンジ・トラッキング(PCT)ビューのリフレッシュがサポートされています。PCTベースのリフレッシュの対象となるには、パーティション化された実表が、範囲、リストまたはコンポジット・パーティション化のいずれかを使用している必要があります。パフォーマンス上の利点がある場合、自動マテリアライズド・ビュー推奨事項には、マテリアライズド・ビューの実表のパーティション化に基づくパーティション化された自動マテリアライズド・ビューが含まれます。サポートされているパーティション化タイプは、ファクト表のパーティション化をミラー化する自動リスト・パーティション化です。

自動マテリアライズド・ビューのメンテナンス・モジュールは、リフレッシュ時点で最も有益なリフレッシュのタイプを決定し、実行時に増分リフレッシュから完全リフレッシュに切り替えるかどうかを決定します。

自動マテリアライズド・ビューの概要

データベースは、ワークロード情報、ワークロード問合せおよび問合せ実行統計を自動的に収集します。また、ワークロードの履歴のメンテナンスおよびパージも実行します。これにより、時間のかかるDBAタスクが排除されます。

自動マテリアライズド・ビューは最小限のDBA操作で実行できますが、その動作は簡単に調整できます。

次に、自動マテリアライズド・ビュー機能の概要を示します。

  • ワークロード問合せ実行統計を自動的に検出して収集します。これには、バッファ読取り、データベース時間、推定コストおよびその他の統計が含まれます。
  • データベース・ワークロードから隠された候補のマテリアライズド・ビューを作成し、予測されるパフォーマンス上の利点が得られることを確認します。これを行うには、バックグラウンドでワークロード問合せのサンプルをテスト実行します。
  • パフォーマンス・テストの結果および実装されているマテリアライズド・ビューの詳細を示すレポートを提供します。
  • マテリアライズド・ビューの自動リフレッシュを提供します。

データベースは、自動マテリアライズド・ビューのうち、利点がメンテナンスのコストを大幅に上回るもののみを実装します。利点がわずかであるものは実装しません。

オブジェクト・アクティビティ・トラッキング・システムによって提供されるワークロード情報

自動マテリアライズド・ビューでは、自動意思決定プロセスの一環として、オブジェクト・アクティビティ・トラッキング・システム(OATS)によって提供されるワークロード情報が使用されます。

Oracle Database 21c以降、オブジェクト・アクティビティ・トラッキング・システム(OATS)によって、データベース・オブジェクトに関連付けられた様々なアクティビティが追跡されます。自動マテリアライズド・ビュー機能は、このシステムのクライアントの1つです。自動マテリアライズド・ビューの場合、OATSによって提供される使用状況データが、マテリアライズド・ビューの作成またはリフレッシュ、および最適なリフレッシュ・タイプと最適なリフレッシュ・スケジュールの決定における費用対効果分析に入力されるデータの1つになります。

選択した任意の数の表内のアクティビティのスナップショットがOATSによって定期的に取得されます。各表のスナップショットでは、スナップショット間隔の開始から終了までの表内のスキャン、ロード、挿入/更新/削除、切捨ておよびパーティション関連アクティビティの数が取得されます。DBAは、DBMS_ACTIVITY PL/SQLパッケージを使用して、OATS取得間隔、スナップショット保存期間および領域制限を設定できます。

たとえば、DBA_ACTIVITY_TABLEビューには、各スナップショット内で取得された使用状況データが表示されます。

自動マテリアライズド・ビューおよびOATSに関する情報を提供するデータ・ディクショナリ・ビュー

Oracle Database 21cでは、データベースに、自動マテリアライズド・ビューおよびOATS (オブジェクト・アクティビティ・トラッキング・システム)に関する情報を表示するデータ・ディクショナリ・ビューが含まれています。

自動マテリアライズド・ビューを監視するためのビュー

次のデータ・ディクショナリ・ビューを使用して、自動マテリアライズド・ビューの構成を確認し、自動マテリアライズド・ビューのアクティビティの様々な側面を調べます。

  • DBA_AUTO_MV_ANALYSIS_ACTIONS

    アクション、コマンド、アドバイザ固有のフラグ、コマンド・パラメータなど、分析およびチューニング・タスクに関する情報が表示されます。

  • DBA_AUTO_MV_ANALYSIS_EXECUTIONS

    同時実行性、ユーザーによってリクエストされた並列度(DOP)および実行終了時の実際のDOP、ステータス、関連するアドバイザ、情報またはエラー・メッセージなど、分析およびチューニングの実行に関する情報が表示されます。

  • DBA_AUTO_MV_ANALYSIS_RECOMMENDATIONS

    自動マテリアライズド・ビューに関連付けられている推奨事項が表示されます。

  • DBA_AUTO_MV_ANALYSIS_REPORT

    タスク名と実行名、ジャーナル・エントリの連番、ジャーナルのメッセージ・エントリなど、分析および推奨事項に関するレポート。

  • DBA_AUTO_MV_ANALYSIS_TASK

    タスク識別子とタスクの説明、作成日と最終変更日、実行データ、親タスク、ステータス、その他の情報など、自動マテリアライズド・ビューに関連付けられている分析の詳細が表示されます。

  • DBA_AUTO_MV_CONFIG

    現在の自動マテリアライズド・ビューの構成が表示されます。

    ノート:

    このビューに表示される構成パラメータは、DBMS_AUTO_MVパッケージのCONFIGUREプロシージャを使用して更新できます。
  • DBA_AUTO_MV_MAINT_REPORT

    自動マテリアライズド・ビューのメンテナンス・アクションに関連付けられている日付、時間およびメッセージが表示されます。

  • DBA_AUTO_MV_REFRESH_HISTORY

    自動マテリアライズド・ビューの各リフレッシュの所有者名、ビュー名、日付、開始時間と終了時間、経過時間、ステータスおよびエラー番号(エラーが発生した場合)が表示されます。

  • DBA_AUTO_MV_VERIFICATION_REPORT

    検証に関連付けられているタスク名、実行名およびメッセージが表示されます。

  • DBA_AUTO_MV_VERIFICATION_STATUS

    検証の所有者、開始/終了タイムスタンプ、使用されるSQLチューニング・セットおよび各検証で使用されるSQLパフォーマンス・アナライザ・タスクが表示されます。

OATSを監視するためのビュー

  • DBA_ACTIVITY_CONFIG

    OATSを制御する構成パラメータの現在の値が表示されます。

    ノート:

    このビューに表示される構成パラメータは、DBMS_ACTIVITYパッケージのCONFIGUREプロシージャを使用して更新できます。
  • DBMS_ACTIVITY_TABLE

    OATSによって最近取得された表アクティビティのスナップショットについて説明します。

  • DBA_ACTIVITY_SNAPSHOT_META

    OATSによって取得されたアクティビティのスナップショットに関する情報が表示されます。

  • DBA_ACTIVITY_MVIEW

    OATSによって最近取得されたマテリアライズド・ビュー・アクティビティのスナップショットについて説明します。

関連項目:

Oracle Databaseリファレンス

DBMS_AUTO_MVパッケージ

このパッケージには、自動マテリアライズド・ビューを制御するためのプロシージャが含まれています。

DBMS_AUTO_MV.CONFIGURE

DBAは、DBMS_AUTO_MVのCONFIGUREプロシージャを使用して、自動マテリアライズド・ビューを起動、停止および構成できます。

表17-1 構成プロシージャ・パラメータ

パラメータ 説明と例
AUTO_MV_MODE

IMPLEMENT: 自動マテリアライズド・ビューを実装します。

OFF: 自動マテリアライズド・ビューをオフにします。

REPORT ONLY: レポート専用モードです。

exec dbms_auto_mv.configure('AUTO_MV_MODE', 'IMPLEMENT');
exec dbms_auto_mv.configure('AUTO_MV_MODE', 'OFF');
exec dbms_auto_mv.configure('AUTO_MV_MODE', 'REPORT ONLY');
AUTO_MV_MAINT_TASK

ENABLE: メンテナンス(リフレッシュ、検証およびクリーン・アップ)を実行するタスクをアクティブ化します。

DISABLE: メンテナンスを実行するタスクを非アクティブ化します。

CLEANUP_AND_DISABLE: すべての自動マテリアライズド・ビューを削除し、タスクを非アクティブ化します。自動マテリアライズド・ビュー・メンテナンスが進行中の場合、タスクが非アクティブ化される前にメンテナンスを終了できます。 

exec dbms_auto_mv.configure('AUTO_MV_MAINT_TASK', 'ENABLE');
exec dbms_auto_mv.configure('AUTO_MV_MAINT_TASK', 'DISABLE');
exec dbms_auto_mv.configure('AUTO_MV_MAINT_TASK', 'CLEANUP_AND_DISABLE');
AUTO_MV_SPACE_BUDGET 自動マテリアライズド・ビューを実装するために、これらのビューが作成された表領域内に配分される領域の割合を指定します。これは、表領域内のすべての自動マテリアライズド・ビューおよび関連する索引によって使用される合計領域の割合です。

AUTO_MV_SPACE_BUDGETの強制に関する条件は、AUTO_MV_DEFAULT_TABLESPACEの値です。

  • AUTO_MV_DEFAULT_TABLESPACEが定義されていない(NULL)場合、自動マテリアライズド・ビューは、ビューの親オブジェクト(ビューの定義で最大のファクト表)の表領域に作成されます。この場合、AUTO_MV_SPACE_BUDGETで定義された配分がその表領域内に強制適用されます。
  • AUTO_MV_DEFAULT_TABLESPACEが定義されている場合、自動マテリアライズド・ビューは指定されたデフォルトの表領域に作成されます。この場合、AUTO_MV_SPACE_BUDGETで設定された配分は無視されます。

配分を超えた場合(おそらく自動マテリアライズド・ビューの増加が原因)、最も使用されていない自動マテリアライズド・ビューが削除されます。

値は、1から100の整数です。デフォルトは67 (表領域の合計量の67%)です。 

exec dbms_auto_mv.configure('AUTO_MV_SPACE_BUDGET', 15);
AUTO_MV_DEFAULT_TABLESPACE 自動マテリアライズド・ビューを作成するためのデフォルト表領域を指定します。使用可能な値は、有効な一時表領域の名前またはNULL(デフォルト)です。NULLの場合、新しい自動マテリアライズド・ビューは親オブジェクトの所有者のデフォルト表領域に作成されます。複数の実表に定義されているマテリアライズド・ビューなど、ビューに複数の親オブジェクトがある場合は、最大の実表の所有者のデフォルト表領域が選択されます。

値が動的に変更された場合、変更は自動マテリアライズド・ビューの推奨事項が次に実装されたときに有効になります。 

exec dbms_auto_mv.configure('AUTO_MV_DEFAULT_TABLESPACE','MYTABLESPACE');
exec dbms_auto_mv.configure('AUTO_MV_DEFAULT_TABLESPACE');
AUTO_MV_TEMP_TABLESPACE 自動マテリアライズド・ビューの作成またはリフレッシュに使用する一時表領域を指定します。使用可能な値は、有効な一時表領域の名前またはNULLです。NULLの場合、表領域は自動マテリアライズド・ビューの最大の親オブジェクトの所有者に割り当てられます。デフォルトはNULLです。
exec dbms_auto_mv.configure('AUTO_MV_TEMP_TABLESPACE','TEMP2');
exec dbms_auto_mv.configure('AUTO_MV_TEMP_TABLESPACE');
AUTO_MV_RETENTION 自動マテリアライズド・ビューが問合せなしで存在し続けることができる日数を指定します。この保存時間を超えても自動マテリアライズド・ビューが使用されない場合は、自動的に削除されます。

使用可能な値は1から373までの任意の整数です。デフォルトは33日です。 

exec dbms_auto_mv.configure('AUTO_MV_RETENTION', 365);
AUTO_MV_ANALYZE_REPORT_RETENTION
AUTO_MV_ANALYZE_REPORT_RETENTION 分析および推奨履歴を保持する最大日数を指定します。使用可能な値は0から90までの任意の整数です。値0は、履歴が保持されないことを意味します。デフォルトは31日です。 
exec dbms_auto_mv.configure('AUTO_MV_ANALYZE_REPORT_RETENTION', 60);
AUTO_MV_VERIFY_REPORT_RETENTION

検証履歴を保存する最大日数を指定します。使用可能な値は0から90までの任意の整数です。値0は、検証履歴が保持されないことを指定します。デフォルトは31日です。

exec dbms_auto_mv.configure('AUTO_MV_VERIFY_REPORT_RETENTION', 7);
AUTO_MV_MAINT_REPORT_RETENTION 自動マテリアライズド・ビュー・メンテナンス(リフレッシュ)の履歴をDBA_AUTO_MV_REFRESH_*ディクショナリ表に保持する最大日数を指定します。使用可能な値は0から90までの任意の整数です。値0は、リフレッシュ履歴が保持されないことを指定します。デフォルトは31日です。
exec dbms_auto_mv.configure('AUTO_MV_MAINT_REPORT_RETENTION', 14);
AUTO_MV_ANALYZE_WORKLOAD_WINDOW 最新のスナップショットからの問合せを調査して推奨する最大時間数を指定します。使用可能な値は1から8760までの任意の整数です。デフォルトは24時間です。
exec dbms_auto_mv.configure('AUTO_MV_ANALYZE_WORKLOAD_WINDOW', 48);
AUTO_MV_ANALYZE_WORKLOAD_MIN_TIME 自動マテリアライズド・ビューの推奨事項で問合せが考慮される最小時間を秒単位で指定します。このしきい値を下回る問合せは、推奨事項とみなされません。使用可能な値は0から3600までの任意の整数です。デフォルトは120秒です。 
exec dbms_auto_mv.configure('AUTO_MV_ANALYZE_WORKLOAD_MIN_TIME', 1800);
AUTO_MV_SCHEMA 自動マテリアライズド・ビューの作成時に含めるスキーマまたは除外するスキーマを指定します。スキーマが構成の包含リストまたは除外リストに追加されます。最初は、両方のリストが空で、自動マテリアライズド・ビューが有効になっているデータベース内のすべてのスキーマに自動マテリアライズド・ビューを作成できます。包含リストと除外リストは、AUTO_MV_SCHEMAを複数回コールすることで作成できます。

ブールALLOWは、スキーマが包含リスト(TRUE)に追加されるか、除外リスト(FALSE)に追加されるかを決定します。デフォルトはTRUEです。ワークロードの処理中に、包含リストのスキーマ内の1つ以上の表に対する参照を含まない問合せは分析されず、自動チューニングされません。推奨および検証にはファクタリングされません。同様に、問合せが除外リストのスキーマ内の表を参照する場合、その問合せは処理から除外されます。 

exec dbms_auto_mv.configure(‘AUTO_MV_SCHEMA’, ’SCHEMA_A’);
exec dbms_auto_mv.configure(‘AUTO_MV_SCHEMA’, ’SCHEMA_B’, FALSE);
すべてのスキーマの処理を有効または無効にするには、スキーマをNULLとして指定します。これは、ALLOWの値に応じて、すべてを有効または無効にします。 
exec dbms_auto_mv.configure(‘AUTO_MV_SCHEMA’,'', TRUE);
AUTO_MV_APP_MODULE 自動マテリアライズド・ビューの作成に含める、または作成から除外するアプリケーション・モジュールを指定します。
exec dbms_auto_mv.configure('AUTO_MV_APP_MODULE', 'MODULE1', TRUE);
exec dbms_auto_mv.configure('AUTO_MV_APP_MODULE', 'MODULE1', FALSE);
exec dbms_auto_mv.configure('AUTO_MV_APP_MODULE', 'MODULE1');

DBMS_AUTO_MV.DROP_AUTO_MVS

このプロシージャは、自動マテリアライズド・ビューを削除します。DBAロールを持つユーザーのみが実行できます。

パラメータ 説明
OWNER 自動マテリアライズド・ビューの所有者の名前です。
MV_NAME 自動マテリアライズド・ビューの名前です。
ALLOW_RECREATE 必要に応じてマテリアライズド・ビューの再作成を許可します。オプションです。

OWNERが指定され、MV_NAMEがNULLに設定されている場合、OWNERが所有するすべての自動マテリアライズド・ビューが削除されます。 

exec dbms_auto_mv.drop_auto_mvs(‘SH’, ‘AUTO_MV$$_G2MKPB9SA1FB7’, TRUE);
exec dbms_auto_mv.drop_auto_mvs(‘SH’, ‘AUTO_MV$$_G2MKPB9SA1FB7’);
exec dbms_auto_mv.drop_auto_mvs(‘SH’, '');
exec dbms_auto_mv.drop_auto_mvs(‘SH’, '', TRUE);

DBMS_AUTO_MV.RECOMMEND

DBMS_AUTO_MV.RECOMMENDは、特定のSQLチューニング・セットに基づいて、自動マテリアライズされた推奨事項を生成します。このAPIを使用すると、(自動SQLチューニング・タスクを介さずに)コマンドラインから自動マテリアライズド・ビュー分析および検証を手動で実行できます。ワークロードの開始時間と終了時間を設定し、この実行結果がレポートのみになるか、実際の実装になるかを決定します。ワークロード・ウィンドウにはデフォルトの時間制限はありません。

このAPIを実行するには、DBAロールが必要です。

ノート:

自動マテリアライズド・ビュー構成パラメータは、DBMS_AUTO_MV.RECOMMENDの結果に影響を与える可能性があります。たとえば、このAPIの分析および推奨事項は、構成パラメータAUTO_MV_SCHEMAで指定されたスキーマに制限されます。
パラメータ 説明
STS_OWNER SQLチューニング・セットの所有者の名前。

デフォルト: SYS。

STS_NAME SQLチューニング・セットの名前。

デフォルト: SYS_AUTO_STS。

WORKLOAD_START_TIME ワークロード・ウィンドウの開始時間。

デフォルト: WORKLOAD_END_TIME - 24時間。

WORKLOAD_END_TIME ワークロード・ウィンドウの終了時間。

デフォルト: 現在のタイムスタンプ。
AUTO_MV_MODE REPORT ONLY (推奨のみ)またはIMPLEMENT。

デフォルト: REPORT ONLY

例:

SYS_AUTO_STSを使用して、過去24時間の推奨事項を生成およびレポートします。デフォルトの動作はREPORT_ONLYで、これは自動マテリアライズド・ビューが実装されないことを意味します。 

var exec_name varchar2(200);
begin   
    :exec_name := dbms_auto_mv.recommend();
end;
SELECT * FROM DBA_AUTO_MV_ANALYSIS_RECOMMENDATIONS
    WHERE exec_name = :exec_name;

DBMS_AUTO_MV.REFRESH

DBMS_AUTO_MV.RECOMMEND APIを使用すると、失効したすべての自動マテリアライズド・ビューを強制的にリフレッシュできます。失効した自動マテリアライズド・ビューは、検証されたクエリー・リライトの利点の値に基づいて、無条件で降順にリフレッシュされます。パラメータはありません。このルーチンは、DBAロールを持つユーザーのみが実行できます。 

exec dbms_auto_mv.dbms_auto_refresh();

DBMS_AUTO_MV.REPORT_ACTIVITY

DBMS_AUTO_MV.REPORT_ACTIVITY APIは、指定された時間ウィンドウ内での自動マテリアライズド・ビューのアクティビティおよび使用状況に関するレポートを生成します。レポートはCLOBとして戻されます。

パラメータ 説明
ACTIVITY_START 時間ウィンドウの開始。

デフォルト: SYSTIMESTAMP -1。

ACTIVITY_END 時間ウィンドウの終了。

デフォルト: SYSTIMESTAMP。

TYPE レポートの形式。'TEXT'、'HTML'および'XML'がサポートされています。

デフォルト: 'TEXT'。

SECTION レポートの対象となる1つ以上のセクション。値には、SUMMARY、MV_DETAILS、QUERY_DETAILS、VERIFICATION_DETAILSの任意の組合せまたはALLを使用できます。

デフォルト: 'ALL'。

ノート:

"+"または"-"演算子を使用して、レポートのセクションを含めたり除外する単一文字列を連結します。これは、後述の例のいずれかを参照してください。

LEVEL レポートの詳細レベル: BASIC、TYPICALまたはALL。

デフォルト: 'TYPICAL'。

例:

すべての自動マテリアライズド・ビューのアクティビティに関するレポートを生成します。レポートをHTML形式で出力します。

select dbms_auto_mv.report_activity(type => ‘HTML') from dual;

すべての自動マテリアライズド・ビューのアクティビティに関するレポートを生成します。検証の詳細を除外します。レポートをXML形式で出力します。

select dbms_auto_mv.report_activity(type => ‘XML', section => ‘ALL-VERIFICATION_DETAILS’) from dual;

DBMS_AUTO_MV.REPORT_LAST_ACTIVITY

DBMS_AUTO_MV.REPORT_LAST_ACTIVITY APIは、最新の自動マテリアライズド・ビューのアクティビティおよび使用状況に関するレポートを生成します。

パラメータ 説明
TYPE レポートの形式。'TEXT'、'HTML'および'XML'がサポートされています。

デフォルト: 'TEXT'。

SECTION レポートの対象となる1つ以上のセクション。値には、SUMMARY、MV_DETAILS、QUERY_DETAILS、VERIFICATION_DETAILSの任意の組合せまたはALLを使用できます。

デフォルト: 'ALL'。

ノート:

"+"または"-"演算子を使用して、レポートのセクションを含めたり除外する単一文字列を連結します。次の例を参照してください。

LEVEL レポートの詳細レベル: BASIC、TYPICALまたはALL。

デフォルト: 'TYPICAL'。

例:

最新のアクティビティの包括的なレポートを、標準レベルの詳細で生成します。レポートをテキスト形式(デフォルト)で出力します。これらの文はいずれも同じ結果を返すことに注意してください。

select dbms_auto_mv.report_last_activity('TEXT', 'ALL', ‘TYPICAL’) from dual;
select dbms_auto_mv.report_last_activity() from dual;

マテリアライズド・ビューのサマリーと詳細のみが含まれる最新のアクティビティのレポートを生成します。最大レベルの詳細でレポートします。XML形式で出力します。

select dbms_auto_mv.report_last_activity(‘XML', 'SUMMARY+MV_DETAILS', 'ALL') from dual;

最新のアクティビティのレポートを、基本レベルの詳細で生成します。検証の詳細を除外します。HTML形式で出力します。

select dbms_auto_mv.report_last_activity(‘XML', 'ALL-VERIFICATION_DETAIL', 'BASIC') from dual;

詳細

関連項目:

Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス。

DBMS_ACTIVITYパッケージ

DBMS_ACTIVITY PL/SQLパッケージには、オブジェクト・アクティビティ・トラッキング・システム(OATS)情報の収集および管理を構成するためのファンクションおよびプロシージャが含まれています。OATSによって収集されたデータは、自動マテリアライズド・ビューによって実行される分析で使用されます。

DBAは、DBMS_ACTIVITY.CONFIGUREプロシージャを使用して、特定のデータベース内の3つのOATSパラメータを制御できます。

  • ACTIVITY_INTERVAL

    スナップショット間の間隔。

    exec dbms_activity.configure('ACTIVITY_INTERVAL_MINUTES',30)
  • ACTIVITY_RETENTION_DAYS

    スナップショットの保存期間。

    exec dbms_activity.configure('ACTIVITY_RETENTION_DAYS',60)
  • ACTIVITY_SPACE_PERCENT

    スナップショット用に予約されている使用可能な領域の量。

    exec dbms_activity.configure('ACTIVITY_SPACE_PERCENT',10)

ノート:

OATSは自己管理することが意図されているため、特に、自動マテリアライズド・ビュー機能を使用する場合は、デフォルトの構成をお薦めします。

関連項目:

PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス。