ITPub博客

首页 > 数据库 > Oracle > 11g中的materialized view log

11g中的materialized view log

原创 Oracle 作者:talio 时间:2014-04-25 16:18:03 0 删除 编辑

从11g版本之前,oracle只支持一种形式的materialized view log,即timestamp-based materialized view log,11g引入了一种新的选择,称为commit SCN-based materialized view log.

本文通过测试比较两种materialized view log的区别,并且分析一个与commit SCN-based materialized view log相关的bug.

timestamp-based materialized view log

  1. show release
  2. release 1002000500
  3.  
  4. create table test_mv_log
  5. (
  6. id number,
  7. name varchar2(10),
  8. status varchar(10)
  9. );
  10.  
  11. alter table test_mv_log add constraint pk_test_mv_log primary key(id);
  12.  
  13. create materialized view log on test_mv_log;
  14.  
  15. create materialized view test_mv_log_mv refresh fast as select * from test_mv_log where status=\'Y\';

这里创建的是传统的timestamp-based materialized view log. 我们知道,mv log是用来支持快速刷新的,我们来看看这种mv log下快速刷新的具体实现方式:

  1. insert into test_mv_log values(1,\'a\',\'Y\');
  2. commit;
  3.  
  4. SQL> select * from mlog$_test_mv_log;
  5.         ID SNAPTIME$$ D O CHANGE_VEC
  6. ---------- ----------------- - - ----------
  7.          1 40000101 00:00:00 I N FE
  8.  
  9. alter session set events \'10046 trace name context forever,level 12\';
  10. exec dbms_mview.refresh(\'TEST_MV_LOG_MV\');
  11. alter session set events \'10046 trace name context off\';

分析10046 trace文件,我们可以发现快速刷新执行的主要操作如下:

1.将mv logMLOG$_TEST_MV_LOG中的snaptime$$字段都更新为当前时间戳(sysdate)
 update "LT_TEST"."MLOG$_TEST_MV_LOG" set snaptime$$ = < 4/25/2014 5:45:47>  where snaptime$$ > to_date('2100-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS')
 由于新插入mv log表记录的SNAPTIME$$列初始值为40000101,所以新的记录到update执行时间点的数据都会被以上语句更新。
2.利用以下语句将上一步中snaptime$$列被置为sysdate的记录插入MV TEST_MV_LOG_MV中:
 INSERT INTO "LT_TEST"."TEST_MV_LOG_MV"  ("ID","NAME","STATUS") VALUES (:1,:2,:3)
3.删除MLOG$_TEST_MV_LOGsnaptime$$小于sysdate的记录:
 delete from "LT_TEST"."MLOG$_TEST_MV_LOG" where snaptime$$ <= <4/25/2014 5:45:47>;
4.在以上步骤中,会穿插着一些对数据字典的更新操作,如mlog$,snap$,slog$等。

以上就是传统timestamp-based materialized view log支持物化视图快速刷新的原理,接下来看看11g中commit SCN-based materialized view log有何不同:

  1. SQL> show release
  2. release 1102000400
  3.  
  4. create table test_mv_log
  5. (
  6. id number,
  7. name varchar2(10),
  8. status varchar(10)
  9. );
  10.  
  11. alter table test_mv_log add constraint pk_test_mv_log primary key(id);
  12.  
  13. create materialized view log on test_mv_log with commit scn;
  14.  
  15. create materialized view test_mv_log_mv refresh fast as select * from test_mv_log where status=\'Y\';
  16. SQL> select LOG_TABLE,COMMIT_SCN_BASED from dba_mview_logs where MASTER=\'TEST_MV_LOG\';
  17.  
  18. LOG_TABLE                      COM
  19. ------------------------------ ---
  20. MLOG$_TEST_MV_LOG              YES

可以看到,视图dba_mview_logs已经有新的列COMMIT_SCN_BASED用来识别这种新的MV log.

同样,我们用10046 trace来分析这种新的mv log对物化视图快速刷新的影响:

  1. insert into test_mv_log values(1,\'a\',\'Y\');
  2. commit;
  3.  
  4. SQL> select * from mlog$_test_mv_log;
  5.  
  6.         ID D O CHANGE_VECTOR$$ XID$$
  7. ---------- - - ------------------------------ ------------------------
  8.          1 I N FE 12947896183050683

与10g相比,SNAPTIME$$列消失了,取而代之的是XID$$列。我们已经知道,这种新的materialized view log是基于commit scn的,但这里的mv log表中却并没有commit scn信息,而是给的xid信息,这是为什么呢?

这是因为11g中给出了XID和commit scn的对应关系,记录在视图all_summap(其底层表是sys.snap_xcmt$)中,这样MV log中给出XID信息就可以知道commit scn了。

  1. SQL> select * from all_summap where xid=12947896183050683;
  2.                   XID COMMIT_SCN
  3. --------------------- ---------------------
  4. 12947896183050683 427340947178
  5.  
  6. alter session set events \'10046 trace name context forever,level 12\';
  7. exec dbms_mview.refresh(\'TEST_MV_LOG_MV\');
  8. alter session set events \'10046 trace name context off\';

分析10046 trace文件,看看commit SCN-based materialized view log上快速刷新执行的主要操作:

1. 获取刷新时系统的当前scn值:current scn;
2. 根据mv log表中记录的信息将记录插入MV(这里是用merge的方式)

  1. /* MV_REFRESH (MRG) */
  2. MERGE INTO \"LT_TEST\".\"TEST_MV_LOG_MV\" \"SNA$\"
  3. USING
  4. (SELECT CURRENT$.\"ID\",CURRENT$.\"NAME\",CURRENT$.\"STATUS\" FROM
  5. (SELECT \"TEST_MV_LOG\".\"ID\" \"ID\",\"TEST_MV_LOG\".\"NAME\" \"NAME\", \"TEST_MV_LOG\".\"STATUS\" \"STATUS\" FROM \"TEST_MV_LOG\" \"TEST_MV_LOG\"
  6. WHERE \"TEST_MV_LOG\".\"STATUS\"=\'Y\') CURRENT$,
  7. (SELECT DISTINCT MLOG$.\"ID\" FROM \"LT_TEST\".\"MLOG$_TEST_MV_LOG\" MLOG$, ALL_SUMMAP MAP$ WHERE MLOG$.XID$$ = MAP$.XID AND MAP$.COMMIT_SCN > :1 AND MAP$.COMMIT_SCN <= :2 AND (\"DMLTYPE$$\" != \'D\')) LOG$ WHERE CURRENT$.\"ID\" = LOG$.\"ID\")\"AV$\"
  8. ON (\"SNA$\".\"ID\" = \"AV$\".\"ID\")
  9. WHEN MATCHED
  10. THEN UPDATE SET \"SNA$\".\"ID\" = \"AV$\".\"ID\",\"SNA$\".\"NAME\" = \"AV$\".\"NAME\",\"SNA$\".\"STATUS\" = \"AV$\".\"STATUS\"
  11. WHEN NOT MATCHED
  12. THEN INSERT (SNA$.\"ID\",SNA$.\"NAME\",SNA$.\"STATUS\") V
  13. ALUES (AV$.\"ID\",AV$.\"NAME\",AV$.\"STATUS\")

上面的变量12应该分别是上次刷新的scncurrent scn

3. 删除mv log表已经被刷新的MV中的记录:

  1. delete from \"LT_TEST\".\"MLOG$_TEST_MV_LOG\"
  2. where rowid in
  3. ( select mas$.rowid from \"LT_TEST\".\"MLOG$_TEST_MV_LOG\" mas$, sys.snap_xcmt$ map$
  4. where mas$.xid$$ = map$.xid and map$.commit_scn <= :1 )

4. 在以上步骤中,会穿插着一些对数据字典的更新操作,如mlog$,snap$,slog$等。

知道了两种MV log的原理就可以大概分析他们在性能上所存在的差异:

1.  timestamp-based mv log中需要对整个mv log表作snaptime$$列的更新,而commit SCN-based mv log刷新则不需要作该操作;

2.  commit SCN-based mv log刷新时,在前面列出的第2,3步都需要于ALL_SUMMAP(或者sys.snap_xcmt$)作关联查询,这是相较于timestamp-based mv log性能处于劣势的地方。

总之,两种mv log所支持的刷新方式各有优劣,不能简单的说哪种方式就更好。

接下来要说的是11g中commit SCN-based materialized view log所存在的一个bug

BUG on commit SCN-based materialized view log

在11g中,当对MV作快速刷新时,需要查询底层表sys.snap_logdep$,然后用得到的scn去更新系统表sys.snap_loadertime$,我们继续用前面的例子来看,在快速刷新时其trace文件中有以下语句:

  1. SELECT snaptime, min(rscn) rscn FROM sys.snap_logdep$ WHERE tableobj# = 8891860 GROUP BY snaptime ORDER BY snaptime;
  2. --运行结果如下:
  3. SNAPTIME RSCN
  4. -------- --------------------
  5. 20140425 427341189143
  6. sys.snap_logdep$表记录的是某master表上的所有MV的上次刷新时间,SCN等信息的。
  7. 上面的object_id 8891860对应表test_mv_log
  8. UPDATE sys.snap_loadertime$ SET oldest_scn = 427341189143 WHERE tableobj# = 8891860 AND oldest_scn < :1

实际使用中我们会发现,有些对象在sys.snap_logdep$中存在多条记录,且某些记录的RSCN值为null,当存在这种情况时,我们在做快速刷新时就会遇到ora-00600错误:

ORA-00600: internal error code, arguments: [kkzlpllg:5], [], [], [], [], [], [], [], [], [], [], []

比如,针对上面的例子,我们可以伪造这种现象:

  1. select * from sys.snap_logdep$ where tableobj#=8891860;
  2.  TABLEOBJ# SNAPID SNAPTIME RSCN
  3. ---------- ---------- -------- --------------------
  4.    8891860 10090 20140425 427341189143
  5.  
  6. SQL> insert into sys.snap_logdep$ select TABLEOBJ#,10091,SNAPTIME,null from sys.snap_logdep$ where tableobj#=8891860;
  7.  
  8. 1 row created.
  9. SQL> commit;
  10. Commit complete.
  11.  
  12. SQL> select * from sys.snap_logdep$ where tableobj#=8891860;
  13.  
  14.  TABLEOBJ# SNAPID SNAPTIME RSCN
  15. ---------- ---------- -------- --------------------
  16.    8891860 10090 20140425 427341189143
  17.    8891860 10091 20140425

接下来更新表test_mv_log并快速刷新MV test_mv_log_mv:

  1. SQL> insert into test_mv_log values(2,\'b\',\'Y\');
  2.  
  3. 1 row created.
  4.  
  5. SQL> commit;
  6.  
  7. Commit complete.
  8.  
  9. SQL> select * from mlog$_test_mv_log;
  10.  
  11.         ID D O CHANGE_VECTOR$$ XID$$
  12. ---------- - - -------------------- --------------------
  13.          2 I N FE 12947870413247169
  14.  
  15. SQL> exec dbms_mview.refresh(\'TEST_MV_LOG_MV\');
  16. *
  17. ERROR at line 1:
  18. ORA-00600: internal error code, arguments: [kkzlpllg:5], [], [], [], [], [], [], [], [], [], [], []
  19. ORA-06512: at \"SYS.DBMS_SNAPSHOT\", line 2809
  20. ORA-06512: at \"SYS.DBMS_SNAPSHOT\", line 3025
  21. ORA-06512: at \"SYS.DBMS_SNAPSHOT\", line 2994
  22. ORA-06512: at line 1

这里出现了前面描述的ora-00600错误,但检查MV可发现数据其实已经刷新成功了。但mlog表中的信息并没有成功清除。

  1. SQL> select * from test_mv_log_mv;

  2.         ID NAME STATUS
  3. ---------- ---------- ----------
  4.          1 a Y
  5.          2 b Y
  6. SQL> select * from mlog$_test_mv_log;

  7.         ID D O CHANGE_VECTOR$$ XID$$
  8. ---------- - - -------------------- --------------------
  9.          2 I N FE 12947870413247169

查询metalink文档可知道,这是commit SCN-based materialized view log的一个bug(14158012),该bug不仅会导致刷新是出现ora-00600错误,还会导致删除该mv log的操作失败。而解决该bug的比较直接的方式就是更新表sys.snap_logdep$,将null值置为非null值,比如9999。


那么为什么会出现这些RSCN为null的值呢?

一个比较典型的原因是这样的:

  1. SQL> show release
  2. release 1002000500
  3. create table master_table(x number primary key, y number, z number);
  4. create snapshot log on master_table LOGGING WITH ROWID (x,y,z) , SEQUENCE INCLUDING NEW VALUES;
  5. create materialized view mv1 refresh fast as select count(*) s1, x, y from master_table group by (x,y);
  6.  
  7. SQL> select d.tableobj#, o.name
  8. from sys.snap_logdep$ d, sys.obj$ o
  9. where d.tableobj# = o.obj# and o.name=\'MASTER_TABLE\'; 2 3
  10.  
  11.  TABLEOBJ# NAME
  12. ---------- ------------------------------
  13.    9543200 MASTER_TABLE
  14.  
  15. drop table master_table;
  16. drop materialized view mv1;
  17. SQL> select * from sys.snap_logdep$ where TABLEOBJ#=9543200;
  18.  
  19.  TABLEOBJ# SNAPID SNAPTIME
  20. ---------- ---------- ----------
  21.    9543200 11054 2014-04-25

在该例子中,我们先删除了MV的master table,然后再删除MV,这样就导致master table在sys.snap_logdep$中没有被清理出去,成为了“孤儿”,而sys.snap_logdep$表的TABLEOBJ#并不是惟一的,后创建的对象很可能会重用该object_id,这样就会出现同一TABLEOBJ#值对应多条记录的情况,其中就包括“孤儿”记录。这在10g中似乎是没有什么影响的。但当数据库从10g升级到11g后,表sys.snap_logdep$的结构发生了变化,增加了RSCN列:

SQL> desc sys.snap_logdep$
 Name                Null?    Type          
 ------------------- -------- --------------
 TABLEOBJ#                    NUMBER
 SNAPID                       NUMBER(38)
 SNAPTIME                     DATE
 RSCN                         DATEBER

表sys.snap_logdep$中真正有效的记录会随着MV的刷新更新RSCN值,而“孤儿”记录则不能,所以就出现了RSCN为null的记录,继而引起前面的ora-00600错误。

对该bug及其产生原因的分析对我们的一个重要指导意义是,在11g中,如果我们想使用commit SCN-based materialized view log这个新特性,需要先确认一下sys.snap_logdep$表中对应master表的记录是否有” 孤儿”记录,否侧创建的MV会出现前面描述的问题。

参考:
http://www.adellera.it/blog/2009/11/03/11gr2-materialized-view-logs-changes/
http://www.seiler.us/2014/03/ora-00600-kkzlpllg5-when-dropping-mview.html

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27243841/viewspace-1149516/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-05-14

  • 博文量
    17
  • 访问量
    273156