Investigation SQL for Mview and Mlog growth

--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 = 'TABLE'
ORDER BY SNAPTIME DESC;
--row growth every time the mlog updates
select count(*)
from ATD_UK_FP.MLOG$_FP_INET_OPTIONS
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 = 'TABLE'
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 = 'TABLE'
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 < (sysdate-0)
and mowner = 'SCHEMA';

Useful Oracle Metalink Docs

236233.1 – Materialized View Refresh : Log Population and Purge

727632.1 – What to do when a Materialized View Log is not cleared automatically after a Fast Refresh

258634.1 – Materialized View registration at Master Site

Advertisements

3 Responses to Investigation SQL for Mview and Mlog growth

  1. Anand says:

    Thanks for the SQL,

    I have found a unregistered snapshot for one of the master table ,but how to find from where and who is holding the refresh?

    Thanks

    • dbamohsin says:

      I think that will require a bit of business knowledge of your databases.

      As far as I know, there is no easy way to find out where the unregistered snapshot sits. When I had this issue, I found the unregistered snapshot via elimination of places where I knew that mview was refreshing.

      If this unregistered mview is causing issues with snapshot size – then also see SNAPSHOT LOGS GROWS DUE TO MISSING/INVALID SNAPSHOT [ID 1031924.6]

  2. Pingback: Unregister Mview Snapshot Cleanly « Mohsin's DBA Blog

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: