11.2中物化视图日志可以指定COMMIT SCN，这时物化视图刷新就不需要时间戳了，这篇简单描述Oracle是如何实现通过COMMIT SCN来进行刷新的。


建立一个测试环境：


SQL> SELECT * FROM V$VERSION;


BANNER


--------------------------------------------------------------------------------


Oracle Database11gEnterprise Edition Release11.2.0.1.0 - 64bit Production


PL/SQL Release 11.2.0.1.0 - Production


CORE    11.2.0.1.0      Production


TNS for Linux: Version 11.2.0.1.0 - Production


NLSRTL Version 11.2.0.1.0 - Production


SQL> CREATE TABLE T


 2  (ID NUMBER PRIMARY KEY,


 3  NAME VARCHAR2(30),


 4  AGE NUMBER(3));


表已创建。


SQL> INSERT INTO T


 2  SELECT ROWNUM, TNAME, ROWNUM


 3  FROM TAB;


已创建18行。


SQL> CREATE MATERIALIZED VIEW LOG ON T


 2  WITH COMMIT SCN;


实体化视图日志已创建。


SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST


 2  AS SELECT * FROM T;


实体化视图已创建。


对物化视图基表进行修改：


SQL> INSERT INTO T      


 2  VALUES (20, 'TEST', 100);


已创建1行。


SQL> UPDATE T


 2  SET NAME = 'TEST UPDATE'


 3  WHERE ID = 1;


已更新1行。


SQL> DELETE T


 2  WHERE ID = 2;


已删除1行。


SQL> COMMIT;


提交完成。


SQL>COLCHANGE_VECTOR$$ FORMAT A30


SQL> SET NUMW 16


SQL> SELECT * FROM MLOG$_T;


             ID D O CHANGE_VECTOR$$                           XID$$


---------------- - - ------------------------------ ----------------


             20 I N FE                             2533498128696031


              1 U U 04                             2533498128696031


              2 D O 00                             2533498128696031


可以看到物化视图日志中记录了INSERT、UPDATE和DELETE操作，由于指定了COMMIT SCN，物化视图日志中多出了XID$$列，而缺少了SNAPTIME$$列。


SQL> CREATE TABLE T_TIMESTAMP (ID NUMBER PRIMARY KEY);


表已创建。


SQL> CREATE MATERIALIZED VIEW LOG ON T_TIMESTAMP;


实体化视图日志已创建。


SQL> INSERT INTO T_TIMESTAMP VALUES (1);


已创建1行。


SQL> SELECT * FROM MLOG$_T_TIMESTAMP;


             ID SNAPTIME$$     D O CHANGE_VECTOR$$                           XID$$


---------------- -------------- - - ------------------------------ ----------------


              1 01-1月-00     I N FE                             2814912975871092


可以看到，即使不指定WITH COMMIT SCN，11.2的物化视图日志也会包含XID$$列，估计是为了实现统一的接口。


下面对刷新过程进行TRACE，看看Oracle是如何实现刷新的：


SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';


会话已更改。


SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')


PL/SQL过程已成功完成。


SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';


会话已更改。


SQL> SELECT SPID              


 2  FROM V$PROCESS P, V$SESSION S


 3  WHERE P.ADDR = S.PADDR


 4  AND SID IN


 5  (SELECT SID      


 6  FROM V$MYSTAT


 7  WHERE ROWNUM = 1);


SPID


------------------------


9862


检查对应的日志：


Trace file /data/oracle/diag/rdbms/test112/test112/trace/test112_ora_9862.trc


Oracle Database11gEnterprise Edition Release11.2.0.1.0 - 64bit Production


With the Partitioning, OLAP, Data Mining and Real Application Testing options


ORACLE_HOME = /data/oracle/product/11.2


System name:    Linux


Node name:      bjtest


Release:        2.6.18-8.el5xen


Version:        #1 SMP Tue Jun 5 23:53:34 EDT 2007


Machine:        x86_64


Instance name: test112


Redo thread mounted by this instance: 1


Oracle process number: 25


Unix process pid: 9862, p_w_picpath: oracle@bjtest (TNS V1-V3)




*** 2010-01-20 02:29:42.045


*** SESSION ID:(146.5127) 2010-01-20 02:29:42.045


*** CLIENT ID:() 2010-01-20 02:29:42.045


*** SERVICE NAME:(SYS$USERS) 2010-01-20 02:29:42.045


*** MODULE NAME:(SQL*Plus) 2010-01-20 02:29:42.045


*** ACTION NAME:() 2010-01-20 02:29:42.045


WAIT #3: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1263925782044841


*** 2010-01-20 02:29:52.082


WAIT #3: nam='SQL*Net message from client' ela= 10037338 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1263925792082882


CLOSE #3:c=0,e=36,dep=0,type=1,tim=1263925792083083


=====================


PARSING IN CURSOR #2 len=40 dep=0 uid=85 ct=47 lid=85 tim=1263925792083357 hv=227083342 ad='1f3d5d940' sqlid='7z7aqjn6sk12f'


BEGIN DBMS_MVIEW.REFRESH('MV_T'); END;


END OF STMT


PARSE #2:c=0,e=184,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1263925792083352


=====================


PARSING IN CURSOR #8 len=11 dep=1 uid=0 ct=44 lid=0 tim=1263925792084131 hv=1180858989 ad='0' sqlid='87rnsy1364ymd'


COMMIT WORK


END OF STMT


PARSE #8:c=0,e=36,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1263925792084126


XCTEND rlbk=0, rd_only=0, tim=1263925792084276


=====================


.


.


.


=====================


PARSING IN CURSOR #12 len=378 dep=1 uid=85 ct=7 lid=85 tim=1263925792302844 hv=4055642071 ad='1ef8d5650' sqlid='6rvarb7svsbyr'


DELETE FROM "TEST"."MV_T" SNAP$ WHERE "ID" IN (SELECT DISTINCT LOG$."ID" FROM (SELECT MLOG$."ID" FROM "TEST"."MLOG$_T" MLOG$ ,  ALL_SUMMAP MAP$ WHERE MLOG$.XID$$ = MAP$.XID AND                        MAP$.COMMIT_SCN > :1 AND MAP$.COMMIT_SCN <= :2 AND ("DMLTYPE$$" != 'I')) LOG$ WHERE (LOG$."ID") NOT IN (SELECT MAS_TAB$."ID" FROM "T" "MAS_TAB$" WHERE LOG$."ID" = MAS_TAB$."ID"))


END OF STMT


PARSE #12:c=0,e=600,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1263925792302839


=====================


.


.


.


BINDS #12:


Bind#0


 acdty=02 mxl=22(08) mxlc=00 mal=00 scl=00 pre=00


 acflg=10 fl2=0001 frm=00 csi=00 siz=24 ff=0


 kxsbbbfp=7fffb51b5fa8  bln=22  avl=08  flg=09


 value=1112622613804


Bind#1


 acdty=02 mxl=22(08) mxlc=00 mal=00 scl=00 pre=00


 acflg=10 fl2=0001 frm=00 csi=00 siz=24 ff=0


 kxsbbbfp=7fffb51b5f90  bln=22  avl=08  flg=09


 value=1112622614232


WAIT #12: nam='asynch descriptor resize' ela= 5 outstanding #aio=0 current aio limit=180 new aio limit=245 obj#=-1 tim=1263925792381973


WAIT #12: nam='asynch descriptor resize' ela= 3 outstanding #aio=0 current aio limit=245 new aio limit=180 obj#=-1 tim=1263925792382401


EXEC #12:c=32002,e=79680,p=0,cr=48,cu=3,mis=1,r=1,dep=1,og=1,plh=3947379916,tim=1263925792382655


STAT #12 id=1 cnt=0 pid=0 pos=1 bj=0 p='DELETE  MV_T (cr=13 pr=0 pw=0 time=0 us)'


STAT #12 id=2 cnt=1 pid=1 pos=1 bj=0 p='NESTED LOOPS  (cr=13 pr=0 pw=0 time=0 us cost=9 size=26 card=1)'


STAT #12 id=3 cnt=1 pid=2 pos=1 bj=0 p='VIEW  VW_NSO_1 (cr=12 pr=0 pw=0 time=0 us cost=8 size=26 card=2)'


STAT #12 id=4 cnt=1 pid=3 pos=1 bj=0 p='SORT UNIQUE (cr=12 pr=0 pw=0 time=0 us)'


STAT #12 id=5 cnt=1 pid=4 pos=1 bj=0 p='FILTER  (cr=12 pr=0 pw=0 time=0 us)'


STAT #12 id=6 cnt=2 pid=5 pos=1 bj=0 p='FILTER  (cr=10 pr=0 pw=0 time=6 us)'


STAT #12 id=7 cnt=2 pid=6 pos=1 bj=0 p='HASH JOIN  (cr=10 pr=0 pw=0 time=0 us cost=7 size=108 card=2)'


STAT #12 id=8 cnt=1 pid=7 pos=1 bj=636 p='TABLE ACCESS FULL SNAP_XCMT$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=26 card=1)'


STAT #12 id=9 cnt=2 pid=7 pos=2 bj=84200 p='TABLE ACCESS FULL MLOG$_T (cr=7 pr=0 pw=0 time=0 us cost=4 size=56 card=2)'


STAT #12 id=10 cnt=1 pid=5 pos=2 bj=84199 p='INDEX UNIQUE SCAN SYS_C0011157 (cr=2 pr=0 pw=0 time=0 us cost=1 size=13 card=1)'


STAT #12 id=11 cnt=1 pid=2 pos=2 bj=84203 p='INDEX UNIQUE SCAN SYS_C0011158 (cr=1 pr=0 pw=0 time=0 us cost=0 size=13 card=1)'


=====================


.


.


.


=====================


PARSING IN CURSOR #12 len=690 dep=1 uid=85 ct=189 lid=85 tim=1263925792384809 hv=766335958 ad='1f3c8d7f8' sqlid='7x4b9jnquuqyq'


/* MV_REFRESH (MRG) */ MERGE INTO "TEST"."MV_T" "SNA$" USING (SELECT CURRENT$."ID",CURRENT$."NAME",CURRENT$."AGE" FROM (SELECT "T"."ID" "ID","T"."NAME" "NAME","T"."AGE" "AGE" FROM "T" "T") CURRENT$, (SELECT DISTINCT MLOG$."ID" FROM "TEST"."MLOG$_T" 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$"."AGE" = "AV$"."AGE" WHEN NOT MATCHED THEN INSERT  (SNA$."ID",SNA$."NAME",SNA$."AGE") VALUES (AV$."ID",AV$."NAME",AV$."AGE")


END OF STMT


PARSE #12:c=0,e=796,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1263925792384804


=====================


.


.


.


BINDS #12:


Bind#0


 acdty=02 mxl=22(08) mxlc=00 mal=00 scl=00 pre=00


 acflg=10 fl2=0001 frm=00 csi=00 siz=24 ff=0


 kxsbbbfp=7fffb51b5f38  bln=22  avl=08  flg=09


 value=1112622613804


Bind#1


 acdty=02 mxl=22(08) mxlc=00 mal=00 scl=00 pre=00


 acflg=10 fl2=0001 frm=00 csi=00 siz=24 ff=0


 kxsbbbfp=7fffb51b5f20  bln=22  avl=08  flg=09


 value=1112622614232


WAIT #12: nam='asynch descriptor resize' ela= 6 outstanding #aio=0 current aio limit=180 new aio limit=245 obj#=-1 tim=1263925792433371


WAIT #12: nam='asynch descriptor resize' ela= 4 outstanding #aio=0 current aio limit=245 new aio limit=180 obj#=-1 tim=1263925792433722


EXEC #12:c=40002,e=59289,p=0,cr=83,cu=27,mis=1,r=2,dep=1,og=1,plh=670631188,tim=1263925792444269


STAT #12 id=1 cnt=0 pid=0 pos=1 bj=0 p='MERGE  MV_T (cr=22 pr=0 pw=0 time=0 us)'


STAT #12 id=2 cnt=2 pid=1 pos=1 bj=0 p='VIEW  (cr=17 pr=0 pw=0 time=101 us)'


STAT #12 id=3 cnt=2 pid=2 pos=1 bj=0 p='NESTED LOOPS OUTER (cr=17 pr=0 pw=0 time=96 us cost=11 size=222 card=2)'


STAT #12 id=4 cnt=2 pid=3 pos=1 bj=0 p='NESTED LOOPS  (cr=14 pr=0 pw=0 time=53 us cost=10 size=112 card=2)'


STAT #12 id=5 cnt=2 pid=4 pos=1 bj=0 p='VIEW  (cr=10 pr=0 pw=0 time=9 us cost=8 size=26 card=2)'


STAT #12 id=6 cnt=2 pid=5 pos=1 bj=0 p='SORT UNIQUE (cr=10 pr=0 pw=0 time=3 us cost=8 size=108 card=2)'


STAT #12 id=7 cnt=2 pid=6 pos=1 bj=0 p='FILTER  (cr=10 pr=0 pw=0 time=18 us)'


STAT #12 id=8 cnt=2 pid=7 pos=1 bj=0 p='HASH JOIN  (cr=10 pr=0 pw=0 time=14 us cost=7 size=108 card=2)'


STAT #12 id=9 cnt=1 pid=8 pos=1 bj=636 p='TABLE ACCESS FULL SNAP_XCMT$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=26 card=1)'


STAT #12 id=10 cnt=2 pid=8 pos=2 bj=84200 p='TABLE ACCESS FULL MLOG$_T (cr=7 pr=0 pw=0 time=3 us cost=4 size=56 card=2)'


STAT #12 id=11 cnt=2 pid=4 pos=2 bj=84198 p='TABLE ACCESS BY INDEX ROWID T (cr=4 pr=0 pw=0 time=0 us cost=1 size=43 card=1)'


STAT #12 id=12 cnt=2 pid=11 pos=1 bj=84199 p='INDEX UNIQUE SCAN SYS_C0011157 (cr=2 pr=0 pw=0 time=0 us cost=0 size=0 card=1)'


STAT #12 id=13 cnt=1 pid=3 pos=2 bj=84202 p='MAT_VIEW ACCESS BY INDEX ROWID MV_T (cr=3 pr=0 pw=0 time=0 us cost=1 size=55 card=1)'


STAT #12 id=14 cnt=1 pid=13 pos=1 bj=84203 p='INDEX UNIQUE SCAN SYS_C0011158 (cr=2 pr=0 pw=0 time=0 us cost=0 size=0 card=1)'


CLOSE #12:c=0,e=18,dep=1,type=0,tim=1263925792444951


CLOSE #9:c=0,e=17,dep=1,type=0,tim=1263925792445202


=====================


根据上面的TRACE结果不难分析出Oracle是如何实现快速刷新的。



oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html