I won't go into much detail here but I did want to mention that if you have any MV's set to Fast refresh, check the associated M$LOG table sizes. If these tables haven't been refreshed in awhile, the M$LOG's will have grown to rather large sizes. I have seen forgotten MV's have a log with 100m rows in it. Of course, if you are still using it, after you refresh the MV, you will have to rebuild the M$LOG to recover any disk space to the Tablespace. For more information, see this previous post.
The other piece I wanted to mention concerns how the MV is being refreshed. If you are noticing high amounts of Redo/Arch activity and suspect a portion of this to be caused by the frequent refreshes of all your MV's - explore the Atomic Refresh = False command for non-Fast (Complete) refreshes.
For example:
SQL> EXEC DBMS_MVIEW.REFRESH('xx_mv', 'C', atomic_refresh=>FALSE);
This command uses a Truncate instead of a Delete - with two obvious benefits: no Redo generated and it is faster with MVs of some size.
"This is also why I do NOT setup a refresh schedule in the mat view creation itself (START WITH/NEXT syntax). Stick with dbms_scheduler to setup refresh schedule, calling dbms_mview.refresh" – tbone Jul 3 at 11:29
"This is also why I do NOT setup a refresh schedule in the mat view creation itself (START WITH/NEXT syntax). Stick with dbms_scheduler to setup refresh schedule, calling dbms_mview.refresh" – tbone Jul 3 at 11:29
No comments:
Post a Comment