Re: Growing MLOG$

  • From: Kellyn Pot'vin <kellyn.potvin@xxxxxxxxx>
  • To: "nupendra@xxxxxxxxxxx" <nupendra@xxxxxxxxxxx>, "anthony.ballo@xxxxxxxxxxx" <anthony.ballo@xxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 18 Aug 2011 13:45:52 -0700 (PDT)

Just another recommendation-  look at the actual SQL for the mview refresh.  Is 
the SQL so complex that it could be delaying the refresh times and causing part 
of the issue?  I have always pushed to simplify the queries as much as possible 
utilized for the refreshes and keep complex code in the reporting side against 
the mviews vs. the source.  Often a little tuning can go a long way when you 
are experiencing problems with mview refreshes/extensive mview log sizes.
Kellyn Pot'Vin
Sr. Database Administrator and Developer

From: Upendra N <nupendra@xxxxxxxxxxx>
To: anthony.ballo@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Sent: Wednesday, August 17, 2011 7:41 PM
Subject: RE: Growing MLOG$

>>  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

> From: anthony.ballo@xxxxxxxxxxx
> To: oracle-l@xxxxxxxxxxxxx
> 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
> PS. I tried running these scripts but don't see my table listed:
> Select
> From all_base_table_mviews a,ALL_REGISTERED_MVIEWS m
> 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');
> --

Other related posts: