Slow Materialized View Complete Refresh Issues Resolved….

I was recently working on tuning – production environment, which had number of materilazed 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 complaning about sudden change in materialed view behaviour, after they upgraded database from 9i to 11g.

After reading Oracle documentation about materialized views I found, the reason for this sudden behavoiur 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 🙂





23 thoughts on “Slow Materialized View Complete Refresh Issues Resolved….

  1. I’m not that much of a online reader to be honest but your blogs really nice, keep it up! I’ll go ahead and bookmark your site to come back later on. Cheers

  2. You actually make it seem so easy with your presentation but I find this topic to be really something that I think I would never understand. It seems too complex and very broad for me. I am looking forward for your next post, I’ll try to get the hang of it!

  3. Great site. Lots of useful information here. I am sending it to some friends
    ans additionally sharing in delicious. And
    certainly, thank you on your effort!

  4. Hey! This post couldn’t be written any better! Reading through this post reminds me of my previous room mate! He always kept chatting about this. I will forward this article to him. Fairly certain he will have a good read. Many thanks for sharing!

  5. Aw, this was an extremely nice post. Taking the time and actual effort
    to generate a great article… but what can I say… I put things off a whole lot and don’t manage
    to get nearly anything done.

  6. That is a really good tip particularly to those new to
    the blogosphere. Short but very accurate info…
    Thank you for sharing this one. A must read article!

  7. Hey there! Someone in my Facebook group shared this
    site with us so I came to check it out. I’m definitely enjoying the information. I’m bookmarking and
    will be tweeting this to my followers! Terrific blog and wonderful design and style.

  8. Wonderful article! That is the kind of info that are meant to be shared around the web.
    Shame on Google for not positioning this submit upper! Come on over and talk over with my web site .
    Thanks =)

  9. I appreciate, result in I found just what I used to be taking a
    look for. You have ended my 4 day long hunt! God Bless you man. Have a nice day.


  10. My relatives always say that I am killing my time here at net,
    except I know I am getting knowledge daily by reading thes good content.

  11. Great post however I was wanting to know if you could write a litte more on this topic?
    I’d be very grateful if you could elaborate a little bit further.
    Many thanks!

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