The regular full refresh of Oracle materialized view causes the burst of archived logs

I. Problem description a project team calls and says there is a materialized view with more than 20 thousand entries in the source table, which is refreshed every 5 minutes, this causes a sudden increase in Oracle Database archive logs. Ii. Problem Analysis and Solution first, clarify the relationship between Archive Log and REDO Log. Oracle redo logs are a set of (or several groups) files that are written cyclically in a certain order. When the redo logs are fully written, they are written from the beginning, if the database is in Archive mode, you need to Archive the current redo logs before rewriting to form Archive logs. Archive logs are from redo logs. Based on this, you can reduce the amount of redo logs generated to reduce the amount of archived logs. Overall: 1. Do not refresh the logs in full. Record the materialized view logs in the source table to quickly refresh the logs, reduce the volume of updated data, and reduce the number of redo logs; 2. Specify the materialized view as the nologging mode. 3. reduce or cancel the index on the view (records, if used frequently, you can even cache it To the memory) 4. If you must have an index, write the refresh Job, disable the index first, refresh it, and then re-create the index (the unique index may be faulty ). 5. Evaluate the business and technical requirements. consider canceling the materialized view and creating a general view. when accessing this view, you can directly query the view from the source table. 3. The verification process verifies the size of the REDO log generated by the fully refreshed Materialized View: -- create source table create table big_table as select * from dba_objects; -- on my machine (11 GB ), select count (*) from big_table;/* start to verify the amount of REDO logs generated by full refresh */-- create materialized view big_table_mv as select * from big_table; -- view the current REDO log volume (automatically cleared after the database is restarted) -- Record the value for comparison with select. name, B. value from v $ statname a, v $ mystat B where. statistic # = B. statistic # and. name = 'red' O size '; -- 243964 -- manual full refresh of the materialized view begin dbms_mview.refresh ('Big _ TABLE_MV', 'C'); end; -- view the REDO log volume, compare -- Record the value for the next comparison of select. name, B. value, to_char (B. value-& V, '000000') diff from v $ statname a, v $ mystat B where. statistic # = B. statistic # and. name = 'redo size'; -- value: 38845196 -- diff: 38601232, increased by about 37 M -- more impressive -- changed the Materialized View to the nologging mode alter table big_table_mv nologging; -- refresh B in full Egin dbms_mview.refresh ('Big _ TABLE_MV ', 'C'); end; -- view the REDO log volume and compare it to record the value for comparison with select. name, B. value, to_char (B. value-& V, '000000') diff from v $ statname a, v $ mystat B where. statistic # = B. statistic # and. name = 'redo size'; -- value: 77495608 -- diff: 38894376, with an increase of about 37 M. During full refresh, specifying nologging has no effect. -- Create index big_table_mv_idx on big_table_mv (owner, object_type, object_name) for the materialized view; -- full refresh begin dbms_mview.refresh ('Big _ TABLE_MV ', 'C'); end; -- check the REDO log volume and compare select. name, B. value, to_char (B. value-& V, '000000') diff from v $ statname a, v $ mystat B where. statistic # = B. statistic # and. name = 'redo size'; -- value: 166458592 -- diff: 127564216, with an increase of about 121 M, the index impact is still relatively large. -- Clear drop materialized view big_table_mv; drop table big_table purge; it can be found that: 1. During full refresh, a large number of REDO operations will be generated. The above situations are used as an example, if the materialized view is refreshed every 5 minutes, archive log data of about 10656 MB (about 10 Gb, with no index, 37 MB) will be generated throughout the day. 2. When the materialized view has an index, the archived log data will be larger. Next, perform another verification. Record the materialized view logs with the same data volume and refresh them quickly. Several records in the source table are updated every 10 seconds. Restart the database and clear the REDO. Verify the REDO log size generated by the materialized view that is refreshed quickly: -- create the source table create table big_table as select * from dba_objects; -- on my machine (11 GB ), select count (*) from big_table; -- create materialized view log on big_table with rowid including new values; /* start to verify the amount of REDO logs generated by quick refresh */-- create a materialized view, create materialized view big_table_mv nologging refresh fast on demand with rowid start with TO_DATE ('18-03-2011 10:09:08 ', 'dd-M M-YYYY HH24: MI: ss') next sysdate + 1/8640 as select * from big_table; -- view the current amount of REDO logs (automatically cleaned up when the database is restarted) -- Record the values, used to compare select. name, B. value from v $ statname a, v $ mystat B where. statistic # = B. statistic # and. name = 'redo size'; -- 305808 select count (*) from big_table t where t. owner = 'epaprd '; -- 937 -- delete and re-insert 1000 records every 2 seconds. Run the command 100 times. -- Delete 0.1 million and insert 0.1 million. Declare I integer; begin I: = 1; loop delete from big_table t where t. owner = 'epaprd '; insert into big_table select * from dba_objects o where o. OWNER = 'epaprd '; commit; dbms_lock.sleep (2); I: = I + 1; exit when I> 100; end loop; end; -- view the number of REDO logs, compare -- Record the value for the next comparison of select. name, B. value, to_char (B. value-& V, '000000') diff from v $ statname a, v $ mystat B where. statistic # = B. stat Istic # and a. name = 'redo size'; -- value: 126422824 -- diff: 126117016, about 121 M. -- Create index big_table_mv_idx on big_table_mv (owner, object_type, object_name) for the materialized view; -- delete and re-insert 1000 records every 2 seconds, run the command for 100 times -- delete 0.1 million records and insert 0.1 million records. Declare I integer; begin I: = 1; loop delete from big_table t where t. owner = 'epaprd '; insert into big_table select * from dba_objects o where o. OWNER = 'epaprd '; commit; dbms_lock.sleep (2); I: = I + 1; exit when I> 100; end loop; end; -- view the number of REDO logs, compare select. name, B. value, to_char (B. value-& V, '000000') diff from v $ statname a, v $ mystat B where. statistic # = B. statistic # and. name = 'Redo size'; -- value: 252701192 -- diff: 126584176, about 121 M. -- Why is there almost no difference in the size of REDO with and without indexes? Indexes must be maintained when data is deleted or inserted. -- Clear drop materialized view big_table_mv; drop table big_table purge; you can see that in the quick refresh mode, refresh 100 times, delete 1000 records each time, and insert 1000 records, the size of the generated REDO logs is much smaller than that of the one-time full refresh. In the production environment, it is unlikely that many operations will actually generate fewer REDO logs. Author: yujj_cn