Monitor number of now changed for mview base table

One programer has interested on number of rows changed for a mview base table, thus he suggested to query x$knstmvr table , which may be found in http://avdeo.com/tag/materialized-view/.  As such, I have concern on accessing to the SYS x$ view.

Initially, I thought to use DBA_TAB_MODIFICATIONS, but it requires to run dbms_stats.flush_database_monitoring_info and gather table statistics of rest the counter, and time co-related to mview refresh.


 For performance reasons, the Oracle Database does not populate this view immediately when the actual modifications occur. Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DIMS_STATS PL/SQL package to populate this view with the latest information. 


Confirmed from https://docs.oracle.com/cd/E26401_01/doc.122/e48791/T578797T589127.htm#T578863, that count on MLOG$_ is an easier way to achieve the purpose. 

MLOG$ Table

An MLOG$ table is the Snapshot Log table created against a base table. A row is inserted into the table each time a change (insert/update/delete) is made to the base table.

When a snapshot is refreshed in fast (incremental) mode, it reads the rows in the MLOG$ table and uses this information to get the updated information from the base table into the snapshot for further processing.

Standard RDBMS functionality manages rows inserted into the MLOG tables. When a snapshot is refreshed in either fast or complete mode, then the RDBMS will delete rows from the MLOG table. However, if multiple snapshots reference the same MLOG, then all the snapshots must be refreshed before the RDBMS can remove any rows from the MLOG table.

The RDBMS creates snapshot logs on the base table and automatically names them using MLOG$_[20 characters of the table name]. For example:

MRP_FORECAST_DATES (18 char) creates MLOG$_MRP_FORECAST_DATES BOM_DEPARTMENT_RESOURCES (24 char) creates MLOG$_BOM_DEPARTMENT_RESOU

In case of a tie, the RDBMS adds a number to the MLOG name. For example:

WSM_COPY_OP_RESOURCES creates MLOG$_WSM_COPY_OP_RESOURCE WSM_COPY_OP_RESOURCE_INSTANCES creates MLOG$_WSM_COPY_OP_RESOURCE1

SQL> select count(*) from mlog$_bonus; 
COUNT(*)
----------0

from http://www.dba-oracle.com/t_mlog_dollar_tables.htm is a real case.

Moreover, questions to programer:
What is the purpose of getting this number?
What actions should be taken if the amount exceeds certain number? What number should be set as threshold?

Thanks for the challenge.

ORA-12034 and Complete Materialized View Refresh Generates Lots Of Archive Logs and Rollback/Undo

1. Refresh group failed with  FAST REFRESH mode .

SQL> exec dbms_refresh.refresh('"ARBOR"."ARBOR_DAILY_REF"');
BEGIN dbms_refresh.refresh('"ARBOR"."ARBOR_DAILY_REF"'); END;

*
ERROR at line 1:
ORA-12048: error encountered while refreshing materialized view
"ARBOR"."EXTERNAL_ID_EQUIP_MAP"
ORA-12034: materialized view log on "ARBOR"."EXTERNAL_ID_EQUIP_MAP" younger
than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1


2. So I refresh the mview directly,still failed.
 
09:08:41 SQL> exec dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"',atomic_refresh=>false);
BEGIN dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"',atomic_refresh=>false); END;

*
ERROR at line 1:
ORA-12034: materialized view log on "ARBOR"."EXTERNAL_ID_EQUIP_MAP" younger
than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1


Elapsed: 00:00:00.55

 3. Than I try complete refresh, 16GB UNDO was not sufficient.

SQL> set time on timing on
11:05:16 SQL> exec dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"','C');
BEGIN dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"','C'); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO'
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1


Elapsed: 03:26:13.56


4. It stops growing until 37GB after I enable autoextensible, but refresh job can't complete after running for 46 hours, and it took anther 6.5 hours for rollback.

14:54:35 SQL> exec dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"','C');

c

BEGIN dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"','C'); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1


Elapsed: 52:26:01.08

As the mview contains 6.3 millions rows.

10:05:38 SQL> select count(*) from "ARBOR"."EXTERNAL_ID_ACCT_MAP";

  COUNT(*)
----------
   6382386



5. Finally I found ATOMIC_REFRESH=>false, and gave it a try. This works in 26 minutes!

09:08:44 SQL> exec dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"','C',atomic_refresh=>false);

PL/SQL procedure successfully completed.

Elapsed: 00:25:12.26
 
11:08:57 SQL> select * from user_mview_refresh_times;

OWNER          NAME                           MASTER_OWNER
-------------- ------------------------------ --------------
MASTER                         LAST_REFRESH
------------------------------ --------------------


ARBOR          EXTERNAL_ID_EQUIP_MAP          ARBOR
EXTERNAL_ID_EQUIP_MAP          29-jun-2012 09:09:06

...
6 rows selected.
 
6. The subsequent FAST REFRESH is also succeed.


11:09:01 SQL> exec dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.99
11:09:28 SQL> exec dbms_refresh.refresh('"ARBOR"."ARBOR_DAILY_REF"');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.75


7. Key timestamp in dictionary views


mview site: 


11:12:43 SQL> select last_refresh from dba_mview_refresh_times where name='EXTERNAL_ID_EQUIP_MAP';

LAST_REFRESH
--------------------
29-jun-2012 11:12:43

master site:

ORACLE1> select CURRENT_SNAPSHOTS from dba_snapshot_logs where MASTER='EXTERNAL_ID_EQUIP_MAP';

CURRENT_SNAPSHOTS
--------------------
29-jun-2012 11:12:43

ORACLE1> select LAST_PURGE_DATE from sys.mlog$ where MASTER='EXTERNAL_ID_EQUIP_MAP';

LAST_PURGE_DATE
--------------------
29-jun-2012 11:12:43
 


8. Findings:



The reason of ORA-12034 is due to both mview and master site are cloned database, and the date cut date of mview site is older than master site, caused the out-of-sync in between mview and mview log.


References:

Complete Materialized View Refresh Generates Lots Of Archive Logs and Rollback/Undo Activity [ID 413188.1]

 http://aprakash.wordpress.com/2010/11/05/mview-complete-refresh-is-it-slow-high-redo-generation-oracle-10g/

http://gavinsoorma.com/2009/07/10g-materialized-view-complete-refresh-using-atomic_refresh/

Mview log is not purged

Today during analyze the growing trend of one tablespace and noticed that the biggest segment is a materialized view log. What a big size ! 7Gb , while the master table is only 21Mb.

After do some research, suspected this could be an orphan mview log from previous upgrade.

Key tables to lookup for investigation are:
master_owner.mlog$_master_table (snaptiem$$)
sys.slog$ (snaptime and snapid)
dba_regestered_mviews(mview_id)
dba_rgroups, dba_refresh_children( for those using refresh group)

Q:When mivew log is purged?
A: Only when all regiested mviews are refreshed, i.e check that all sys.slog$.snaptime are equal or before master_owner.mlog$_master_table.snaptime$$


From this practice, I also learned that if the refresh method is complete, no mview log is required.