从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

show release
release 1002000500
create table test_mv_log
(
id number,
name varchar2(10),
status varchar(10)
);
alter table test_mv_log add constraint pk_test_mv_log primary key(id);
create materialized view log on test_mv_log;
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下快速刷新的具体实现方式：

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

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

1．将mv log表MLOG$_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_LOG中snaptime$$小于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有何不同：

SQL> show release
release 1102000400
create table test_mv_log
(
id number,
name varchar2(10),
status varchar(10)
);
alter table test_mv_log add constraint pk_test_mv_log primary key(id);
create materialized view log on test_mv_log with commit scn;
create materialized view test_mv_log_mv refresh fast as select * from test_mv_log where status=\'Y\';
SQL> select LOG_TABLE,COMMIT_SCN_BASED from dba_mview_logs where MASTER=\'TEST_MV_LOG\';
LOG_TABLE COM
------------------------------ ---
MLOG$_TEST_MV_LOG YES

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

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

insert into test_mv_log values(1,\'a\',\'Y\');
commit;
SQL> select * from mlog$_test_mv_log;
ID D O CHANGE_VECTOR$$ XID$$
---------- - - ------------------------------ ------------------------
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了。

SQL> select * from all_summap where xid=12947896183050683;
XID COMMIT_SCN
--------------------- ---------------------
12947896183050683 427340947178
alter session set events \'10046 trace name context forever,level 12\';
exec dbms_mview.refresh(\'TEST_MV_LOG_MV\');
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的方式)

/* MV_REFRESH (MRG) */
MERGE INTO \"LT_TEST\".\"TEST_MV_LOG_MV\" \"SNA$\"
USING
(SELECT CURRENT$.\"ID\",CURRENT$.\"NAME\",CURRENT$.\"STATUS\" FROM
(SELECT \"TEST_MV_LOG\".\"ID\" \"ID\",\"TEST_MV_LOG\".\"NAME\" \"NAME\", \"TEST_MV_LOG\".\"STATUS\" \"STATUS\" FROM \"TEST_MV_LOG\" \"TEST_MV_LOG\"
WHERE \"TEST_MV_LOG\".\"STATUS\"=\'Y\') CURRENT$,
(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$\"
ON (\"SNA$\".\"ID\" = \"AV$\".\"ID\")
WHEN MATCHED
THEN UPDATE SET \"SNA$\".\"ID\" = \"AV$\".\"ID\",\"SNA$\".\"NAME\" = \"AV$\".\"NAME\",\"SNA$\".\"STATUS\" = \"AV$\".\"STATUS\"
WHEN NOT MATCHED
THEN INSERT (SNA$.\"ID\",SNA$.\"NAME\",SNA$.\"STATUS\") V
ALUES (AV$.\"ID\",AV$.\"NAME\",AV$.\"STATUS\")

上面的变量1和2应该分别是上次刷新的scn和current scn值

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

delete from \"LT_TEST\".\"MLOG$_TEST_MV_LOG\"
where rowid in
( select mas$.rowid from \"LT_TEST\".\"MLOG$_TEST_MV_LOG\" mas$, sys.snap_xcmt$ map$
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文件中有以下语句：

SELECT snaptime, min(rscn) rscn FROM sys.snap_logdep$ WHERE tableobj# = 8891860 GROUP BY snaptime ORDER BY snaptime;
--运行结果如下：
SNAPTIME RSCN
-------- --------------------
20140425 427341189143
sys.snap_logdep$表记录的是某master表上的所有MV的上次刷新时间，SCN等信息的。
上面的object_id 8891860对应表test_mv_log
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], [], [], [], [], [], [], [], [], [], [], []

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

select * from sys.snap_logdep$ where tableobj#=8891860;
TABLEOBJ# SNAPID SNAPTIME RSCN
---------- ---------- -------- --------------------
8891860 10090 20140425 427341189143
SQL> insert into sys.snap_logdep$ select TABLEOBJ#,10091,SNAPTIME,null from sys.snap_logdep$ where tableobj#=8891860;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from sys.snap_logdep$ where tableobj#=8891860;
TABLEOBJ# SNAPID SNAPTIME RSCN
---------- ---------- -------- --------------------
8891860 10090 20140425 427341189143
8891860 10091 20140425

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

SQL> insert into test_mv_log values(2,\'b\',\'Y\');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from mlog$_test_mv_log;
ID D O CHANGE_VECTOR$$ XID$$
---------- - - -------------------- --------------------
2 I N FE 12947870413247169
SQL> exec dbms_mview.refresh(\'TEST_MV_LOG_MV\');
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkzlpllg:5], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at \"SYS.DBMS_SNAPSHOT\", line 2809
ORA-06512: at \"SYS.DBMS_SNAPSHOT\", line 3025
ORA-06512: at \"SYS.DBMS_SNAPSHOT\", line 2994
ORA-06512: at line 1

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

SQL> select * from test_mv_log_mv;
ID NAME STATUS
---------- ---------- ----------
1 a Y
2 b Y
SQL> select * from mlog$_test_mv_log;
ID D O CHANGE_VECTOR$$ XID$$
---------- - - -------------------- --------------------
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的值呢？

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

SQL> show release
release 1002000500
create table master_table(x number primary key, y number, z number);
create snapshot log on master_table LOGGING WITH ROWID (x,y,z) , SEQUENCE INCLUDING NEW VALUES;
create materialized view mv1 refresh fast as select count(*) s1, x, y from master_table group by (x,y);
SQL> select d.tableobj#, o.name
from sys.snap_logdep$ d, sys.obj$ o
where d.tableobj# = o.obj# and o.name=\'MASTER_TABLE\'; 2 3
TABLEOBJ# NAME
---------- ------------------------------
9543200 MASTER_TABLE
drop table master_table;
drop materialized view mv1;
SQL> select * from sys.snap_logdep$ where TABLEOBJ#=9543200;
TABLEOBJ# SNAPID SNAPTIME
---------- ---------- ----------
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博客

11g中的materialized view log

最新文章