Materialised Views – Mlog Build up Issues

I had an issue whereby the Materialised View Log (MLog) was building up for a table even thought the snapshot sites where receiving an up to date dataset. I managed to find the issue by writing a couple of scripts to diagnose the problem and find out what was holding up the log from purging.

Materialised view logs build up when snapshot sites are requesting FAST refreshes from the source. Until all the registered destinations for a particular Mview have received the log, the purge of the log will not happen. One of the ways around this is to change the refresh mode from FAST to COMPLETE if for example one site is requesting a mview refresh less than the other sites. Important to consider the size of the table being refreshed.

–Find oldest snaptime for a mlog – i.e. time the mlog has been building up since
select * from sys.slog$
where mowner = ‘SCHEMA’
and Master = ‘MVIEW_NAME’
ORDER BY SNAPTIME DESC;

–row growth every time the mlog updates
select count(*)
from ATD_UK_FP.MLOG$_TABLE_NAME
group by SNAPTIME$$;

— SNapshot sites for a particular mview
select owner, name ,snapshot_site, snapshot_id, refresh_method from dba_registered_snapshots rs
WHERE rs.OWNER = ‘SCHEMA’
and rs.Name = ‘MVIEW_NAME’
order by rs.owner, rs.name;

–Find which snapshot_site is holding up the mlog
select sl.*, rs.snapshot_site, rs.owner, rs.name, rs.refresh_method from sys.slog$ sl
left join dba_registered_snapshots rs on rs.SNAPSHOT_ID = sl.SNAPID
where mowner = ‘SCHEMA’
and Master = ‘MVIEW_NAME’
ORDER BY SNAPTIME DESC;

–Find unregistered snaps and snaps older than certain number of days not being refreshed
select mowner, master, snapid,  nvl(r.snapsite, ‘not registered’) snapsite,snaptime
from   sys.slog$ s, sys.reg_snap$ r where  s.snapid=r.snapshot_id(+) and s.snaptime < (sysdate0)
and mowner = ‘SCHEMA’;

–Change the refresh mode for a Mview
ALTER MATERIALIZED VIEW SCHEMA.MVIEW_NAME
 REFRESH COMPLETE
 WITH PRIMARY KEY;

Useful Oracle Metalink Docs

236233.1           Materialized View Refresh : Log Population and Purge
727632.1           What to do When Materialized View Log is not cleared automatically after a Fast Refresh
258634.1           Materialized View registration at Master Site

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: