(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 <trace file>

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.