Re: Growing MLOG$
Date: Thu, 18 Aug 2011 20:11:42 +0100
Message-ID: <CAOCOAVLKQuCCMLiM79Tjk0MfAXBRWhohoG7bGU9TWT8ZacfmyQ_at_mail.gmail.com>
I have seen a situation similar (although no where near as critical) to this.
Due to issues with snapshot reliability over database restarts (we were still preforming cold backups at the time) a cron job was generated that would delete the snapshot at the 'client' end and recreate it the next morning.
As far as the originating database was concerned, a new consumer was being generated each day, and thus no data was ever removed from the log!
So the fact that you can't find any evidence of the materialised view might mean that it only exists at certain times of the day!
I don't have access to a system with any materialised views curently, so I'm not 100% confident on the mechanism employed, but I suspect that there will be a trigger populating this table!
Checking in all_source for code populating this table might identify a trigger that is causing this.
But I beleive that if you have a materialised view with multiple consumers, and only one is live, no log records will be deleted but there is still the posibility that there could be a live materialised view feeding off this log!
Dave
On Thu, Aug 18, 2011 at 2:41 AM, Upendra N <nupendra_at_hotmail.com> wrote:
> >> PS. I tried running these scripts but don't see my table listed:
>
> If you don't have an Materialized view created on the source table, you
> should drop the MV log. Otherwise you'll see MLOG$ keeps growing, even if
> you manually truncate them it will grow again when you have more new
> transactions modify the table. You can recreate the MV LOG when you are
> ready to create the first Materialized view.
> Hope this helps
> -Upendra
>
>
> > From: anthony.ballo_at_onecall.com
> > To: oracle-l_at_freelists.org
> > Date: Wed, 17 Aug 2011 16:44:39 -0700
> > Subject: Growing MLOG$
> >
> > 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
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 18 2011 - 14:11:42 CDT