Slow Materialized View Complete Refresh Issues Resolved….

I was recently working on tuning – production environment, which had number of materialized views which were scheduled to complete refresh during off hours.

This complete refresh process was very time consuming, also producing a large amount archivelogs & undo.

Customer was complaining about sudden change in materialized view behavior, after they upgraded database from 9i to 11g.

After reading Oracle documentation about materialized views I found, the reason for this sudden behavior change.

From 10g, Oracle has changed the default parameter value of ATOMIC_REFRESH in the DBMS_MVIEW.REFRESH package.

In earlier releases (before 10g) the parameter was set to FALSE by default but now it is set to TRUE, which forces a DELETE of the materialized view instead of TRUNCATE, making the materialized view more “available” at refresh time.


  • No or very less archive generation
  • Complete refresh process is very fast as compared to process with ATOMIC_REFRESH => TRUE

Disadvantages of ATOMIC_REFRESH => FLASE

  • Unavailability of data during the refresh process, which could be unacceptable to application and business user.

After discussing with customer, I made changes in refresh methodology so as to use ATOMIC_REFRESH => FLASE.

          Time Taken          Archive generation
Before    Approx. 43 minutes  Approx. 68 
After     Approx. 6 minutes   Approx. 7



Hope so this post is useful for you 🙂




67 replies

  1. whoah this weblog is wonderful i love reading your articles.

    Stay up the great work! You understand, many individuals are hunting around
    for this information, you could help them greatly.

  2. I’m not that much of a online reader to be honest but your sites really nice, keep it up! I’ll go ahead and bookmark your website to come back later. All the best

Leave a Reply

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

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

Google photo

You are commenting using your Google 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