Materialized View Refresh is very slow in Oracle Database 12.2

Hello,

Starting 12.2 Oracle Database collects and stores statistics about materialized view refresh operations. These statistics are accessible using data dictionary views. Statistics for both current and historical materialized view refresh operations are stored in the database. Historical materialized view refresh statistics enable you to understand and analyze materialized view refresh performance over time in your database. Refresh statistics can be collected at varying levels of granularity.

Back to the problem in trace I saw Oracle executed DML’s and selects on below tables and were performing full table scan.

mvref$_stats;

mvref$_run_stats;

mvref$_change_stats;

mvref$_stmt_stats;

There is a collection level parameter which specifies the collection level for materialized view refresh statistics. 

The values that can be set for the COLLECTION_LEVEL parameter are:

NONE: No statistics are collected for materialized view refresh operations.

TYPICAL : Only basic refresh statistics are collected for materialized view refresh operations. This is the default setting.

ADVANCED : Detailed statistics, including the parameters used in the refresh operation and the SQL statements that are run, are collected for materialized view refresh operations.

So to avoid it better to turn off the collection/tracking level. By default collection level is typical. So as to stop completely we need to execute below statement to turn it off. 

exec dbms_mview_stats.set_system_default('COLLECTION_LEVEL', 'NONE');

select * from DBA_MVREF_STATS_SYS_DEFAULTS;

Advertisement