频道栏目
首页 > 数据库 > Oracle > 正文
Oracle物化视图定时全量刷新导致归档日志骤增
2012-03-20 08:49:53           
收藏   我要投稿

一、问题描述 

  某项目组来电,说有一个源表约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日志会更小。

 

 

 

 

作者 yujj_cn

点击复制链接 与好友分享!回本站首页
相关TAG标签 视图 日志
上一篇:oracle笛卡尔操作
下一篇:ORACLE RAC环境下的外部表实验
相关文章
图文推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站