By on ( 89 Comments )

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.

Advantages of ATOMIC_REFRESH => FLASE

  • 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

Usage:

EXEC DBMS_MVIEW.REFRESH(‘MVIEW1’, METHOD => ‘C’, ATOMIC_REFRESH => FALSE);

Hope so this post is useful for you 🙂

Cheers

Regards,

Adityanath

Categories: Administration

Tagged as: , ,

89 replies ›

  2. 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.

    Reply

  3. 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

    Reply

  7. Wow! After all I got a website from where I can actually obtain valuable data
    concerning my study and knowledge.

    Reply

  8. This piece of writing provides clear idea designed for the new users of blogging, that really how to do running a blog.

    Reply

  9. Awesome issues here. I am very glad to look your article.
    Thanks so much and I’m having a look forward to touch you.
    Will you kindly drop me a e-mail?

    Reply

  10. I blog quite often and I genuinely thank you for your content.
    This article has really peaked my interest. I am going to
    bookmark your site and keep checking for new information about once a week.
    I opted in for your RSS feed as well.

    Reply

  11. Hi there, all is going sound here and ofcourse every one is sharing information, that’s in fact excellent, keep up writing.

    Reply

  12. Hi there, I found your blog via Google at the same time as looking for a related matter, your web site got here up, it appears to be like great. I’ve bookmarked it in my google bookmarks.

    Reply

  13. *Oh my goodness! an amazing article dude. Thank you However I am experiencing issue with ur rss . Don’t know why Unable to subscribe to it. Is there anyone getting identical rss problem? Anyone who knows kindly respond. Thnkx

    Reply

  14. Very nice post. I simply stumbled upon your blog and wanted to say that I have truly enjoyed surfing around your weblog posts. In any case I will be subscribing on your feed and I’m hoping you write again very soon!

    Reply

Leave a reply to nba即時比分 Cancel reply