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

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

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

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

ノート:

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

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

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

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

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

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

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

データベースには、メンテナンスのコストを大幅に上回るメリットを持つ自動マテリアライズド・ビューのみが実装されます。わずかな利益を提供するものは実装していません。

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

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

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

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

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

13.3 自動マテリアライズド・ビューと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によって最近取得されたマテリアライズド・ビュー・アクティビティ・スナップショットについて説明します。

13.4 DBMS_AUTO_MVパッケージ

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

DBMS_AUTO_MV.CONFIGURE

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

表13-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`)、自動マテリアライズド・ビューは、ビューの親オブジェクトの表領域(ビューの定義で最大のFACT表)に作成されます。この場合、`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`	レポートの対象となるセクション。値には、`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`	レポートの対象となるセクション。値には、`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;

詳細

13.5 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は自己管理を目的としており、特に自動マテリアライズド・ビュー機能を使用する場合は、デフォルトの構成をお薦めします。