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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s