As I had noted about 5 years ago "Large (Growing) Snapshot Logs indicate that you have a problem", if you have a Fast Refresh Materialized View that hasn't been refreshed for a considerable period of time, you will find that the Materialized View Log ("Snapshot Log" in earlier versions) would keep growing because it patiently expects the MV to come around for the next refresh. You can end up with very large MV Logs and also performance issues inserting into the base tables as each insert also has to update the MV Logs. Furthermore, other MVs that do refresh from the same base table may also suffer because they have to contend with larger MV Logs.
Here is a demonstration with two users "ANOTHER_USR" and "LAZY_USR" having created their own Materialized Views against my source table. If LAZY_USR does not execute a Refresh of his MV, the Snapshot Log on my source table does not get purge.
Here is a demonstration with two users "ANOTHER_USR" and "LAZY_USR" having created their own Materialized Views against my source table. If LAZY_USR does not execute a Refresh of his MV, the Snapshot Log on my source table does not get purge.
23:39:21 SQL> --- create two users that will be creating MVs
23:39:21 SQL> drop user another_usr cascade;
User dropped.
23:39:21 SQL> create user another_usr identified by another_usr default tablespace users;
User created.
23:39:21 SQL> grant create session, create table, create materialized view to another_usr;
Grant succeeded.
23:39:21 SQL> alter user another_usr quota unlimited on users;
User altered.
23:39:21 SQL>
23:39:21 SQL> drop user lazy_usr cascade;
User dropped.
23:39:22 SQL> create user lazy_usr identified by lazy_usr default tablespace users;
User created.
23:39:22 SQL> grant create session, create table, create materialized view to lazy_usr;
Grant succeeded.
23:39:22 SQL> alter user lazy_usr quota unlimited on users;
User altered.
23:39:22 SQL>
23:39:22 SQL>
23:39:22 SQL> --- create the source table and mv log on it
23:39:22 SQL> drop table my_data_table purge;
Table dropped.
23:39:22 SQL> create table my_data_table
23:39:22 2 as select object_id as ID_Number, object_name as Data_Item, created as Crtn_date
23:39:22 3 from dba_objects where object_id is not null;
Table created.
23:39:22 SQL> alter table my_data_table modify (ID_Number not null);
Table altered.
23:39:22 SQL> alter table my_data_table add constraint my_data_table_pk primary key (ID_Number);
Table altered.
23:39:22 SQL>
23:39:22 SQL> create materialized view log on my_data_table ;
Materialized view log created.
23:39:22 SQL>
23:39:22 SQL> grant select on my_data_table to another_usr;
Grant succeeded.
23:39:22 SQL> grant select on mlog$_my_data_table to another_usr;
Grant succeeded.
23:39:22 SQL> grant select on my_data_table to lazy_usr;
Grant succeeded.
23:39:22 SQL> grant select on mlog$_my_data_table to lazy_usr;
Grant succeeded.
23:39:22 SQL>
23:39:22 SQL> --- create the mv in the another_usr account
23:39:22 SQL> connect another_usr/another_usr
Connected.
23:39:22 SQL> create materialized view my_mv
23:39:22 2 refresh fast on demand
23:39:22 3 as
23:39:22 4 select rowid as row_identifier, id_number, data_item
23:39:22 5 from hemant.my_data_table
23:39:22 6 /
Materialized view created.
23:39:22 SQL>
23:39:22 SQL> --- query the data dictionary
23:39:22 SQL> connect hemant/hemant
Connected.
23:39:22 SQL> col owner format a12
23:39:22 SQL> col name format a12
23:39:22 SQL> col snapshot_site format a12
23:39:22 SQL> col refresh_method format a12
23:39:22 SQL> select name from v$database;
NAME
------------
ORCL
23:39:22 SQL> select v.owner, v.name, v.snapshot_site, v.refresh_method,
23:39:22 2 to_char(l.current_snapshots,'DD-MON HH24:MI:SS')
23:39:22 3 from dba_registered_snapshots v, dba_snapshot_logs l
23:39:22 4 where v.snapshot_id = l.snapshot_id
23:39:22 5 and l.log_owner = 'HEMANT'
23:39:22 6 and l.master = 'MY_DATA_TABLE'
23:39:22 7 order by 1,2
23:39:22 8 /
OWNER NAME SNAPSHOT_SIT REFRESH_METH TO_CHAR(L.CURRENT_SNAPSH
------------ ------------ ------------ ------------ ------------------------
ANOTHER_USR MY_MV ORCL PRIMARY KEY 15-JAN 23:39:23
23:39:22 SQL>
23:39:22 SQL> -- insert into the source table
23:39:22 SQL> insert into my_data_table values (1000000,'A DUMMY',sysdate);
1 row created.
23:39:22 SQL> commit;
Commit complete.
23:39:22 SQL>
23:39:22 SQL> select count(*) from mlog$_my_data_table;
COUNT(*)
----------
1
23:39:22 SQL>
23:39:22 SQL> exec dbms_lock.sleep(30);
PL/SQL procedure successfully completed.
23:39:52 SQL>
23:39:52 SQL> --- check if the MV has been updated ?!
23:39:52 SQL> select v.owner, v.name, v.snapshot_site, v.refresh_method,
23:39:52 2 to_char(l.current_snapshots,'DD-MON HH24:MI:SS')
23:39:52 3 from dba_registered_snapshots v, dba_snapshot_logs l
23:39:52 4 where v.snapshot_id = l.snapshot_id
23:39:52 5 and l.log_owner = 'HEMANT'
23:39:52 6 and l.master = 'MY_DATA_TABLE'
23:39:52 7 order by 1,2
23:39:52 8 /
OWNER NAME SNAPSHOT_SIT REFRESH_METH TO_CHAR(L.CURRENT_SNAPSH
------------ ------------ ------------ ------------ ------------------------
ANOTHER_USR MY_MV ORCL PRIMARY KEY 15-JAN 23:39:23
23:39:52 SQL>
23:39:52 SQL> exec dbms_lock.sleep(10);
PL/SQL procedure successfully completed.
23:40:02 SQL>
23:40:02 SQL> -- refresh the MV
23:40:02 SQL> connect another_usr/another_usr
Connected.
23:40:03 SQL> exec dbms_mview.refresh('MY_MV');
PL/SQL procedure successfully completed.
23:40:03 SQL>
23:40:03 SQL>
23:40:03 SQL> --- re-query the data dictionary
23:40:03 SQL> connect hemant/hemant
Connected.
23:40:03 SQL> select v.owner, v.name, v.snapshot_site, v.refresh_method,
23:40:03 2 to_char(l.current_snapshots,'DD-MON HH24:MI:SS')
23:40:03 3 from dba_registered_snapshots v, dba_snapshot_logs l
23:40:03 4 where v.snapshot_id = l.snapshot_id
23:40:03 5 and l.log_owner = 'HEMANT'
23:40:03 6 and l.master = 'MY_DATA_TABLE'
23:40:03 7 order by 1,2
23:40:03 8 /
OWNER NAME SNAPSHOT_SIT REFRESH_METH TO_CHAR(L.CURRENT_SNAPSH
------------ ------------ ------------ ------------ ------------------------
ANOTHER_USR MY_MV ORCL PRIMARY KEY 15-JAN 23:40:03
23:40:03 SQL> select count(*) from mlog$_my_data_table;
COUNT(*)
----------
0
23:40:03 SQL>
23:40:03 SQL> --- create another mv in the lazy_usr account
23:40:03 SQL> connect lazy_usr/lazy_usr
Connected.
23:40:03 SQL> create materialized view lazy_mv
23:40:03 2 refresh fast on demand
23:40:03 3 as
23:40:03 4 select rowid as row_identifier, id_number, data_item
23:40:03 5 from hemant.my_data_table
23:40:03 6 /
Materialized view created.
23:40:03 SQL>
23:40:03 SQL>
23:40:03 SQL> --- insert new data
23:40:03 SQL> connect hemant/hemant
Connected.
23:40:03 SQL> insert into my_data_table values (2000000,'Another dummy',sysdate);
1 row created.
23:40:03 SQL> commit;
Commit complete.
23:40:03 SQL> select count(*) from mlog$_my_data_table;
COUNT(*)
----------
1
23:40:03 SQL>
23:40:03 SQL> exec dbms_lock.sleep(30);
PL/SQL procedure successfully completed.
23:40:33 SQL>
23:40:33 SQL> -- refresh another_usr's mv only
23:40:33 SQL> connect another_usr/another_usr
Connected.
23:40:33 SQL> exec dbms_mview.refresh('MY_MV');
PL/SQL procedure successfully completed.
23:40:34 SQL>
23:40:34 SQL> --- requery the data dictionary
23:40:34 SQL> connect hemant/hemant
Connected.
23:40:34 SQL> select v.owner, v.name, v.snapshot_site, v.refresh_method,
23:40:34 2 to_char(l.current_snapshots,'DD-MON HH24:MI:SS')
23:40:34 3 from dba_registered_snapshots v, dba_snapshot_logs l
23:40:34 4 where v.snapshot_id = l.snapshot_id
23:40:34 5 and l.log_owner = 'HEMANT'
23:40:34 6 and l.master = 'MY_DATA_TABLE'
23:40:34 7 order by 1,2
23:40:34 8 /
OWNER NAME SNAPSHOT_SIT REFRESH_METH TO_CHAR(L.CURRENT_SNAPSH
------------ ------------ ------------ ------------ ------------------------
ANOTHER_USR MY_MV ORCL PRIMARY KEY 15-JAN 23:40:34
LAZY_USR LAZY_MV ORCL PRIMARY KEY 15-JAN 23:40:04
23:40:34 SQL> select count(*) from mlog$_my_data_table;
COUNT(*)
----------
1
23:40:34 SQL>
My data dictionary query, at the first execution at 23:39:22, showed that ANOTHER_USR had an MV called "MY_MV" against my table MY_DATA_TABLE. Inserting a single row in MY_DATA_TABLE caused a corresponding row to be inserted into the MV Log MLOG$_MY_DATA_TABLE. However, as ANOTHER_USR had not refreshed his MV even as at 23:39:52, the Data Dictionary showed that the MV was still as of 23:39:23.
After ANOTHER_USR did refresh his MV (at 23:40:03), the MV Log MLOG$_MY_DATA_TABLE was purged of the record that was used to track the one row DML against MY_DATA_TABLE.
However, when LAZY_USR created an MV but did not refresh the MV, even as at 23:40:34, the one row in MLOG$_MY_DATA_TABLE (to capture the insert of "(2000000,'Another dummy',sysdate)), was *not* purged. Until LAZY_USR executes a Refresh of his MV, MLOG$_MY_DATA_TABLE will keep growing, even though ANOTHER_USR might be diligently refreshing his MV.
Over time, I will find MLOG$_MY_DATA_TABLE to have grown "excessively large !".
The solution is to identify LAZY_USR (which the query against the Data Dictionary does do) and then :
0 (optional). Drop LAZY_USR's MV "LAZY_MV"
2. Purge MLOG$_MY_DATA_TABLE (using either DBMS_MVIEW.PURGE_LOG or a TRUNCATE)
3. ReCreate or Refersh LAZY_USR's MV "LAZY_MV"
I have seen situations where LAZY_USR is a an account in a "remote" database managed by another application / development / support / DBA team that doesn't bother to inform HEMANT that they are no longer refreshing the MV even as HEMANT's schema continues to grow only to hold entries in MLOG$_MY_DATA_TABLE.
The example I have posted is of 1 source table and 1 row DML. In real-life you may have a dozen such tables and tens of thousands of rows before you notice this (and you would notice it only if you are diligently monitoring all the schemas in your database).
.
.
.
5 comments:
Marvelous work this is a great post for me i m working on Oracle i think your blog will do a great work for me keep sharing such kind of nice info i will keep visiting thanks.
Nice information, I can use this.
But I also have a question. What happens is the table for the Materialized view log can not grow anymore, tablespace completly filled up? Is it still possible to update the master table? (I created my Materialized view logs in an other tablespace than the master table).
If the MV Log cannot grow, transactions cannot be executed against the source table. Updating the MV is a recursive call and part of the transaction that executes DML against the source table. Think of it as a trigger on the source table. If the trigger fails to execute, the calling transaction also has to be failed.
Hemant
Is there any way to find outstanding records for a given MV LOG? I have a MV log and there are multiple snapshot_site so by looking at snaptime i know who did not consume records since when but I want to get counts too.
Thanks
Anonymous,
I guess you could trace the SQL that a Refresh MV executes against the source table and MV Log and reverse engineer to identify the "pending rows" a refresh would read.
But I haven't tried that.
Hemant
Post a Comment