13 January, 2007

Large (Growing) Snapshot Logs indicate that you have a problem

Some time ago, we had noticed that in a particular database, a Materialized View's refreshs were taking longer and were occassionally failing on ORA-1555s. We then redefined the MV to extract a smaller subset of data . We knewthat this was supposed to be faster. Yet, it wasn't any faster and the load that the refresh was causing was still high. We realised that the snapshot logs on the base tables hadg rown inordinately large {as a result of the ORA-1555s in the refresh's}. We dropped and recreated the snapshot logs. Thereafter, MV refresh's were also much faster.
When you know that the Query in the MV should be fast and that Fast Refreshs are
getting slow, you should look at the size of the Snapshot Log on the base tables.
It could also be that you had, at some time, defined additional MVs on the same base tables but had stopped refreshing the other MVs. Unfortunately, this would have resulted in the Snapshot Logs continuing to grow because they are still "waiting" for those other MVs to "come over and pick up the modified rows". See Note #1031924.6 on MetaLink.
Note that even if you do drop the non-refreshed MV as advised in the Note, it would still be preferable, if possible, to drop the Snapshot Logs on the base tables -- because you would want to rebuild the Snapshot Log afresh as a small log -- and rebuild other MVs that are still in use. (I say "if possible" because a complete rebuild of some MVs might also take too long to be acceptable "on-line").

No comments: