Complete Materialized View Refresh Generates Lots Of Archive Logs and Rollback/Undo Activity (Doc ID 413188.1)

In this Document

Symptoms

Cause

Solution

References

---

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.

SYMPTOMS

The issue here is that the database is producing a large amount of UNDO and REDO.  This is evident from the archive log generation and the amount of rollback segment usage.

On closer inspection of the database usage over this time it was noticed that a materialized view and/or materialized view group refresh was occurring.

The MVIEW refresh method in both cases above has been defined as the COMPLETE refresh with the ATOMIC_REFRESH option being default value (TRUE).

CAUSE

The ATOMIC_REFRESH=TRUE and COMPLETE refresh combination will produce UNDO and REDO to enable the MVIEW refresh mechanism to rollback to the original dataset in case of an unexpected refresh process failure.  So the redo generation and Undo usage is expected behaviour.

It is important to note that there are 2 methods to refresh with Transactional consistency (ATOMIC_REFRESH=TRUE)

1.  Refresh a single MVIEW with the COMPLETE option and ATOMIC_REFRESH=TRUE or Default value.

2.  Refresh a refresh group with more than one mview defined in it will always guarantee transactional consistency and do a atomic_refresh=true. 

The refresh procedure is executed as follows: 
Single Mview:  All of the data in the mview is deleted and written to the redo, then data from the master table is inserted into the mview. If  the mview fails to refresh, the whole transaction is rolled back and the mview is unchanged and in a state prior to the refresh. Therefore, when using complete refresh(not fast) this generates more entries into the redo since all the data in the mview is deleted.

Refresh Group: The situation with refreshing a refresh group, is that all the mviews are refreshed in a single transaction, so when one of them fails, the whole transaction has to  roll back and before image data has to be reinstated again.  Using a refresh group does not allow the ATOMIC_REFRESH to be set to FALSE as this will affect the transactional consistency of the group.

The Reason Behind Refreshing the Group in a Single Transaction.

When it is important for materialized views to be transactionally consistent with each other, you can organize them into refresh groups. By refreshing the refresh group, you can ensure that the data in all of the materialized views in the refresh group correspond to the same transactionally consistent point in time. Both read-only and updatable materialized views can be included in a refresh group.

SOLUTION

If the mviews are not dependent and transactional consistency is not required between the set of mviews, you can set ATOMIC_REFRESH=FALSE in dbms_mview.refresh() to refresh each mview in a single transaction. This will truncate the mviews data rather than deleting it, which will save a lot of space in the redo logs. 

The usage of the TRUNCATE command is explained here:

Using the TRUNCATE statement provides a fast, efficient method for deleting all rows from a table (MVIEW Container table). A TRUNCATE statement does not generate any undo information and it commits immediately. It is a DDL statement and cannot be rolled back. 

To Incorporate this into the Mview Refresh:

example:

execute dbms_mview.refresh ('user.mview_test', 'C', atomic_refresh=> FALSE);

To refresh more than one mview at a time we can provide a list in the above command:

execute DBMS_MVIEW.REFRESH('countries_mv,regions_mv,hr.employees_mv','c',atomic_refresh=>FALSE);

REFERENCES

NOTE:553464.1 - Complete Refresh Read Consistency Behavior During Refresh and Complete Refresh Performance as Influenced by the ATOMIC_REFRESH Refresh Parameter
NOTE:365157.1 - Complete Refresh of Single Materialized View Is Doing Delete Instead of Truncate
BUG:4217420 - REFRESH COMPLETE GENERATING A LOT OF ARCHIVED LOGS FOR A NOLOGGING MVIEW
NOTE:334878.1 - Can Redo Generation Be Disabled During Materialized View Refresh