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
Like!! Really appreciate you sharing this blog post.Really thank you! Keep writing.
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.
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
I believe this website has got some rattling wonderful info for everyone : D.
Good post. I will be dealing with a few of these issues as well..
It’s remarkable for me to have a web site, which is beneficial
for my knowledge. thanks admin
my blog :: zipper wallet insert for chic sparrow waypoint