Mview Log – Size Increasing !!!!!

Recently while checking the segment’s size for a particular schema, i noticed a materialized view log (MLOG$_TABLE_NAME) occupying almost 10Gb of space.

What is materialized view log? From the Oracle document :-

When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must reexecute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.

A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.

Coming back to issue,

17:04:33 SQL> @segment_info
Enter value for segment_name: MLOG$_CONS_DET

OWNER      SEGMENT_NAME              SEGMENT_TYPE       TABLESPACE_NAME              MB                                                                                                                 
---------- ------------------------- ------------------ -------------------- ----------                                                                                                                 
ABC      MLOG$_CONS_DET      TABLE              USER                        9868                                                                                                                 

The mview log is approximately 9.6Gb, which is too big and occupying lots of space.It was time to find out the reason why the mview log was not getting purged??

sql >select LOG_OWNER,MASTER,LOG_TABLE,LOG_TRIGGER,PRIMARY_KEY from dba_mview_logs where log_owner='ABC' and MASTER='CONS_DET'

LOG_OWNER                      MASTER                         LOG_TABLE                      LOG_TRIGGER                      PRI                                                           
------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------
ABC                         CONS_DET                        MLOG$_CONS_DET                                                     YES         

17:09:33 SQL> select MOWNER,MASTER,SNAPSHOT,SNAPID,SNAPTIME,SSCN,USER# from sys.slog$ where mowner='ABC' and master='CONS_DET';

MOWNER                         MASTER                         SNAPSHOT              SNAPID SNAPTIME                SSCN      USER#                                                                      
------------------------------ ------------------------------ ----------------- ---------- ----------------- ---------- ----------                                                                      
ABC                                  CONS_DET                                        442 13-06-08 00:16:32                                                                                            
ABC                                  CONS_DET                                          500 06-08-10 00:55:17                                                                             

The SYS.SLOG$ shows two entries for the master table CONS_DET.Oracle keeps track of snapshots that are using entries in SYS.SLOG$ at the master site. In fact, when deciding whether to purge snapshot log records, Oracle compares SYS.SLOG$.SNAPTIME for the table with MLOG$_.SNAPTIME$$. The rows with a MLOG$_.SNAPTIME$$ equal to or older than the oldest SYS.SLOG$.SNAPTIME for the table are purged from the log. If an orphan entry exists in SYS.SLOG$ at the master site for a deleted snapshot, the SNAPTIME in SLOG$ will not be updated. Consequently, any records in the snapshot log will never be purged during a refresh.

The below query was useful in identifying situations where a snapshot entry exists in SLOG$ but is not registered and has not been updated in a long time.

17:09:46 SQL> @mview_reg_info
Enter value for owner_name: ABC
Enter value for table_name: CONS_DET

SNAPNAME                       SNAPID SNAPSITE                       SNAPTIME                                                                                                                           
------------------------------ ------ ------------------------------ ------------------------------                                                                                                     
                                  442   not registered                 13-JUN-08 00:16:32                                                                                                                 
CONS_DET                          500    SMI                           06-AUG-10 00:55:17      

The script used is -
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');                                                                                           

An un-registered snapshot entry in the SYS.SLOG$ for CONS_DET table with last snaptime of “13-06-08 00:16:32” is preventing the mview log from getting purged.

To purge the mview log my plan was :-

1. Drop the mview log on the master table.
2. Re-create the mview log on the master table.
3. Complete refresh of the mview on the destination site.

17:11:09 SQL> DROP MATERIALIZED VIEW LOG ON ABC.CONS_DET;

Materialized view log dropped.

Elapsed: 00:00:02.28
17:11:16 SQL> @mview_reg_info
Enter value for owner_name: ABC
Enter value for table_name: CONS_DET

no rows selected

17:11:24 SQL> select MOWNER,MASTER,SNAPSHOT,SNAPID,SNAPTIME,SSCN,USER# from sys.slog$ where mowner='ABC' and master='CONS_DET';

no rows selected
17:11:28 SQL> CREATE MATERIALIZED VIEW LOG ON ABC.CONS_DET
17:12:43   2  TABLESPACE USR
17:12:43   3  PCTUSED    30
17:12:43   4  PCTFREE    60
17:12:43   5  INITRANS   20
17:12:43   6  MAXTRANS   255
17:12:43   7  NOCACHE
17:12:43   8  LOGGING
17:12:43   9  NOPARALLEL
17:12:43  10  WITH PRIMARY KEY
17:12:43  11  EXCLUDING NEW VALUES;

Materialized view log created.

Elapsed: 00:00:00.39
17:12:44 SQL> 
17:12:45 SQL> 
17:12:45 SQL> 
17:12:45 SQL> @mview_reg_info
Enter value for owner_name: ABC
Enter value for table_name: CONS_DET

no rows selected

Elapsed: 00:00:00.01
17:12:56 SQL> select MOWNER,MASTER,SNAPSHOT,SNAPID,SNAPTIME,SSCN,USER# from sys.slog$ where mowner='ABC' and master='CONS_DET';

no rows selected

Elapsed: 00:00:00.00
17:13:07 SQL> 
17:13:53 SQL> @segment_info
Enter value for segment_name: MLOG$_CONS_DET

OWNER      SEGMENT_NAME              SEGMENT_TYPE       TABLESPACE_NAME              MB                                                                                                                 
---------- ------------------------- ------------------ -------------------- ----------                                                                                                                 
ABC        MLOG$_CONS_DET            TABLE               USR                       .0625                                                                                                                 

After creating the mview log on the master table (source database), started the complete refresh of the materialized view on target database.

exec dbms_mview.refresh ('CONS_DET','C',parallelism =>4);

During the process checked for the locked objects

18:38:59 SQL> @lock_objects

     SID USERNAME             OBJECT_OWNER         OBJECT_NAME                    LOCKED_MODE     OS_USER_NAME
-------- -------------------- -------------------- ------------------------------ --------------- ------------------------------
     351 ABC                    ABC                  CONS_DET                       Row-X (SX)      ora
     351 ABC                    SYS                  MLOG$                          Row-X (SX)      ora
     351 ABC                    SYS                  SNAP$                          Row-X (SX)      ora
     351 ABC                    SYS                  SNAP_REFOP$                    Row-X (SX)      ora

SNAP_REFOP$ can be used to check the steps that oracle internally performs to complete the operation.

select VNAME,OPERATION#,SQL_TXT from  SNAP_REFOP$ where SOWNER='ABC' and VNAME='CONS_DET';

Once, the complete refresh was over, checked for the entries in SYS.SLOG$

21:17:32 SQL> select MOWNER,MASTER,SNAPSHOT,SNAPID,SNAPTIME,SSCN,USER# from sys.slog$ where mowner='ABC' and master='CONS_DET';

MOWNER                         MASTER                         SNAPSHOT      SNAPID SNAPTIME        SSCN      USER#
------------------------------ ------------------------------ --------- ---------- --------- ---------- ----------
ABC                             CONS_DET                                      500 06-AUG-10

Advertisements

6 thoughts on “Mview Log – Size Increasing !!!!!

  1. Hello,
    very helpful information – and practical explanation how to resolve the issue.

    Is there any possibility to check what (program/application) accessing the MLOG tables?

    Thanks

    1. Below sql should give you the access details. You can modify as per your need.

      select distinct sp.sql_id,
      sp.plan_hash_value,
      sa.BUFFER_GETS,
      sa.EXECUTIONS,
      decode (sa.command_type, 2, ‘INSERT’,
      3, ‘SELECT’,
      6, ‘UPDATE’,
      7, ‘DELETE’) COMMAND_NAME,
      sa.LAST_ACTIVE_TIME
      from v$sql_plan sp, v$sqlarea sa
      where sp.sql_id = sa.sql_id and
      sp.plan_hash_value = sa.plan_hash_value and
      sp.object_name =upper(‘&obj_name’);

      Usually MLOG$ is not accessed by any program/application. The dbms_mview refresh access it.

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