Automatic Materialized Views in Oracle Database 21c

“It’s supposed to be automatic, but actually you have to push this button.” ― John Brunner

With Oracle Database 21c, there is no button for Automatic Materialized Views – it is fully automatic.

DBA_MVIEWS has a new column call AUTO – that is how a DBA can distinguish the auto MVs from the manual ones. The auto naming convention is something like AUTO_MV$$_H3KBHG7DAH6T5. That is all start with AUTO_MV$$.

The Oracle database automatically collects workload statistics, SQL statements and query execution statistics. Oracle also maintains and purges the history of the workload.

All preconfigured / additionally configured parameters can be viewed from DBA_AUTO_MV_CONFIG:

Here is how automatic materialized views work:

– The database automatically detects and collects workload query execution statistics including buffer-gets, database time, estimated cost, and other statistics: DBA_AUTO_MV_ANALYSIS_EXECUTIONS displays information about analysis and tuning executions, including concurrency, degree of parallelism (DOP) requested by the user and actual DOP upon execution finish, status, associated advisor, and informational or error message.

– Oracle creates candidate materialized views hidden from the database workload and verifies that they will deliver the projected performance benefit by test executing a sample of workload queries in the background: DBA_AUTO_MV_ANALYSIS_RECOMMENDATIONS

Displays recommendations associated with automatic materialized views

– There are provided reports with detailed performance test results and which materialized views have been implemented: DBA_AUTO_MV_ANALYSIS_REPORT reports on analyses and recommendations, including task and execution names, sequence number of the journal entry, and message entry in the journal

– Automatic materialized view refresh is also automatic: DBA_AUTO_MV_REFRESH_HISTORY displays the owner name, view name, date, start and end time, elapsed time, status, and error number (if an error occurred) for each automatic materialized view refresh

Automatic MVs are off by default. As DBAs, we can use the CONFIGURE procedure of the DBMS_AUTO_MV package to configure automatic materialized views creation in the database. The AUTO_MV_MODE parameter enables (IMPLEMENT) or disables (OFF) automatic materialized views, or engages report-only mode (REPORT ONLY). The AUTO_MV_MAINT_TASK parameter activates or deactivates the task performing the maintenance (refreshes, validations, and clean up).

Let us enable it all:

Oracle Database 21c includes data dictionary views that display information about automatic materialized views as well as OATS (Object Activity Tracking System). DBAs can use the DBMS_ACTIVITY.CONFIGURE procedure to control the three OATS parameters within a specific database.

ACTIVITY_INTERVAL defines the interval between snapshots:

exec dbms_activity.configure('ACTIVITY_INTERVAL_MINUTES','30');

ACTIVITY_RETENTION_DAYS defines how long snapshots are saved:

exec dbms_activity.configure('ACTIVITY_RETENTION_DAYS','60');

ACTIVITY_SPACE_PERCENT sets how much of available space is reserved for snapshots:

exec dbms_activity.configure('ACTIVITY_SPACE_PERCENT','10');

You mostly likely will get though the following error, even alter system set “_exadata_feature_on”=true scope=spfile; and restart:

ERROR at line 1:

ORA-40216: feature not supported

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.DBMS_ACTIVITY", line 278

ORA-06512: at "SYS.DBMS_ACTIVITY", line 314

ORA-06512: at line 1

The feature is only available on Exadata and ExaCs.

Here are some additional details:

– Automatic materialized views support partitioned and non-partitioned base tables

– Incremental materialized view refresh is supported

– If partitioned base tables use either range, list, or composite partitioning, then they are eligible for Partition Change Tracking (PCT) view refresh

– If there is performance advantage, the automatic materialized view recommendations will include a partitioned automatic materialized view based on the partitioning of the base table of the materialized view and the partitioning type supported is auto-list partitioning, which will mirror the partitioning of the fact table

– The automatic materialized view maintenance module decides the type of refresh that is the most beneficial at the time of refresh, and will decide during run time whether to switch from incremental refresh to full refresh

– DBAs can drop automatic materialized views using the dbms_auto_mv.drop_auto_mv procedure

Source: juliandontcheff.wordpress.com