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
Adityanath's Oracle Blog 
Nice Blog, thanks for sharing this kind of information.
Thanks for visiting and appreciate your feedback. 🙂
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
Hello There,
Thanks for visiting and appreciate your feedback.:-)
Regards,
Adi
Great delivery. Great arguments. Keep up the great work.
Hello There,
Thanks for visiting and appreciate your feedback.:-)
Regards,
Adi
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!
Hello There,
Thanks for visiting and appreciate your feedback.:-)
Regards,
Adi
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!
Hello There,
Thanks for visiting and appreciate your feedback.:-)
Regards,
Adi
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!
Hello There,
Thanks for visiting and appreciate your feedback.:-)
Regards,
Adi
Hi there, I wish for to subscribe for this weblog to obtain latest updates, thus where can i do it
please assist.
Hello There,
Thanks for visiting my blog.
You can use “FOLLOW BLOG VIA EMAIL” option for the same!!!
Regards,
Adi
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.
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!
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.
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 =)
What’s up, just wanted to tell you, I liked this article.
It was helpful. Keep on posting!
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.
Bye
Superb, what a blog it is! This blog presents
helpful data to us, keep it up.
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.
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!
Hi there, You’ve done a great job. I’ll certainly digg it and personally recommend to
my friends. I am confident they will be benefited from this
web site.
Hello There,
Thanks for visiting and appreciate your feedback.:-)
Regards,
Adi
exec DBMS_MVIEW.REFRESH(‘XX_AP_INV2’, method => ‘C’, atomic_refresh => FALSE);
Still, my refresh took an HOUR…
Hello There,
How much time did it take in the past for complete refresh?
Note: “atomic_refresh => FALSE” will only help you to save time taken for deleting old data in mview, insert will take same time as before.
Regards,
Adi
Hey there just wanted to give you a quick heads up. The text in your article seem to
be running off the screen in Firefox. I’m not sure if this is a format issue or something to do with
internet browser compatibility but I thought I’d post to let
you know. The design look great though! Hope you get the issue fixed soon. Kudos
Very soon this web page will be famous amid all blog visitors, due
to it’s pleasant articles
Thanks for finally talking about >Slow Materialized View Complete
Refresh Issues Resolved…. | Adityanath’s Oracle Blog <Liked it!
Hello There,
Thanks for visiting and appreciate your feedback.:-)
Regards,
Adi
Many thanks for the info we were looking for this while we were scanning the internet as well as your site turned up– Many thanks,
I love what you guys tend to be up too. Such clever work and reporting!
Keep up the good works guys I’ve included you guys to
blogroll.
Quality content is the crucial to invite the viewers to go to see the website,
that’s what this web page is providing.
I always was concerned in this subject and stock still am, regards for posting .
Hey There. I found your blog using msn. This is an extremely neatly written article.
I’ll make sure to bookmark it and come back to read more of your helpful information. Thank
you for the post. I will definitely return.
I used to be able to find good advice from your blog articles.
Excellent post. I’m going through some of these issues as well..
Hello There,
Thanks for visiting and appreciate your feedback.:-)
Regards,
Adi
Very nice article. I absolutely appreciate this website.
Continue the good work!
Good web site you’ve got here.. It’s hard to find excellent writing like
yours nowadays. I really appreciate people like you! Take care!!
It’s actually a great and helpful piece of info. I am happy that you simply shared this useful info with us.
Please keep us up to date like this. Thank you for sharing.
Looks realy great! Thanks for the post.
Great article.
I’d like to thank you for the efforts you have put in penning
this blog. I am hoping to see the same high-grade content by you in the future as well.
In truth, your creative writing abilities has inspired
me to get my own site now 😉
Hey very nice blog!
Hi! Someone in my Facebook group shared this site with us so
I came to take a look. I’m definitely enjoying the information. I’m bookmarking and will be tweeting this to my
followers! Wonderful blog and great design.
Hello There. I found your blog using msn. This is a very well written article. I will be sure to bookmark it and come back to read more of your useful info. Thanks for the post. I’ll definitely comeback.
This piece of writing presents clear idea in support of the new visitors of blogging,
that in fact how to do blogging and site-building.
Hello! I simply wish to give you a big thumbs up for your excellent info you have here on this post.
I’ll be coming back to your blog for more soon.
Hi, I check your blog on a regular basis. Your writing style is witty, keep it up!
Hello there, You’ve done an incredible job. I will certainly digg it
and personally suggest to my friends. I’m confident they
will be benefited from this site.
Heya i am for the first time here. I found this board and I find It really useful & it helped me out a
lot. I hope to give something back and help others like you aided me.
Thanks for sharing, this is a fantastic article post.Much thanks again. Really Great.
You made some decent points there. I looked on the web to learn more about the issue and found most individuals will go along with your views on this web site.
Hello I am so grateful I found your website, I really found you by accident, while I was browsing on Google for something
else, Anyhow I am here now and would just like to say thanks
a lot for a marvelous post and a all round thrilling blog (I
also love the theme/design), I don’t have time to go through it all
at the minute but I have book-marked it and also included your RSS feeds,
so when I have time I will be back to read much more, Please do keep
up the superb jo.
I used to be able to find good advice from your content.
It’s actually a cool and useful piece of information. I am satisfied that you simply shared this useful info with us.
Please keep us informed like this. Thanks for sharing.
Awesome! Its genuinely remarkable post, I have got much
clear idea regarding from this post.
Hi there! I simply would like to give you a huge thumbs up for the great information you have right here on this post.
I’ll be returning to your website for more soon.
Thanks for your marvelous posting! I seriously enjoyed reading it,
you could be a great author. I will make sure to
bookmark your blog and will come back later in life.
I want to encourage you to continue your great work,
have a nice morning!