Materialized View refresh error generating huge Archive Logs after Database Upgrade to 19c.

Issue:-

A huge amount of archive log generation when materialized view refreshing after database upgrade from 11g to 19c and performance issues while accessing the database.

 

Analysis:-

On checking the issue it was noticed that a database feature naming “MV Refresh stats Collection” was introduced from Oracle Database 12.2.0.1. This is used to monitor refresh performance of Materialized views.

Database collects and stores statistics about materialized view refresh operations, on querying from the database following is the information received, where “Typical” means “Only basic refresh statistics are collected for the refresh operation. This is the default setting.:

 

SQL> select * from DBA_MVREF_STATS_SYS_DEFAULTS;

 

PARAMETER_NAME   VALUE

—————- —————————————-

COLLECTION_LEVEL TYPICAL

RETENTION_PERIOD 31

 

Solution:-

We will disable the collection of statics as:-

SQL> exec DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT(‘COLLECTION_LEVEL’, ‘NONE’);

 

SQL> commit;

also delete the existing stats:-

truncate table mvref$_stats;truncate table mvref$_run_stats;truncate table mvref$_change_stats;truncate table mvref$_stmt_stats;