Oracle 物化视图定时全量刷新导致归档日志骤增

一、问题描述
　　2011年3月28日，某项目组来电，说有一个源表约2万多条的物化视图，每5分钟定时全量(Complete)刷新一次，一天下来，导致Oracle数据库归档日志骤增。

二、问题分析及解决
　　先明确一个问题：归档日志(Archive Log)和重做日志(REDO Log)的关系。
　　Oracle的重做日志是一组（或几组）文件，按一定的规则顺序循环写，当重做日志写满后，从头开始写之前，如果数据库在归档模式(Archive)，则在重写之前，需要把当前的重做日志进行归档(Archive)，形成归档日志。即归档日志来自于重做日志。
　　基于此，可以通过减少产生重做日志的量来达到减少归档日志量的目的。

　　综合一下：
　　1、不要全量刷新，采用在源表上记录物化视图日志的方式，实现快速刷新，减少更新的数据量，达到减少重做日志的目的；
　　2、指定物化视图为nologging模式
　　3、减少或取消其上的索引（2W条记录，如果使用得比较频繁，甚至可以考虑把它cache到内存中）
　　4、如果一定要有索引，自己写刷新的Job，先disable索引，然后刷新，然后重建索引（唯一索引可能有问题）。
　　5、评估业务、技术要求，考虑取消物化视图，建立一般视图，在访问该视图时，直接从源表中查询。

三、验证过程
　　验证全量刷新的物化视图产生的REDO日志的大小：
-- 建立源表
create table big_table as select * from dba_objects;

-- 我机器上（11g），大概8W条记录
select count(*) from big_table;

/*
  开始验证全量刷新产生的REDO日志的量
*/
-- 建立物化视图
create materialized view big_table_mv as select * from big_table;

-- 查看目前REDO日志的量（重新启动数据库会自动清理）
-- 记录下数值，用于接下来的比较
select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
--243964

-- 手工全量刷新物化视图
begin
  dbms_mview.refresh( 'BIG_TABLE_MV', 'C' );
end;

-- 再查看REDO日志的量，比较一下
-- 记录下数值，用于接下来的比较
select a.name, b.value, to_char( b.value-&V, '999999999999' ) diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
--value:38845196
--diff:38601232，增加了约37M

-- 还是比较可观的

-- 把物化视图改为nologging模式
alter table big_table_mv nologging;

-- 再全量刷新
begin
  dbms_mview.refresh( 'BIG_TABLE_MV', 'C' );
end;

-- 再查看REDO日志的量，比较一下
-- 记录下数值，用于接下来的比较
select a.name, b.value, to_char( b.value-&V, '999999999999' ) diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
--value:77495608
--diff:38894376，增加了约37M，全量刷新时，指定nologging没有什么效果喔。

-- 给物化视图建立索引
create index big_table_mv_idx on big_table_mv(owner,object_type,object_name);

-- 全量刷新
begin
  dbms_mview.refresh( 'BIG_TABLE_MV', 'C' );
end;

-- 再查看REDO日志的量，比较一下
select a.name, b.value, to_char( b.value-&V, '999999999999' ) diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
--value:166458592
--diff:127564216，增加了约121M，索引的影响还是比较大的。

-- 清理
drop materialized view big_table_mv;
drop table big_table purge;

　　可以发现：
　　1、全量刷新时，将产生较多的REDO，以上面的情况为例，如果该物化视图每5分钟刷新一次，则全天将产生约10656M（约10G，以不带索引，37M计算）的归档日志数据。
　　2、当该物化视图上有索引时，归档日志的数据将更大。

　　接下来再做一个验证，相同数据量，记录物化视图日志，快速刷新，每10秒更新源表中的数条记录。
　　重启数据库，清理REDO。
　　验证快速刷新的物化视图产生的REDO日志的大小：
-- 建立源表
create table big_table as select * from dba_objects;

-- 我机器上（11g），大概8W条记录
select count(*) from big_table;

-- 建立物化视图日志
create materialized view log on big_table with rowid including new values;

/*
  开始验证快速刷新产生的REDO日志的量
*/
-- 建立物化视图，每10秒刷新一次
create materialized view big_table_mv nologging
refresh fast on demand
with rowid
START WITH TO_DATE('18-03-2011 10:09:08', 'DD-MM-YYYY HH24:MI:SS') NEXT SYSDATE + 1 / 8640 
as select * from big_table;

-- 查看目前REDO日志的量（重新启动数据库会自动清理）
-- 记录下数值，用于接下来的比较
select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
--305808

select count(*) from big_table t where t.owner = 'EPAPRD';
--937

-- 每隔2秒钟，删除并重新插入1000条记录，执行100次吧。
-- 共删除10万条，插入10万条。
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;

-- 再查看REDO日志的量，比较一下
-- 记录下数值，用于接下来的比较
select a.name, b.value, to_char( b.value-&V, '999999999999' ) diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
--value:126422824
--diff:126117016，大约121M。

-- 给物化视图建立索引
create index big_table_mv_idx on big_table_mv(owner,object_type,object_name);

-- 每隔2秒钟，删除并重新插入1000条记录，执行100次
-- 共删除10万条，插入10万条。
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;

-- 再查看REDO日志的量，比较一下
select a.name, b.value, to_char( b.value-&V, '999999999999' ) diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
--value:252701192
--diff:126584176，大约121M。
-- 为什么有索引和没有索引的REDO大小几乎没有差别？删除、插入数据时，都是要维护索引的呀。

-- 清理
drop materialized view big_table_mv;
drop table big_table purge;

　　可以看出，快速刷新模式下，刷新100次，每次删除1000条、插入1000条记录，产生的REDO日志大小与1次全量刷新的大小小了很多。而且在生产环境中，想来不太可能有如果之多的操作，实际产生的REDO日志会更小。

四、参考
　　1、AskTom:http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:15695764787749
　　2、物化视图解决方案.doc