Oracle Materialized Views with Atomic Refresh

If you find yourself supporting a mature DB that makes used of Materialized Views (MV), there are two aspects that deserve some investigation on your system: Refresh Types and Atomic Refresh

I won't go into much detail here but I did want to mention that if you have any MV's set to Fast refresh, check the associated M$LOG table sizes. If these tables haven't been refreshed in awhile, the M$LOG's will have grown to rather large sizes. I have seen forgotten MV's have a log with 100m rows in it. Of course, if you are still using it, after you refresh the MV, you will have to rebuild the M$LOG to recover any disk space to the Tablespace. For more information, see this previous post.

The other piece I wanted to mention concerns how the MV is being refreshed. If you are noticing high amounts of Redo/Arch activity and suspect a portion of this to be caused by the frequent refreshes of all your MV's - explore the Atomic Refresh = False command for non-Fast (Complete) refreshes.

For example: 

SQL> EXEC DBMS_MVIEW.REFRESH('xx_mv', 'C', atomic_refresh=>FALSE);

This command uses a Truncate instead of a Delete - with two obvious benefits: no Redo generated and it is faster with MVs of some size.

"This is also why I do NOT setup a refresh schedule in the mat view creation itself (START WITH/NEXT syntax). Stick with dbms_scheduler to setup refresh schedule, calling dbms_mview.refresh" – tbone Jul 3 at 11:29