Lately I faced the following problem - what is faster: manual load or mview refresh. I would not tell - while mview refresh is a direct copy from a source to a destination, the manual load offers full control over the whole process, so we may additionally cut it according to our needs. There is one obstacle though in this particular issue - the final result should be an mview, we may refresh incrementally.
I was not sure if this is possible to run a fast refresh without initial complete refresh - if it would be impossible then the whole concept with the manual load would be useless regarding the circumstances. And during the standard Google search I hit
this article.
In summary it proofs the thing is really fairly easy. I played a little with it and it seems the refresh is really a dumb machanism in this case (which works in our favour). In short it does not check when one initially loaded the pre-built table or which change was last or any SCN or whatever the implementers thought of. It simply assumes a user knows what he/she does, which approach is one of my favorite ones. Thus assuming one have a source table with a primary key defined, a pre-built table initially loaded with (some) data shaped according to one's needs, a mview log on the source table and a mview defined on top of the pre-built table the call to refresh the materialized view looks into the mlog, collects operations stored there and applies them to the destination mview without worrying about the integrity of the data - exactly what I tried to achieve.
Showing posts with label mview. Show all posts
Showing posts with label mview. Show all posts
Monday, 28 April 2014
Monday, 5 August 2013
The orphaned mview registration on a mview log
Some time ago we migrated a certain application between 2 databases. This operation was quite long-term and went in few steps. The replication based upon mviews was involved for the transition period. The one of the final steps was a drop of a few schemas on the source database. So far, so good...
After several months we realized that one of the mview logs on a table from the migrated schemas had become quite large despite the fact that the mview based upon it had been refreshed regularly and that was the only mview bound with this mview log.
What is behind this tale?
It is quite easy to find the solution by googling or at least some clues to what happened with our log - for example on the blog by A. Prakesh or the blog by D.Vadas. Finally on the MOS there are a few articles describing the whole mview refresh mechanism (236233.1, 258634.1) and dealing by the way with this particular issue.
In our particular case the scenario is the following. The schemas drop operation on the source database affected also the mviews, which were registered with the mview logs on the destination database. Such situation is one of the few possible, when there is performed no explicit unregistration of the mview. In the catalog of the destination database it is seen after performing the following query:
So we found the guilty, what's next?
Here is a time for the fix1 column from the query above. The call to the purge_mview_from_log procedure from the dbms_mview package purges all the entries, which are held for a once-in-time existing mviews, which we implicitly dropped with the migrated schemas. The required argument is the snapid column value from s.slog$.
After the purging operation we are left possibly with an enormously grown segment of the mview log, so we need 2 more operations. One is to enable the row movement on the table storing the mview log entries (the system-create name with the pattern MLOG$_{the master table name}) and the next one is the shrink of course, but called this time on the mview log object and not the table (all the issues with shrinking apply .
At least in our case the purge even on small tables enabled high waits on the log sync event (though this was not the case on the test environment), so this is important to do the purge in the period of the low database load - for example at night or for weekend. I suppose the reason behind this is that the purge is done by deleting every entry in the mlog. It seems there is a one commit per purge but at least in our case there were lots of commit cleanouts on the production site.
After several months we realized that one of the mview logs on a table from the migrated schemas had become quite large despite the fact that the mview based upon it had been refreshed regularly and that was the only mview bound with this mview log.
What is behind this tale?
It is quite easy to find the solution by googling or at least some clues to what happened with our log - for example on the blog by A. Prakesh or the blog by D.Vadas. Finally on the MOS there are a few articles describing the whole mview refresh mechanism (236233.1, 258634.1) and dealing by the way with this particular issue.
In our particular case the scenario is the following. The schemas drop operation on the source database affected also the mviews, which were registered with the mview logs on the destination database. Such situation is one of the few possible, when there is performed no explicit unregistration of the mview. In the catalog of the destination database it is seen after performing the following query:
select s.mowner, s.master, s.snaptime, 'exec dbms_mview.purge_mview_from_log('||s.snapid||');' fix1
from sys.slog$ s
where not exists(select 1 from dba_registered_snapshots r where s.snapid=r.snapshot_id)
order by mowner, master;
While there are no more entries in the dba_registered_snapshots view, there still exist entries in the sys.slog$, which indicate the database engine, how much data have to be stored to allow for the fast refresh on the registered mviews. The snaptime value indicates the last fast refresh on the registered mviews. In our case we had 2 entries per mview bound with this issue. One of them was the "healthy" one, which has been refreshed frequently, while the second entry was the one without a corresponding entry in the dba_registered_snapshots view with the snaptime far in the past (which corresponded to the oldest rows in the mview log).So we found the guilty, what's next?
Here is a time for the fix1 column from the query above. The call to the purge_mview_from_log procedure from the dbms_mview package purges all the entries, which are held for a once-in-time existing mviews, which we implicitly dropped with the migrated schemas. The required argument is the snapid column value from s.slog$.
After the purging operation we are left possibly with an enormously grown segment of the mview log, so we need 2 more operations. One is to enable the row movement on the table storing the mview log entries (the system-create name with the pattern MLOG$_{the master table name}) and the next one is the shrink of course, but called this time on the mview log object and not the table (all the issues with shrinking apply .
alter table MLOG$_{the master table name} enable row movement;
alter materialized view log on {the master table name} shrink space [compact];
alter materialized view log on {the master table name} shrink space; -- if previous command with compact
At least in our case the purge even on small tables enabled high waits on the log sync event (though this was not the case on the test environment), so this is important to do the purge in the period of the low database load - for example at night or for weekend. I suppose the reason behind this is that the purge is done by deleting every entry in the mlog. It seems there is a one commit per purge but at least in our case there were lots of commit cleanouts on the production site.
Subscribe to:
Posts (Atom)