從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/

11g中的materialized view log

相關文章