(http://www.freelists.org/post/oracle-l/Anybody-studied-refresh-mechanism-of-refresh-group) Subject: Anybody studied refresh mechanism of refresh group? Date: Fri, 30 Apr 2010 19:40:06 -0700 (PDT) According to Oracle, materialized views in a refresh group are guaranteed to be transactionally consistent when they are refreshed. This not only means that there's no commit in the middle of a refresh group's refresh, but also means that the source data seen by the first refreshed MV is not more not less than the data the last refreshed MV sees, similar to what consistent=y for exp or flashback_time|scn for expdp does when they export more than one table. That is, Suppose we have 2 MVs in a refresh group. They refresh from two tables rmtT1 and rmtT2 on remote DB, each for each MV (rmtT1 -> MV1, rmtT2 -> MV2). At time t1, MV1 starts to refresh from rmtT1. At time t2, MV1 finishes and MV2 starts to refresh from rmtT2. If between t1 and t2, rmtT2 has new rows inserted. At t2, MV2's refresh will ignore those new rows in rmtT2. How does Oracle make sure at t2, MV2 only reads rows as existed in rmtT2 at the time of t1? We know exp consistent=y simply runs "set transaction read only" at the beginning of the exp session, and expdp flashback_xxx probably uses flashback query (select ... as of). What does refresh group use? I enabled SQL trace in the source database (master site, where rmtT1 and rmtT2 are), but there's no special SQL seen during the period, no "set transaction read only", no "select ... as of". Has anybody studied this? My test is done in Oracle 10.2.0.4. If interested, please view the SQL trace of the session on the source DB (see Appendix I). Note dbms_refresh.refresh makes calls to DBMS_SNAPSHOT_UTL.VERIFY_LOG and DBMS_SNAPSHOT_UTL.WRAP_UP, which is not seen in dbms_mview.refresh (an individual mview's refresh). Maybe those two procedures make the difference? Amit Bansal suggested that there may be queries like select ... from mlog$ wheer snaptime$$ > :1 on the source. Unfortunately that SQL is not found in the SQL trace (Appendix I) and no column like 'SNAPTIME%' is in sys.col_usage$ of source right after dbms_refresh.refresh on target and exec dbms_stats.flush_database_monitoring_info on source (Appendix II). (If a SQL used no_monitoring hint, col_usage$ would not be updated. But in that case, the SQL will still be captured by SQL trace.) Appendix I SQL trace on the source (master, remote DB) site when dbms_refresh.refresh is run on target (local DB). The test case is as follows: On remote DB: create table rmtt1 (x int primary key); create table rmtt2 (x int primary key); On local DB: create materialized view mv1 as select * from yhuang.rmtt1@rmtdb; create materialized view mv2 as select * from yhuang.rmtt2@rmtdb; exec dbms_refresh.make('refgroup', 'mv1', sysdate, null, null) exec dbms_refresh.add('refgroup', 'mv2') exec dbms_refresh.refresh('refgroup') Note that SQL trace on rmtdb has to be enabled on the session created on behalf of this DB link. In the local session (local means on target DB), initiate a DB link (e.g. select * from dual@rmtdb). Find the session in the remote (source) DB with this SQL: select /*+ ordered */ substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) origin, substr(g.k2gtitid_ora,1,35) gtxid, substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) lsession, s.ksuudlna username, substr(decode(bitand(ksuseidl,11), 1,'ACTIVE', 0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'), 2,'SNIPED', 3,'SNIPED', 'KILLED'),1,1) status, e.kslednam waiting from x$k2gte g, x$ktcxb t, x$ksuse s, x$ksled e where g.k2gtdxcb=t.ktcxbxba and g.k2gtdses=t.ktcxbses and s.addr=g.k2gtdses and e.indx=s.ksuseopc; Then use dbms_system.set_ev (or equivalent) to set event 10046 level 4 to it. The SQL trace on source on behalf of dbms_refresh.refresh on target is shown below (bind values are under the SQLs): set constraints all deferred RPC CALL:PROCEDURE SYS.DBMS_SNAPSHOT_UTL.SET_UP(MOWN_COL IN IDENS, MAS_COL IN IDENS, ROLLSEG_COL IN IDENS, FLAG_COL IN SCALARS, OUTFLAG_COL OUT SCALARS, SNAPTIME_COL OUT DATES, LOADERTIME_COL OUT DATES, NUM_SID IN BINARY_INTEGER, SNAPID_TAB OUT SCALARS, MAX_TAB_SCN_COL OUT SCALARS, MAX_DL_SCN_COL OUT SCALARS, MAX_PMOP_SCN_COL OUT SCALARS, TRUNC_SCN_COL OUT SCALARS, MAX_INS_TIM_COL OUT DATES, MAX_UPD_TIM_COL OUT DATES, MAX_DEL_TIM_COL OUT DATES, MAX_INS_SCN_COL OUT SCALARS, MAX_UPD_SCN_COL OUT SCALARS, MAX_DEL_SCN_COL OUT SCALARS, HDL_COL IN SCALARS, HDL_INDX_COL IN SCALARS, RSCN OUT NUMBER); all are 0's select 1 from sys.cdc_change_tables$ where source_schema_name = :1 and source_table_name = :2 and bitand(mvl_flag, 128)=128 "YHUANG" "RMTT1" select log, oldest, oldest_pk, oldest_oid, oldest_new, youngest+1/86400, flag, yscn, oldest_seq from sys.mlog$ where mowner = :1 and master = :2 "YHUANG" "RMTT1" select log, oldest, oldest_pk, oldest_oid, oldest_new, youngest+1/86400, flag, yscn, oldest_seq from sys.mlog$ where mowner = :1 and master = :2 for update "YHUANG" "RMTT1" select 1 from sys.cdc_change_tables$ where source_schema_name = :1 and source_table_name = :2 and bitand(mvl_flag, 128)=128 "YHUANG" "RMTT2" select log, oldest, oldest_pk, oldest_oid, oldest_new, youngest+1/86400, flag, yscn, oldest_seq from sys.mlog$ where mowner = :1 and master = :2 "YHUANG" "RMTT2" select log, oldest, oldest_pk, oldest_oid, oldest_new, youngest+1/86400, flag, yscn, oldest_seq from sys.mlog$ where mowner = :1 and master = :2 for update "YHUANG" "RMTT2" select decode(bitand(s.flags,1),0,0,1), NVL(max(s.scn), 0) from sumpartlog$ s where s.bo# = :1 and nvl(s.scn,0) <= :2 group by decode(bitand(s.flags,1),0,0,1) 415048 35608764662 select decode(bitand(s.flags,1),0,0,1), NVL(max(s.scn), 0) from sumpartlog$ s where s.bo# = :1 and nvl(s.scn,0) <= :2 group by decode(bitand(s.flags,1),0,0,1) 415050 35608764662 RPC CALL:PROCEDURE SYS.DBMS_SNAPSHOT_UTL.VERIFY_LOG(MOWN_COL IN IDENS, MAS_COL IN IDENS, MASOBJ_COL OUT SCALARS, FLAG_COL IN SCALARS, OUTFLAG_COL OUT SCALARS, LOGNM_COL OUT IDENS, OLDEST_COL OUT DATES, OLDESTPK_COL OUT DATES, OLDLOADER_COL OUT DATES, OLDEST_OID_COL OUT DATES, OLDESTNEW_COL OUT DATES, FCVEC_COL OUT RAWS, YOUNGEST_FC_COL OUT DATES, HDL_COL IN SCALARS, HDL_INDX_COL IN SCALARS, LRSCN_COL IN SCALARS, STAT_COL OUT SCALARS, TYP_OWN_COL IN IDENS, TYP_NAM_COL IN IDENS, TOID_COL OUT OIDS, HASHCODE_COL OUT HASHCODES , OLDESTSEQ_COL OUT DATES); all are 0's select 1 from sys.cdc_change_tables$ where source_schema_name = :1 and source_table_name = :2 and bitand(mvl_flag, 128)=128 select log, oldest, oldest_pk, oldest_oid, oldest_new, youngest+1/86400, flag, yscn, oldest_seq from sys.mlog$ where mowner = :1 and master = :2 select log, oldest, oldest_pk, oldest_oid, oldest_new, youngest+1/86400, flag, yscn, oldest_seq from sys.mlog$ where mowner = :1 and master = :2 select 1 from sys.cdc_change_tables$ where source_schema_name = :1 and source_table_name = :2 and bitand(mvl_flag, 128)=128 select log, oldest, oldest_pk, oldest_oid, oldest_new, youngest+1/86400, flag, yscn, oldest_seq from sys.mlog$ where mowner = :1 and master = :2 select log, oldest, oldest_pk, oldest_oid, oldest_new, youngest+1/86400, flag, yscn, oldest_seq from sys.mlog$ where mowner = :1 and master = :2 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ NO_PARALLEL("RMTT1") FULL("RMTT1") NO_PARALLEL_INDEX("RMTT1") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "YHUANG"."RMTT1" "RMTT1") SAMPLESUB 0 0 1 1 SELECT /*+ OPAQUE_TRANSFORM */ "X" FROM "YHUANG"."RMTT1" "RMTT1" SELECT /*+ OPAQUE_TRANSFORM */ "X" FROM "YHUANG"."RMTT2" "RMTT2" RPC CALL:PROCEDURE SYS.DBMS_SNAPSHOT_UTL.WRAP_UP(MOWN_COL IN IDENS, MAS_COL IN IDENS, MASOBJ_COL IN SCALARS, ROLLSEG_COL IN IDENS, FLAG_COL IN SCALARS, SNAPTIME_COL IN DATES, LOADERTIME_COL IN DATES, SNAPID_COL IN SCALARS, SNAPTYPE_COL IN SCALARS, MASIDX_COL all are 0's select 1 from sys.cdc_change_tables$ where source_schema_name = :1 and source_table_name = :2 and bitand(mvl_flag, 128)=128 select OLDEST_PK, flag from sys.mlog$ where mowner = :1 and master = :2 for update select 1 from sys.cdc_change_tables$ where source_schema_name = :1 and source_table_name = :2 and bitand(mvl_flag, 128)=128 select OLDEST_PK, flag from sys.mlog$ where mowner = :1 and master = :2 for update For comparison, SQL trace on source is also shown below when just refreshing one mview with exec dbms_mview.refresh('mv1') on target: set constraints all deferred RPC CALL:PROCEDURE SYS.DBMS_SNAPSHOT_UTL.SET_UP(MOWN_COL IN IDENS, MAS_COL IN IDENS, ROLLSEG_COL IN IDENS, FLAG_COL IN SCALARS, OUTFLAG_COL OUT SCALARS, SNAPTIME_COL OUT DATES, LOADERTIME_COL OUT DATES, NUM_SID IN BINARY_INTEGER, SNAPID_TAB OUT SCALARS, MAX_TAB_SCN_COL OUT SCALARS, MAX_DL_SCN_COL OUT SCALARS, MAX_PMOP_SCN_COL OUT SCALARS, TRUNC_SCN_COL OUT SCALARS, MAX_INS_TIM_COL OUT DATES, MAX_UPD_TIM_COL OUT DATES, MAX_DEL_TIM_COL OUT DATES, MAX_INS_SCN_COL OUT SCALARS, MAX_UPD_SCN_COL OUT SCALARS, MAX_DEL_SCN_COL OUT SCALARS, HDL_COL IN SCALARS, HDL_INDX_COL IN SCALARS, RSCN OUT NUMBER); select 1 from sys.cdc_change_tables$ where source_schema_name = :1 and source_table_name = :2 and bitand(mvl_flag, 128)=128 "YHUANG" "RMTT1" select log, oldest, oldest_pk, oldest_oid, oldest_new, youngest+1/86400, flag, yscn, oldest_seq from sys.mlog$ where mowner = :1 and master = :2 "YHUANG" "RMTT1" select log, oldest, oldest_pk, oldest_oid, oldest_new, youngest+1/86400, flag, yscn, oldest_seq from sys.mlog$ where mowner = :1 and master = :2 for update "YHUANG" "RMTT1" select decode(bitand(s.flags,1),0,0,1), NVL(max(s.scn), 0) from sumpartlog$ s where s.bo# = :1 and nvl(s.scn,0) <= :2 group by decode(bitand(s.flags,1),0,0,1) 415048 35608774004 select 1 from sys.cdc_change_tables$ where source_schema_name = :1 and source_table_name = :2 and bitand(mvl_flag, 128)=128 select log, oldest, oldest_pk, oldest_oid, oldest_new, youngest+1/86400, flag, yscn, oldest_seq from sys.mlog$ where mowner = :1 and master = :2 select log, oldest, oldest_pk, oldest_oid, oldest_new, youngest+1/86400, flag, yscn, oldest_seq from sys.mlog$ where mowner = :1 and master = :2 SELECT /*+ OPAQUE_TRANSFORM */ "X" FROM "YHUANG"."RMTT1" "RMTT1" select 1 from sys.cdc_change_tables$ where source_schema_name = :1 and source_table_name = :2 and bitand(mvl_flag, 128)=128 select OLDEST_PK, flag from sys.mlog$ where mowner = :1 and master = :2 for update You see, there's no "select ... as of", no "set transaction read only", no "select ... where snaptime" (or snapptime$). Appendix II Find the recently queried columns named like 'SNAPTIME%': select a.owner, table_name, column_name, object_id from dba_tab_columns a, dba_objects b where a.table_name = b.object_name and column_name like 'SNAPTIME%' and a.owner in ('SYS', 'YHUANG') order by 1, 2, 3; OWNER TABLE_NAME COLUMN_NAME OBJECT_ID ------ -------------- ----------- --------- SYS EXU8SLOG SNAPTIME 3121 SYS EXU8SLOGU SNAPTIME 3122 SYS SLOG$ SNAPTIME 166 SYS SNAP$ SNAPTIME 212 SYS SNAP_LOGDEP$ SNAPTIME 159 SYS SNAP_REFTIME$ SNAPTIME 220 YHUANG MLOG$_A SNAPTIME$$ 411958 YHUANG MLOG$_B SNAPTIME$$ 411959 YHUANG MLOG$_C SNAPTIME$$ 411963 YHUANG MLOG$_D SNAPTIME$$ 411964 YHUANG MLOG$_UAS_USER SNAPTIME$$ 411972 select a.obj#, a.intcol#, name colnm, timestamp from sys.col_usage$ a, sys.col$ b where a.obj# = b.obj# and a.intcol# = b.intcol# and a.obj# in (3121, 3122, 166, 212, 159, 220, 411958, 411959, 411963, 411964, 411972) order by timestamp; OBJ# INTCOL# COLNM TIMESTAMP ---- ------- -------------- ----------------- 212 10 SNAPID 20081217 23:16:01 212 20 USLOG 20090128 12:57:07 212 19 USTRG 20090128 12:57:07 212 4 MVIEW 20090128 12:57:07 220 8 MASOBJ# 20090305 11:29:41 212 6 MASTER 20100219 10:49:24 212 5 MOWNER 20100219 10:49:24 166 4 SNAPID 20100429 15:32:52 159 2 SNAPID 20100503 02:00:27 220 5 MOWNER 20100503 02:00:27 220 6 MASTER 20100503 02:00:27 212 7 MLINK 20100503 02:00:27 212 21 STATUS 20100503 02:00:27 159 1 TABLEOBJ# 20100504 02:18:25 212 46 PARENT_SOWNER 20100506 08:47:10 212 47 PARENT_VNAME 20100506 08:47:10 212 3 TNAME 20100506 13:37:09 220 11 INSTSITE 20100506 14:28:51 220 3 TABLENUM 20100506 14:28:51 220 2 VNAME 20100506 14:28:51 220 1 SOWNER 20100506 14:28:51 212 37 OBJFLAG 20100506 14:28:51 212 35 INSTSITE 20100506 14:28:51 212 26 FLAG 20100506 14:28:51 166 1 MOWNER 20100506 14:28:51 166 2 MASTER 20100506 14:28:51 212 1 SOWNER 20100506 14:28:51 212 2 VNAME 20100506 14:28:51 You can see, no column named like 'SNAPTIME%' is recently used. However, if I have materialized view log as in this simplest case, then I'll see snaptime and snaptime$$: create table t (x int primary key); create materialized view log on t; --newly added create materialized view mv_t as select * from t; alter session set events '10046 trace name context forever, level 4'; exec dbms_mview.refresh('mv_t') grep -i snaptime The SQL trace has these lines: SELECT masobj#, masflag, mowner, master, lastsuccess, snaptime, sysdate, loadertime, sysdate, NVL(refscn, 0), fcmaskvec, ejms.snap_reftime$ WHERE sowner = :1 AND vname = :2 AND instsite = :3 ORDER BY tablenum update "YHUANG"."MLOG$_T" set snaptime$$ = :1 where snaptime$$ > to_date('2100-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS') --:1 is "5/10/2010 10:5:27", the time I did the refresh. This SQL is the only one with snaptime or snaptime$$ greater than something. update sys.snap$ set snapshot = :1, snaptime = :2, auto_date = :3, snapid = :4, error# = 0 where vname = :6 and sowner = :5 and --Both :1 and :2 are "1/1/1950 12:0:0" UPDATE sys.snap_reftime$ SET snaptime = :1, loadertime = :2, refscn = :3, fcmaskvec = :4, ejmaskvec = :5, masobj# = :6, suster = :10 WHERE sowner = :11 AND vname = :12 AND tablenum = :13 AND instsite = :14 --:1 is "5/10/2010 10:5:27", the time I did the refresh, :2 is "1/1/1950 12:0:0" update sys.slog$ set snaptime = :1 where snapid = :2 and mowner = :3 and master = :4 --:1 is "5/10/2010 10:5:27" select snaptime from sys.slog$ where master = :2 and mowner = :1 order by snaptime delete from "YHUANG"."MLOG$_T" where snaptime$$ <= :1 --:1 is "5/10/2010 10:5:27" But this doesn't answer the original question because mview log is not required for a refresh group.