Growing MLOG$
- From: Anthony Ballo <anthony.ballo@xxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 17 Aug 2011 16:44:39 -0700
We have a MLOG$ table that grows to 100m+ rows in a matter of days.
Currently, our DBA is truncating the table. Today it has generated 33m
rows.
From my research, rows in these logs should be deleted when a refresh
occurs on a MV and that a growing MLOG$ is the sign of a MV not
refreshing. (basically true?)
So, I was wondering on how to best troubleshoot and find the MV that
refers to this log. We also have Materialized Views on other systems (via
DBLink) that references this table.
On a side note: We also have a high amount (IMO) of REDO creation which I
think this MLOG$ possibly plays a role in. I've been seeing 35-50 log
switches per hour (peak 108) constantly in the 24hr day. We have 18 groups
of log files and some are 104857600 bytes (100mb) - others are 209715200
(200mb).
Thanks,
Anthony
Oracle 10.2.0.4/Linux
PS. I tried running these scripts but don't see my table listed:
Select
a.OWNER,a.MASTER,a.MVIEW_LAST_REFRESH_TIME,m.OWNER,m.NAME,m.MVIEW_SITE
From all_base_table_mviews a,ALL_REGISTERED_MVIEWS m
Where m.MVIEW_ID=a.MVIEW_ID
And a.MASTER='PRODUCTS'
select MOWNER,MASTER,SNAPSHOT,SNAPID,SNAPTIME,SSCN,USER# from sys.slog$
where mowner='PRODUCTS' and master='PRODUCTS';
SELECT r.NAME snapname, snapid, NVL(r.snapshot_site, 'not registered')
snapsite, snaptime FROM sys.slog$ s, dba_registered_snapshots r
WHERE s.snapid=r.snapshot_id(+) AND mowner LIKE UPPER('&owner') AND
MASTER LIKE UPPER('&table_name');
SELECT * FROM DBA_REGISTERED_MVIEWS
--
//www.freelists.org/webpage/oracle-l
Other related posts:
