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