10 September, 2007

ATOMIC_REFRESH=>FALSE causes TRUNCATE and INSERT behaviour in 10g ??

The 10g Datawarehousing documentation, when speaking of maintaining MViews inadvertently seems to imply that even FAST Refresh's can benefit with TRUNCATE.
Immediately after the text about the ATOMIC_REFRESH parameter (" If set to FALSE, Oracle can optimize refresh by using parallel DML and truncate DDL on a materialized views."), come the words :
For example, to perform a fast refresh on the materialized view cal_month_sales_mv, the DBMS_MVIEW package would be called as follows:
DBMS_MVIEW.REFRESH('CAL_MONTH_SALES_MV', 'F', '', TRUE, FALSE, 0,0,0, FALSE);
seeming to imply that even a FAST Refresh can use a TRUNCATE and INSERT -- which, certainly, cannot be the case.

Furthermore, when I tested (10.2.0.1 on Windows) refreshing 1 and 2 MVs together, I found that the TRUNCATE would occur only if one MV was being refreshed. If 2 MVs were being refreshed, even if ATOMIC_REFRESH was set to FALSE, I saw both suffering DELETE and INSERTs.

These were my test SQLs :

set echo on
set timing on
spool Test_MV_Complete_Refresh
drop materialized view my_dummy_mv;
drop materialized view my_mv_fast;
drop materialized view my_mv_complete;
drop materialized view my_mv2_complete;
drop table my_source_tab purge;
create table my_source_tab as select * from dba_objects where 1=2;
alter table my_source_tab nologging;
insert /*+ APPEND */ into my_source_tab select * from dba_objects;
commit;
delete my_source_tab where object_id is null ;
alter table my_source_tab modify (object_id not null);
create unique index my_source_tab_u1 on my_source_tab(object_id);
alter table my_source_tab add primary key (object_id);
create snapshot log on my_source_tab ;
create materialized view my_mv_fast
refresh fast on demand
as select * from my_source_tab;
create materialized view my_mv_complete
refresh complete on demand
as select * from my_source_tab;
create materialized view my_mv2_complete
refresh complete on demand
as select * from my_source_tab;
create materialized view my_dummy_mv
refresh complete
as select * from dual
where dummy='X' and rownum=1
/
exec dbms_mview.refresh('MY_MV_FAST','F');
exec dbms_mview.refresh('MY_MV_COMPLETE','C');
exec dbms_mview.refresh('MY_MV_COMPLETE,MY_DUMMY_MV','C');
exec dbms_mview.refresh('MY_MV_COMPLETE,MY_MV2_COMPLETE','C');
alter session set tracefile_identifier='T_M_C_R';
alter session set sql_trace=TRUE;
exec dbms_mview.refresh('MY_MV_FAST','F',atomic_refresh=>FALSE);
exec dbms_mview.refresh('MY_MV_COMPLETE','C',atomic_refresh=>FALSE);
exec dbms_mview.refresh('MY_MV_COMPLETE,MY_DUMMY_MV','C',atomic_refresh=>FALSE);
exec dbms_mview.refresh('MY_MV_COMPLETE,MY_MV2_COMPLETE','C',atomic_refresh=>FALSE);
spool off
exit

8 comments:

Anonymous said...

Nice work. I ran your test and got trace file. How did you know 'delete and insert ' happened instead of 'truncate and insert'?

Following is part of the trace file:

PARSING IN CURSOR #12 len=72 dep=0 uid=37 oct=47 lid=37 tim=4404683060 hv=3507756469 ad='6859f694'
BEGIN dbms_mview.refresh('MY_MV_FAST','F',atomic_refresh=>FALSE); END;
END OF STMT
PARSE #12:c=0,e=3152,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=4404683056
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #14 len=11 dep=1 uid=0 oct=44 lid=0 tim=4404695645 hv=1180858989 ad='0'
COMMIT WORK
END OF STMT
EXEC #14:c=0,e=1984,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=4404695641
=====================
PARSING IN CURSOR #13 len=25 dep=1 uid=0 oct=3 lid=0 tim=4404705600 hv=4010258057 ad='6b9bed84'
SELECT USER FROM SYS.DUAL
END OF STMT
EXEC #13:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=4404705595
FETCH #13:c=0,e=7,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,tim=4404715378
=====================
PARSING IN CURSOR #1 len=75 dep=1 uid=0 oct=3 lid=0 tim=4404717629 hv=1721407150 ad='6853a3c0'
SELECT 1 FROM SYS.SNAP$ WHERE SOWNER = :B2 AND VNAME = :B1 AND INSTSITE = 0
END OF STMT
EXEC #1:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=4404717621
FETCH #1:c=0,e=18,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=4404727741
=====================
PARSING IN CURSOR #6 len=146 dep=1 uid=0 oct=3 lid=0 tim=4404729860 hv=3349525290 ad='686c6f20'
SELECT SUBSTR(MLINK,2), TNAME, USLOG, SUBSTR(AUTO_FAST,1,1), BITAND(FLAG, :B3 ) FROM SYS.SNAP$ WHERE SOWNER = :B2 AND VNAME = :B1 AND INSTSITE = 0
END OF STMT
EXEC #6:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=4404729855
FETCH #6:c=0,e=95,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,tim=4404740437
=====================
PARSING IN CURSOR #11 len=66 dep=1 uid=0 oct=3 lid=0 tim=4404759553 hv=1493897133 ad='669e61d0'
SELECT LOCKID FROM DBMS_LOCK_ALLOCATED WHERE NAME = :B1 FOR UPDATE
END OF STMT
EXEC #11:c=0,e=16732,p=1,cr=2,cu=3,mis=0,r=0,dep=1,og=1,tim=4404759541
FETCH #11:c=0,e=7,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,tim=4404770457
=====================
PARSING IN CURSOR #16 len=85 dep=1 uid=0 oct=6 lid=0 tim=4404772742 hv=2672478400 ad='6bafd30c'
UPDATE DBMS_LOCK_ALLOCATED SET EXPIRATION = SYSDATE + (:B1 /86400) WHERE ROWID = :B2
END OF STMT
EXEC #16:c=0,e=116,p=0,cr=0,cu=1,mis=0,r=1,dep=1,og=1,tim=4404772737
XCTEND rlbk=0, rd_only=0
=====================
PARSING IN CURSOR #15 len=6 dep=1 uid=0 oct=44 lid=0 tim=4404785761 hv=255718823 ad='0'
COMMIT
END OF STMT
EXEC #15:c=0,e=2280,p=0,cr=0,cu=1,mis=0,r=0,dep=1,og=0,tim=4404785755
=====================
PARSING IN CURSOR #17 len=110 dep=1 uid=0 oct=3 lid=0 tim=4404796754 hv=2199897519 ad='6b904f4c'
SELECT NVL(DISABLED, 'F'), FLAG FROM SYSTEM.DEF$_DESTINATION WHERE DBLINK = NLS_UPPER(:B2 ) AND CATCHUP = :B1
END OF STMT
EXEC #17:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=4404796748
FETCH #17:c=0,e=32,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,tim=4404807339
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #25 len=193 dep=1 uid=0 oct=3 lid=0 tim=4404813051 hv=117824429 ad='66a9b450'
select last_delivered, last_enq_tid, last_seq, disabled, rowid, decode(apply_init, NULL, 1, 0), NVL(flag, '00000000') from system.def$_destination where dblink = :1 and catchup = :2
END OF STMT
PARSE #25:c=0,e=416,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4404813045
EXEC #25:c=0,e=33369,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4404858136
FETCH #25:c=0,e=40,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=4404860821
=====================
PARSING IN CURSOR #4 len=572 dep=1 uid=0 oct=6 lid=0 tim=4404863990 hv=2383384089 ad='687e8898'
update system.def$_destination set last_delivered = :1, last_enq_tid = :2, last_seq = :3, last_txn_count = :4, total_txn_count = total_txn_count + :5, total_prop_time_throughput = total_prop_time_throughput + :6, total_prop_time_latency = total_prop_time_latency + :7, to_communication_size = to_communication_size + :8, from_communication_size = from_communication_size + :9, spare1 = spare1 + :10, spare2 = spare2 + :11, spare3 = spare3 + :12, spare4 = spare4 + :13, last_error_number = :14, last_error_message = :15 where rowid = :16
END OF STMT
PARSE #4:c=0,e=645,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4404863986
EXEC #4:c=0,e=2202,p=0,cr=1,cu=2,mis=1,r=1,dep=1,og=4,tim=4404879476
XCTEND rlbk=0, rd_only=0
STAT #25 id=1 cnt=1 pid=0 pos=1 obj=4868 op='TABLE ACCESS BY INDEX ROWID DEF$_DESTINATION (cr=2 pr=0 pw=0 time=34 us)'
STAT #25 id=2 cnt=1 pid=1 pos=1 obj=4869 op='INDEX UNIQUE SCAN DEF$_DESTINATION_PRIMARY (cr=1 pr=0 pw=0 time=15 us)'
=====================
PARSING IN CURSOR #25 len=88 dep=1 uid=0 oct=3 lid=0 tim=4404890636 hv=889674504 ad='66867af0'
select disabled, NVL(flag, '00000000') from system.def$_destination where rowid = :1
END OF STMT
PARSE #25:c=0,e=504,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4404890632
EXEC #25:c=0,e=904,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4404904047
FETCH #25:c=0,e=33,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=4404906939
=====================
PARSING IN CURSOR #31 len=382 dep=1 uid=0 oct=3 lid=0 tim=4404910607 hv=2702941290 ad='6850904c'
select last_delivered, last_enq_tid, rowid from system.def$_destination dd where (dd.dblink != :1) and ( exists ( select 1 from system.def$_calldest cd where cd.dblink = dd.dblink and cd.catchup = dd.catchup) or exists ( select 1 from system.repcat$_repprop rp where rp.dblink = dd.dblink and rp.how = 1 and rp.extension_id = dd.catchup))
END OF STMT
PARSE #31:c=0,e=1111,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4404910603
=====================
PARSING IN CURSOR #37 len=42 dep=2 uid=0 oct=3 lid=0 tim=4404924831 hv=844002283 ad='684f96ec'
select condition from cdef$ where rowid=:1
END OF STMT
PARSE #37:c=0,e=317,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=4404924827
EXEC #37:c=0,e=824,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=4404938919
FETCH #37:c=0,e=7853,p=1,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=4404949568
STAT #37 id=1 cnt=1 pid=0 pos=1 obj=31 op='TABLE ACCESS BY USER ROWID CDEF$ (cr=1 pr=1 pw=0 time=7840 us)'
EXEC #31:c=31250,e=32684,p=1,cr=2,cu=0,mis=1,r=0,dep=1,og=4,tim=4404956056
FETCH #31:c=0,e=57,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=4404958905
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=0 op='UPDATE DEF$_DESTINATION (cr=1 pr=0 pw=0 time=154 us)'
STAT #4 id=2 cnt=1 pid=1 pos=1 obj=4868 op='TABLE ACCESS BY USER ROWID DEF$_DESTINATION (cr=1 pr=0 pw=0 time=21 us)'
=====================
PARSING IN CURSOR #4 len=71 dep=1 uid=0 oct=3 lid=0 tim=4404967245 hv=1829046 ad='668be770'
select last_delivered, last_enq_tid from system.def$_destination
END OF STMT
PARSE #4:c=0,e=645,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4404967240
EXEC #4:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4404980257
FETCH #4:c=0,e=53,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=4404982861
FETCH #4:c=0,e=9,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4404985847
EXEC #31:c=0,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4404988487
FETCH #31:c=0,e=73,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=4404991176
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #32 len=60 dep=1 uid=0 oct=3 lid=0 tim=4404996943 hv=2171804318 ad='668beba8'
select enq_tid from system.def$_aqcall where (cscn < :1)
END OF STMT
PARSE #32:c=0,e=503,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4404996939
=====================
PARSING IN CURSOR #10 len=53 dep=1 uid=0 oct=7 lid=0 tim=4405003240 hv=192289147 ad='66bc30a0'
delete from system.def$_lob where (enq_tid = :1)
END OF STMT
PARSE #10:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4405003236
=====================
PARSING IN CURSOR #34 len=58 dep=1 uid=0 oct=7 lid=0 tim=4405003351 hv=2424289869 ad='687a06ac'
delete from system.def$_calldest where (enq_tid = :1)
END OF STMT
PARSE #34:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4405003348
=====================
PARSING IN CURSOR #20 len=56 dep=1 uid=0 oct=7 lid=0 tim=4405003460 hv=2914327384 ad='6b923ebc'
delete from system.def$_aqcall where (enq_tid = :1)
END OF STMT
PARSE #20:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4405003457
EXEC #32:c=0,e=746,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405004305
FETCH #32:c=0,e=18,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=4405004367
STAT #31 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER (cr=6 pr=0 pw=0 time=136 us)'
STAT #31 id=2 cnt=0 pid=1 pos=1 obj=4868 op='TABLE ACCESS FULL DEF$_DESTINATION (cr=6 pr=0 pw=0 time=128 us)'
STAT #31 id=3 cnt=0 pid=1 pos=2 obj=4870 op='TABLE ACCESS BY INDEX ROWID DEF$_CALLDEST (cr=0 pr=0 pw=0 time=0 us)'
STAT #31 id=4 cnt=0 pid=3 pos=1 obj=4871 op='INDEX SKIP SCAN DEF$_CALLDEST_PRIMARY (cr=0 pr=0 pw=0 time=0 us)'
STAT #31 id=5 cnt=0 pid=1 pos=3 obj=7480 op='INDEX RANGE SCAN REPCAT$_REPPROP_DBLINK_HOW (cr=0 pr=0 pw=0 time=0 us)'
=====================
PARSING IN CURSOR #31 len=518 dep=1 uid=0 oct=6 lid=0 tim=4405005039 hv=4043529771 ad='66b278dc'
update system.def$_destination set last_delivered = :1, last_enq_tid = :2, last_seq = :3, last_txn_count = :4, total_txn_count = total_txn_count + :5, total_prop_time_throughput = total_prop_time_throughput + :6, total_prop_time_latency = total_prop_time_latency + :7, to_communication_size = to_communication_size + :8, from_communication_size = from_communication_size + :9, spare1 = spare1 + :10, spare2 = spare2 + :11, spare3 = spare3 + :12, spare4 = spare4 + :13 where rowid = :14
END OF STMT
PARSE #31:c=0,e=483,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405005036
EXEC #31:c=0,e=1109,p=0,cr=1,cu=2,mis=1,r=1,dep=1,og=4,tim=4405006252
XCTEND rlbk=0, rd_only=0
STAT #25 id=1 cnt=1 pid=0 pos=1 obj=4868 op='TABLE ACCESS BY USER ROWID DEF$_DESTINATION (cr=1 pr=0 pw=0 time=25 us)'
=====================
PARSING IN CURSOR #25 len=88 dep=1 uid=0 oct=3 lid=0 tim=4405006511 hv=889674504 ad='66867af0'
select disabled, NVL(flag, '00000000') from system.def$_destination where rowid = :1
END OF STMT
PARSE #25:c=0,e=49,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4405006506
EXEC #25:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4405006688
FETCH #25:c=0,e=26,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=4405006751
STAT #25 id=1 cnt=1 pid=0 pos=1 obj=4868 op='TABLE ACCESS BY USER ROWID DEF$_DESTINATION (cr=1 pr=0 pw=0 time=19 us)'
STAT #31 id=1 cnt=1 pid=0 pos=1 obj=0 op='UPDATE DEF$_DESTINATION (cr=1 pr=0 pw=0 time=115 us)'
STAT #31 id=2 cnt=1 pid=1 pos=1 obj=4868 op='TABLE ACCESS BY USER ROWID DEF$_DESTINATION (cr=1 pr=0 pw=0 time=17 us)'
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=4868 op='TABLE ACCESS FULL DEF$_DESTINATION (cr=3 pr=0 pw=0 time=49 us)'
STAT #32 id=1 cnt=0 pid=0 pos=1 obj=4855 op='INDEX RANGE SCAN DEF$_TRANORDER (cr=1 pr=0 pw=0 time=20 us)'
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #28 len=11 dep=1 uid=0 oct=44 lid=0 tim=4405007197 hv=1180858989 ad='0'
COMMIT WORK
END OF STMT
EXEC #28:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=4405007194
=====================
PARSING IN CURSOR #35 len=28 dep=1 uid=37 oct=90 lid=37 tim=4405007608 hv=3879155062 ad='6b83f660'
SET CONSTRAINTS ALL DEFERRED
END OF STMT
PARSE #35:c=0,e=209,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=4405007605
EXEC #35:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=4405007741
=====================
PARSING IN CURSOR #26 len=260 dep=1 uid=0 oct=3 lid=0 tim=4405007927 hv=2456569857 ad='66a1cf04'
SELECT '"' || CON$.NAME || '"' FROM CON$, USER$, CDEF$, OBJ$ WHERE USER$.NAME = :B2 AND OBJ$.NAME = :B1 AND OBJ$.OWNER# = USER$.USER# AND OBJ$.NAMESPACE = 1 AND CDEF$.OBJ# = OBJ$.OBJ# AND CDEF$.TYPE# = 2 AND BITAND(CDEF$.DEFER,1) = 1 AND CON$.CON# = CDEF$.CON#
END OF STMT
EXEC #26:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=4405007923
FETCH #26:c=0,e=60,p=0,cr=8,cu=0,mis=0,r=0,dep=1,og=1,tim=4405008064
=====================
PARSING IN CURSOR #24 len=107 dep=1 uid=0 oct=3 lid=0 tim=4405009235 hv=1749444386 ad='66afb79c'
SELECT KSPPSTVL FROM X$KSPPI X, X$KSPPCV Y WHERE (X.INDX = Y.INDX) AND KSPPINM = '_enable_refresh_schedule'
END OF STMT
EXEC #24:c=0,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=4405009232
FETCH #24:c=0,e=2942,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,tim=4405012255
=====================
PARSING IN CURSOR #22 len=111 dep=1 uid=0 oct=3 lid=0 tim=4405012385 hv=3294448573 ad='68699598'
SELECT UPPER(KSPPSTVL) FROM X$KSPPI X, X$KSPPCV Y WHERE (X.INDX = Y.INDX) AND KSPPINM = '_delay_index_maintain'
END OF STMT
EXEC #22:c=0,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=4405012381
FETCH #22:c=15625,e=3542,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,tim=4405016005
=====================
PARSING IN CURSOR #23 len=59 dep=1 uid=0 oct=3 lid=0 tim=4405016083 hv=3299590659 ad='68455d14'
SELECT VALUE FROM SYS.V$PARAMETER WHERE NAME = 'compatible'
END OF STMT
EXEC #23:c=0,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=4405016079
FETCH #23:c=0,e=3510,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,tim=4405019669
=====================
PARSING IN CURSOR #25 len=430 dep=1 uid=0 oct=3 lid=0 tim=4405020329 hv=860000844 ad='6687f0c0'
SELECT tname, snapid, flag, flag2, status, master_version, tables, lobmaskvec, query_len, mas_roll_seg, mlink, snapshot, auto_fun, uslog, NVL(rscn, 0), refhnt, objflag, auto_fast, sna_type_oid, sna_type_hashcode, sna_type_owner, sna_type_name, mas_type_oid, mas_type_hashcode, mas_type_owner, mas_type_name, syn_count FROM sys.snap$ WHERE sowner = :1 and vname = :2 and instsite = :3
END OF STMT
PARSE #25:c=0,e=514,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405020326
EXEC #25:c=0,e=1070,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405021496
FETCH #25:c=0,e=37,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=4405021571
STAT #25 id=1 cnt=1 pid=0 pos=1 obj=212 op='TABLE ACCESS BY INDEX ROWID SNAP$ (cr=2 pr=0 pw=0 time=31 us)'
STAT #25 id=2 cnt=1 pid=1 pos=1 obj=217 op='INDEX UNIQUE SCAN I_SNAP1 (cr=1 pr=0 pw=0 time=16 us)'
=====================
PARSING IN CURSOR #25 len=265 dep=1 uid=0 oct=3 lid=0 tim=4405022161 hv=29191747 ad='66b57bb0'
SELECT masobj#, masflag, mowner, master, lastsuccess, snaptime, sysdate, loadertime, sysdate, NVL(refscn, 0), fcmaskvec, ejmaskvec, sub_handle, change_view FROM sys.snap_reftime$ WHERE sowner = :1 AND vname = :2 AND instsite = :3 ORDER BY tablenum
END OF STMT
PARSE #25:c=0,e=443,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405022157
EXEC #25:c=0,e=853,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405023101
FETCH #25:c=0,e=37,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=4405023173
=====================
PARSING IN CURSOR #31 len=154 dep=1 uid=0 oct=6 lid=0 tim=4405023593 hv=2947295090 ad='68530be8'
update sys.sumpartlog$ s set s.timestamp = :1, s.scn = :2 where s.bo# = :3 and s.timestamp >= to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS')
END OF STMT
PARSE #31:c=0,e=322,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405023590
EXEC #31:c=0,e=719,p=0,cr=1,cu=0,mis=1,r=0,dep=1,og=4,tim=4405024399
STAT #31 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE SUMPARTLOG$ (cr=1 pr=0 pw=0 time=19 us)'
STAT #31 id=2 cnt=0 pid=1 pos=1 obj=154 op='TABLE ACCESS BY INDEX ROWID SUMPARTLOG$ (cr=1 pr=0 pw=0 time=14 us)'
STAT #31 id=3 cnt=0 pid=2 pos=1 obj=155 op='INDEX RANGE SCAN I_SUMPARTLOG$ (cr=1 pr=0 pw=0 time=11 us)'
=====================
PARSING IN CURSOR #4 len=126 dep=1 uid=0 oct=3 lid=0 tim=4405024974 hv=2198721921 ad='66a9b9e0'
select 1 from sys.cdc_change_tables$ where source_schema_name = :1 and source_table_name = :2 and bitand(mvl_flag, 128)=128
END OF STMT
PARSE #4:c=0,e=396,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405024970
EXEC #4:c=0,e=504,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405025680
FETCH #4:c=0,e=30,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=4405025743
STAT #4 id=1 cnt=0 pid=0 pos=1 obj=524 op='TABLE ACCESS FULL CDC_CHANGE_TABLES$ (cr=3 pr=0 pw=0 time=30 us)'
=====================
PARSING IN CURSOR #36 len=147 dep=1 uid=0 oct=3 lid=0 tim=4405026179 hv=1231457410 ad='6848e26c'
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
END OF STMT
PARSE #36:c=0,e=328,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405026175
EXEC #36:c=0,e=683,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405026951
FETCH #36:c=0,e=36,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=4405027021
STAT #36 id=1 cnt=1 pid=0 pos=1 obj=165 op='TABLE ACCESS CLUSTER MLOG$ (cr=2 pr=0 pw=0 time=32 us)'
STAT #36 id=2 cnt=1 pid=1 pos=1 obj=164 op='INDEX UNIQUE SCAN I_MLOG# (cr=1 pr=0 pw=0 time=11 us)'
=====================
PARSING IN CURSOR #21 len=158 dep=1 uid=0 oct=3 lid=0 tim=4405027505 hv=1221548236 ad='6b9e8f24'
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
END OF STMT
PARSE #21:c=0,e=331,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405027502
EXEC #21:c=0,e=829,p=0,cr=4,cu=2,mis=1,r=0,dep=1,og=4,tim=4405028423
FETCH #21:c=0,e=33,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=4405028493
STAT #21 id=1 cnt=1 pid=0 pos=1 obj=0 op='FOR UPDATE (cr=7 pr=0 pw=0 time=166 us)'
STAT #21 id=2 cnt=2 pid=1 pos=1 obj=165 op='TABLE ACCESS CLUSTER MLOG$ (cr=7 pr=0 pw=0 time=78 us)'
STAT #21 id=3 cnt=2 pid=2 pos=1 obj=164 op='INDEX UNIQUE SCAN I_MLOG# (cr=2 pr=0 pw=0 time=16 us)'
=====================
PARSING IN CURSOR #32 len=128 dep=1 uid=0 oct=6 lid=0 tim=4405029090 hv=3900209593 ad='66bc0e2c'
update "PM"."MLOG$_MY_SOURCE_TAB" set snaptime$$ = :1 where snaptime$$ > to_date('2100-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS')
END OF STMT
PARSE #32:c=0,e=404,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405029087
EXEC #32:c=15625,e=3823,p=0,cr=3,cu=0,mis=1,r=0,dep=1,og=4,tim=4405033015
STAT #32 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE MLOG$_MY_SOURCE_TAB (cr=3 pr=0 pw=0 time=38 us)'
STAT #32 id=2 cnt=0 pid=1 pos=1 obj=48300 op='TABLE ACCESS FULL MLOG$_MY_SOURCE_TAB (cr=3 pr=0 pw=0 time=33 us)'
=====================
PARSING IN CURSOR #32 len=82 dep=1 uid=0 oct=6 lid=0 tim=4405033528 hv=974917559 ad='66a9c860'
update sys.mlog$ set youngest = :1, yscn = :2 where mowner = :3 and master = :4
END OF STMT
PARSE #32:c=0,e=349,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405033524
EXEC #32:c=0,e=832,p=0,cr=3,cu=1,mis=1,r=1,dep=1,og=4,tim=4405034448
STAT #32 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE MLOG$ (cr=3 pr=0 pw=0 time=118 us)'
STAT #32 id=2 cnt=1 pid=1 pos=1 obj=165 op='TABLE ACCESS CLUSTER MLOG$ (cr=3 pr=0 pw=0 time=48 us)'
STAT #32 id=3 cnt=1 pid=2 pos=1 obj=164 op='INDEX UNIQUE SCAN I_MLOG# (cr=1 pr=0 pw=0 time=13 us)'
=====================
PARSING IN CURSOR #34 len=161 dep=1 uid=0 oct=3 lid=0 tim=4405035041 hv=3120606553 ad='6b8b7148'
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)
END OF STMT
PARSE #34:c=0,e=358,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405035037
EXEC #34:c=0,e=768,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405035897
FETCH #34:c=0,e=23,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=4405035957
STAT #34 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT GROUP BY (cr=1 pr=0 pw=0 time=38 us)'
STAT #34 id=2 cnt=0 pid=1 pos=1 obj=154 op='TABLE ACCESS BY INDEX ROWID SUMPARTLOG$ (cr=1 pr=0 pw=0 time=17 us)'
STAT #34 id=3 cnt=0 pid=2 pos=1 obj=155 op='INDEX RANGE SCAN I_SUMPARTLOG$ (cr=1 pr=0 pw=0 time=13 us)'
=====================
PARSING IN CURSOR #10 len=137 dep=1 uid=0 oct=3 lid=0 tim=4405036743 hv=561309863 ad='6bb1131c'
select snacol, mascol, maspos, colrole, snapos from sys.snap_colmap$ WHERE sowner = :1 AND vname = :2 AND tabnum = :3 AND instsite = :4
END OF STMT
PARSE #10:c=0,e=625,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405036739
EXEC #10:c=0,e=1007,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405037902
FETCH #10:c=0,e=36,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=4405037982
FETCH #10:c=0,e=8,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=4405038025
STAT #10 id=1 cnt=1 pid=0 pos=1 obj=226 op='TABLE ACCESS FULL SNAP_COLMAP$ (cr=4 pr=0 pw=0 time=33 us)'
=====================
PARSING IN CURSOR #29 len=126 dep=1 uid=0 oct=3 lid=0 tim=4405038170 hv=2198721921 ad='66a9b9e0'
select 1 from sys.cdc_change_tables$ where source_schema_name = :1 and source_table_name = :2 and bitand(mvl_flag, 128)=128
END OF STMT
PARSE #29:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4405038167
EXEC #29:c=0,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4405038272
FETCH #29:c=0,e=19,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=4405038314
STAT #29 id=1 cnt=0 pid=0 pos=1 obj=524 op='TABLE ACCESS FULL CDC_CHANGE_TABLES$ (cr=3 pr=0 pw=0 time=19 us)'
=====================
PARSING IN CURSOR #35 len=147 dep=1 uid=0 oct=3 lid=0 tim=4405038437 hv=1231457410 ad='6848e26c'
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
END OF STMT
PARSE #35:c=0,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4405038433
EXEC #35:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4405038549
FETCH #35:c=0,e=30,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=4405038602
STAT #35 id=1 cnt=1 pid=0 pos=1 obj=165 op='TABLE ACCESS CLUSTER MLOG$ (cr=2 pr=0 pw=0 time=26 us)'
STAT #35 id=2 cnt=1 pid=1 pos=1 obj=164 op='INDEX UNIQUE SCAN I_MLOG# (cr=1 pr=0 pw=0 time=11 us)'
=====================
PARSING IN CURSOR #31 len=147 dep=1 uid=0 oct=3 lid=0 tim=4405038733 hv=1231457410 ad='6848e26c'
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
END OF STMT
PARSE #31:c=0,e=16,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4405038729
EXEC #31:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4405038836
FETCH #31:c=0,e=19,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=4405038878
STAT #31 id=1 cnt=1 pid=0 pos=1 obj=165 op='TABLE ACCESS CLUSTER MLOG$ (cr=2 pr=0 pw=0 time=17 us)'
STAT #31 id=2 cnt=1 pid=1 pos=1 obj=164 op='INDEX UNIQUE SCAN I_MLOG# (cr=1 pr=0 pw=0 time=7 us)'
=====================
PARSING IN CURSOR #4 len=106 dep=1 uid=0 oct=3 lid=0 tim=4405039536 hv=2800037168 ad='685e30ec'
SELECT o.obj# FROM sys.obj$ o WHERE o.owner# = :1 AND o.name = :2 AND o.type# = 2 AND o.namespace = 1
END OF STMT
PARSE #4:c=0,e=543,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405039532
EXEC #4:c=0,e=690,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405040362
FETCH #4:c=0,e=33,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=4405040431
=====================
PARSING IN CURSOR #36 len=177 dep=1 uid=0 oct=3 lid=0 tim=4405040895 hv=2167694731 ad='66862ae4'
SELECT change_table_name, mvl_flag, mvl_backcompat_view FROM sys.cdc_change_tables$ WHERE source_table_name = :1 AND source_schema_name = :2 AND bitand(mvl_flag, 128)=128
END OF STMT
PARSE #36:c=0,e=399,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405040892
EXEC #36:c=0,e=497,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405041481
FETCH #36:c=0,e=26,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=4405041540
STAT #36 id=1 cnt=0 pid=0 pos=1 obj=524 op='TABLE ACCESS FULL CDC_CHANGE_TABLES$ (cr=3 pr=0 pw=0 time=27 us)'
=====================
PARSING IN CURSOR #36 len=65 dep=1 uid=0 oct=3 lid=0 tim=4405041933 hv=3612717576 ad='687bf334'
SELECT log, flag FROM sys.mlog$ WHERE master = :1 AND mowner = :2
END OF STMT
PARSE #36:c=0,e=295,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405041930
EXEC #36:c=0,e=511,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405042531
FETCH #36:c=0,e=25,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=4405042588
STAT #36 id=1 cnt=1 pid=0 pos=1 obj=165 op='TABLE ACCESS CLUSTER MLOG$ (cr=2 pr=0 pw=0 time=26 us)'
STAT #36 id=2 cnt=1 pid=1 pos=1 obj=164 op='INDEX UNIQUE SCAN I_MLOG# (cr=1 pr=0 pw=0 time=9 us)'
=====================
PARSING IN CURSOR #21 len=47 dep=1 uid=0 oct=3 lid=0 tim=4405043064 hv=3653610141 ad='66bf8038'
select c.name from sys.con$ c where c.con# = :1
END OF STMT
PARSE #21:c=0,e=217,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405043061
EXEC #21:c=0,e=459,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405043608
FETCH #21:c=0,e=26,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=4405043667
STAT #21 id=1 cnt=1 pid=0 pos=1 obj=28 op='TABLE ACCESS BY INDEX ROWID CON$ (cr=3 pr=0 pw=0 time=28 us)'
STAT #21 id=2 cnt=1 pid=1 pos=1 obj=49 op='INDEX UNIQUE SCAN I_CON2 (cr=2 pr=0 pw=0 time=18 us)'
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=35 us)'
STAT #4 id=2 cnt=1 pid=1 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=21 us)'
=====================
PARSING IN CURSOR #37 len=145 dep=1 uid=0 oct=6 lid=0 tim=4405044484 hv=2512022117 ad='68493698'
update sys.snap$ set snapshot = :1, snaptime = :2, auto_date = :3, snapid = :4, error# = 0 where vname = :6 and sowner = :5 and instsite = :7
END OF STMT
PARSE #37:c=0,e=592,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405044481
EXEC #37:c=15625,e=1269,p=0,cr=1,cu=3,mis=1,r=1,dep=1,og=4,tim=4405045906
=====================
PARSING IN CURSOR #32 len=88 dep=1 uid=0 oct=3 lid=0 tim=4405046549 hv=2068908653 ad='6686f360'
select 1 from sumpartlog$ a where a.bo# = :1 and bitand(a.flags,5) != 5 and rownum = 1
END OF STMT
PARSE #32:c=0,e=411,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405046545
EXEC #32:c=0,e=640,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405047279
FETCH #32:c=0,e=17,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=4405047331
STAT #32 id=1 cnt=0 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=1 pr=0 pw=0 time=22 us)'
STAT #32 id=2 cnt=0 pid=1 pos=1 obj=154 op='TABLE ACCESS BY INDEX ROWID SUMPARTLOG$ (cr=1 pr=0 pw=0 time=18 us)'
STAT #32 id=3 cnt=0 pid=2 pos=1 obj=155 op='INDEX RANGE SCAN I_SUMPARTLOG$ (cr=1 pr=0 pw=0 time=13 us)'
=====================
PARSING IN CURSOR #20 len=84 dep=1 uid=0 oct=3 lid=0 tim=4405047861 hv=1183946329 ad='6ba3b830'
SELECT query_txt FROM sys.snap$ WHERE sowner = :1 and vname = :2 AND instsite = :3
END OF STMT
PARSE #20:c=0,e=376,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405047857
EXEC #20:c=0,e=566,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405048515
FETCH #20:c=0,e=27,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=4405048576
STAT #20 id=1 cnt=1 pid=0 pos=1 obj=212 op='TABLE ACCESS BY INDEX ROWID SNAP$ (cr=2 pr=0 pw=0 time=21 us)'
STAT #20 id=2 cnt=1 pid=1 pos=1 obj=217 op='INDEX UNIQUE SCAN I_SNAP1 (cr=1 pr=0 pw=0 time=10 us)'
=====================
PARSING IN CURSOR #34 len=513 dep=1 uid=0 oct=6 lid=0 tim=4405050115 hv=3318256875 ad='6b88311c'
update sum$ set containerobj#=:2,containertype=:3,containernam=:4,fullrefreshtim=:5,increfreshtim=:6,lastrefreshscn=:7,lastrefreshdate=:8,refreshmode=:9,pflags=:10,mflags=:11,numdetailtab=:12,numaggregates=:13,numkeycolumns=:14,numjoins=:15,numinlines=:16,sumtextlen=:17,sumtext=:18,fromoffset=:19,fromlen=:20,objcount=:21,metaversion=:22, xpflags=:23,numwhrnodes=:24, numhavnodes=:25, numqbnodes=:26, qbcmarker=:27, markerdty=:28, rw_mode=:29, rw_name=:30, dest_stmt=NULL, src_stmt=NULL, spare1=:31 where obj#=:1
END OF STMT
PARSE #34:c=0,e=682,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405050112
EXEC #34:c=0,e=1633,p=0,cr=1,cu=4,mis=1,r=1,dep=1,og=4,tim=4405051863
STAT #34 id=1 cnt=1 pid=0 pos=1 obj=0 op='UPDATE SUM$ (cr=1 pr=0 pw=0 time=164 us)'
STAT #34 id=2 cnt=1 pid=1 pos=1 obj=398 op='INDEX UNIQUE SCAN I_SUM$_1 (cr=1 pr=0 pw=0 time=12 us)'
=====================
PARSING IN CURSOR #29 len=260 dep=1 uid=0 oct=3 lid=0 tim=4405052940 hv=855276698 ad='6ba75ad4'
SELECT operation#, cols, sql_txt, tabnum, fcmaskvec, ejmaskvec, setnum FROM sys.snap_refop$ WHERE ((operation# >= 0 AND operation# <= 6) OR operation# IN (10, 12, 13)) AND sowner = :1 AND vname = :2 AND instsite = :3 ORDER BY tabnum, setnum, operation#
END OF STMT
PARSE #29:c=0,e=863,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405052935
EXEC #29:c=0,e=1543,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405054672
FETCH #29:c=0,e=90,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=4405054821
FETCH #29:c=0,e=12,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=4405054875
FETCH #29:c=0,e=11,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=4405054923
FETCH #29:c=0,e=10,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=4405054971
FETCH #29:c=0,e=10,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=4405055019
STAT #29 id=1 cnt=5 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=3 pr=0 pw=0 time=82 us)'
STAT #29 id=2 cnt=5 pid=1 pos=1 obj=224 op='TABLE ACCESS FULL SNAP_REFOP$ (cr=3 pr=0 pw=0 time=61 us)'
=====================
PARSING IN CURSOR #35 len=85 dep=1 uid=0 oct=3 lid=0 tim=4405055553 hv=1777820491 ad='684e9c80'
select 1 from sumdelta$ a where a.tableobj# = :1 and a.timestamp > :2 and rownum = 1
END OF STMT
PARSE #35:c=0,e=410,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405055550
EXEC #35:c=0,e=20466,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405076108
FETCH #35:c=0,e=17,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=4405076162
STAT #35 id=1 cnt=0 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=1 pr=0 pw=0 time=21 us)'
STAT #35 id=2 cnt=0 pid=1 pos=1 obj=158 op='INDEX RANGE SCAN I_SUMDELTA$ (cr=1 pr=0 pw=0 time=18 us)'
STAT #37 id=1 cnt=1 pid=0 pos=1 obj=0 op='UPDATE SNAP$ (cr=1 pr=0 pw=0 time=133 us)'
STAT #37 id=2 cnt=1 pid=1 pos=1 obj=217 op='INDEX UNIQUE SCAN I_SNAP1 (cr=1 pr=0 pw=0 time=11 us)'
=====================
PARSING IN CURSOR #37 len=118 dep=1 uid=0 oct=6 lid=0 tim=4405077165 hv=884348145 ad='685b985c'
UPDATE sys.snap$ SET status = :1, rscn = :2, refhnt = :3, flag = :4 WHERE vname = :5 AND sowner = :6 AND instsite = :7
END OF STMT
PARSE #37:c=15625,e=635,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405077161
EXEC #37:c=0,e=1161,p=0,cr=1,cu=2,mis=1,r=1,dep=1,og=4,tim=4405078466
STAT #37 id=1 cnt=1 pid=0 pos=1 obj=0 op='UPDATE SNAP$ (cr=1 pr=0 pw=0 time=85 us)'
STAT #37 id=2 cnt=1 pid=1 pos=1 obj=217 op='INDEX UNIQUE SCAN I_SNAP1 (cr=1 pr=0 pw=0 time=10 us)'
=====================
PARSING IN CURSOR #37 len=264 dep=1 uid=0 oct=6 lid=0 tim=4405079282 hv=2874142926 ad='6b867b98'
UPDATE sys.snap_reftime$ SET snaptime = :1, loadertime = :2, refscn = :3, fcmaskvec = :4, ejmaskvec = :5, masobj# = :6, sub_handle = :7, change_view = :8, mowner = :9, master = :10 WHERE sowner = :11 AND vname = :12 AND tablenum = :13 AND instsite = :14
END OF STMT
PARSE #37:c=0,e=689,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405079279
EXEC #37:c=0,e=1233,p=0,cr=1,cu=1,mis=1,r=1,dep=1,og=4,tim=4405080686
=====================
PARSING IN CURSOR #4 len=587 dep=1 uid=0 oct=3 lid=0 tim=4405082024 hv=2730186508 ad='6851ebd0'
select count(*), max(scn) from (select sp.BO#, sp.PMOPTYPE, sp.scn, sp.flags from sumpartlog$ sp, sumdep$ sd where sd.sumobj# = :1 and sd.p_obj# = sp.bo# group by sp.BO#, sp.PMOPTYPE, sp.scn, sp.flags minus select sp.BO#, sp.PMOPTYPE, sp.scn, sp.flags from sumpartlog$ sp where sp.bo# not in (select sk.DETAILOBJ# from sumkey$ sk where sk.sumobj# = :1 and sk.DETAILCOLFUNCTION in (2,3) ) and bitand(sp.flags, 2) != 2 and sp.PMOPTYPE in (2,3,5,7) group by sp.BO#, sp.PMOPTYPE, sp.scn, sp.flags) where scn > : 2
END OF STMT
PARSE #4:c=0,e=1234,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405082020
EXEC #4:c=0,e=2871,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405085003
FETCH #4:c=0,e=363,p=0,cr=6,cu=0,mis=0,r=1,dep=1,og=4,tim=4405085410
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=6 pr=0 pw=0 time=412 us)'
STAT #4 id=2 cnt=0 pid=1 pos=1 obj=0 op='VIEW (cr=6 pr=0 pw=0 time=398 us)'
STAT #4 id=3 cnt=0 pid=2 pos=1 obj=0 op='MINUS (cr=6 pr=0 pw=0 time=396 us)'
STAT #4 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT UNIQUE NOSORT (cr=3 pr=0 pw=0 time=78 us)'
STAT #4 id=5 cnt=0 pid=4 pos=1 obj=0 op='SORT GROUP BY (cr=3 pr=0 pw=0 time=71 us)'
STAT #4 id=6 cnt=0 pid=5 pos=1 obj=412 op='TABLE ACCESS BY INDEX ROWID SUMDEP$ (cr=3 pr=0 pw=0 time=51 us)'
STAT #4 id=7 cnt=1 pid=6 pos=1 obj=0 op='NESTED LOOPS (cr=3 pr=0 pw=0 time=36 us)'
STAT #4 id=8 cnt=0 pid=7 pos=1 obj=154 op='TABLE ACCESS FULL SUMPARTLOG$ (cr=3 pr=0 pw=0 time=30 us)'
STAT #4 id=9 cnt=0 pid=7 pos=2 obj=414 op='INDEX RANGE SCAN I_SUMDEP$_2 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=10 cnt=0 pid=3 pos=2 obj=0 op='SORT UNIQUE NOSORT (cr=3 pr=0 pw=0 time=313 us)'
STAT #4 id=11 cnt=0 pid=10 pos=1 obj=0 op='SORT GROUP BY (cr=3 pr=0 pw=0 time=308 us)'
STAT #4 id=12 cnt=0 pid=11 pos=1 obj=0 op='HASH JOIN ANTI (cr=3 pr=0 pw=0 time=296 us)'
STAT #4 id=13 cnt=0 pid=12 pos=1 obj=154 op='TABLE ACCESS FULL SUMPARTLOG$ (cr=3 pr=0 pw=0 time=36 us)'
STAT #4 id=14 cnt=0 pid=12 pos=2 obj=406 op='TABLE ACCESS FULL SUMKEY$ (cr=0 pr=0 pw=0 time=0 us)'
=====================
PARSING IN CURSOR #32 len=513 dep=1 uid=0 oct=6 lid=0 tim=4405085889 hv=3318256875 ad='6b88311c'
update sum$ set containerobj#=:2,containertype=:3,containernam=:4,fullrefreshtim=:5,increfreshtim=:6,lastrefreshscn=:7,lastrefreshdate=:8,refreshmode=:9,pflags=:10,mflags=:11,numdetailtab=:12,numaggregates=:13,numkeycolumns=:14,numjoins=:15,numinlines=:16,sumtextlen=:17,sumtext=:18,fromoffset=:19,fromlen=:20,objcount=:21,metaversion=:22, xpflags=:23,numwhrnodes=:24, numhavnodes=:25, numqbnodes=:26, qbcmarker=:27, markerdty=:28, rw_mode=:29, rw_name=:30, dest_stmt=NULL, src_stmt=NULL, spare1=:31 where obj#=:1
END OF STMT
PARSE #32:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4405085886
EXEC #32:c=0,e=205,p=0,cr=1,cu=3,mis=0,r=1,dep=1,og=4,tim=4405086201
STAT #32 id=1 cnt=1 pid=0 pos=1 obj=0 op='UPDATE SUM$ (cr=1 pr=0 pw=0 time=152 us)'
STAT #32 id=2 cnt=1 pid=1 pos=1 obj=398 op='INDEX UNIQUE SCAN I_SUM$_1 (cr=1 pr=0 pw=0 time=9 us)'
=====================
PARSING IN CURSOR #20 len=193 dep=1 uid=0 oct=6 lid=0 tim=4405087055 hv=1167793001 ad='6866e128'
update sumdetail$ set detailobjtype=:3, refreshscn=:5, detaileut=:6, spare4=:7, inline# =:8, dataless =:10, qbcid = :11 where sumobj#=:1 and detailobj#=:2 and detailalias=:4 and instance# =:9
END OF STMT
PARSE #20:c=0,e=732,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405087052
EXEC #20:c=0,e=1254,p=0,cr=3,cu=1,mis=1,r=1,dep=1,og=4,tim=4405088413
STAT #20 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE SUMDETAIL$ (cr=3 pr=0 pw=0 time=103 us)'
STAT #20 id=2 cnt=1 pid=1 pos=1 obj=399 op='TABLE ACCESS FULL SUMDETAIL$ (cr=3 pr=0 pw=0 time=43 us)'
=====================
PARSING IN CURSOR #10 len=125 dep=1 uid=0 oct=3 lid=0 tim=4405089031 hv=425663694 ad='66a25d38'
select min(s1.lastrefreshscn) from sum$ s1, sumdetail$ s2 where s1.obj#= s2.sumobj# and s2.detailobj# = :1 and s1.obj#!= :2
END OF STMT
PARSE #10:c=0,e=407,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405089027
EXEC #10:c=0,e=970,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405090090
FETCH #10:c=0,e=118,p=0,cr=6,cu=0,mis=0,r=0,dep=1,og=4,tim=4405090244
STAT #10 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=6 pr=0 pw=0 time=134 us)'
STAT #10 id=2 cnt=0 pid=1 pos=1 obj=399 op='TABLE ACCESS BY INDEX ROWID SUMDETAIL$ (cr=6 pr=0 pw=0 time=120 us)'
STAT #10 id=3 cnt=8 pid=2 pos=1 obj=0 op='NESTED LOOPS (cr=5 pr=0 pw=0 time=299 us)'
STAT #10 id=4 cnt=4 pid=3 pos=1 obj=393 op='TABLE ACCESS FULL SUM$ (cr=3 pr=0 pw=0 time=34 us)'
STAT #10 id=5 cnt=3 pid=3 pos=2 obj=400 op='INDEX RANGE SCAN I_SUMDETAIL$_1 (cr=2 pr=0 pw=0 time=32 us)'
=====================
PARSING IN CURSOR #29 len=47 dep=1 uid=0 oct=7 lid=0 tim=4405094918 hv=2752216163 ad='669c19ac'
delete from sumpartlog$ where bo#=:1 and scn<:2
END OF STMT
PARSE #29:c=0,e=1227,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405094913
EXEC #29:c=0,e=770,p=0,cr=1,cu=0,mis=1,r=0,dep=1,og=4,tim=4405095788
STAT #29 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE SUMPARTLOG$ (cr=1 pr=0 pw=0 time=26 us)'
STAT #29 id=2 cnt=0 pid=1 pos=1 obj=154 op='TABLE ACCESS BY INDEX ROWID SUMPARTLOG$ (cr=1 pr=0 pw=0 time=22 us)'
STAT #29 id=3 cnt=0 pid=2 pos=1 obj=155 op='INDEX RANGE SCAN I_SUMPARTLOG$ (cr=1 pr=0 pw=0 time=18 us)'
XCTEND rlbk=0, rd_only=0
=====================
PARSING IN CURSOR #35 len=513 dep=1 uid=0 oct=6 lid=0 tim=4405096177 hv=3318256875 ad='6b88311c'
update sum$ set containerobj#=:2,containertype=:3,containernam=:4,fullrefreshtim=:5,increfreshtim=:6,lastrefreshscn=:7,lastrefreshdate=:8,refreshmode=:9,pflags=:10,mflags=:11,numdetailtab=:12,numaggregates=:13,numkeycolumns=:14,numjoins=:15,numinlines=:16,sumtextlen=:17,sumtext=:18,fromoffset=:19,fromlen=:20,objcount=:21,metaversion=:22, xpflags=:23,numwhrnodes=:24, numhavnodes=:25, numqbnodes=:26, qbcmarker=:27, markerdty=:28, rw_mode=:29, rw_name=:30, dest_stmt=NULL, src_stmt=NULL, spare1=:31 where obj#=:1
END OF STMT
PARSE #35:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4405096173
EXEC #35:c=0,e=235,p=0,cr=1,cu=5,mis=0,r=1,dep=1,og=4,tim=4405096585
STAT #35 id=1 cnt=1 pid=0 pos=1 obj=0 op='UPDATE SUM$ (cr=1 pr=0 pw=0 time=185 us)'
STAT #35 id=2 cnt=1 pid=1 pos=1 obj=398 op='INDEX UNIQUE SCAN I_SUM$_1 (cr=1 pr=0 pw=0 time=9 us)'
=====================
PARSING IN CURSOR #36 len=126 dep=1 uid=0 oct=3 lid=0 tim=4405096788 hv=2198721921 ad='66a9b9e0'
select 1 from sys.cdc_change_tables$ where source_schema_name = :1 and source_table_name = :2 and bitand(mvl_flag, 128)=128
END OF STMT
PARSE #36:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4405096785
EXEC #36:c=0,e=19,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4405096892
FETCH #36:c=0,e=27,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=4405096943
STAT #36 id=1 cnt=0 pid=0 pos=1 obj=524 op='TABLE ACCESS FULL CDC_CHANGE_TABLES$ (cr=3 pr=0 pw=0 time=27 us)'
=====================
PARSING IN CURSOR #21 len=82 dep=1 uid=0 oct=3 lid=0 tim=4405097371 hv=4260063577 ad='686723c4'
select OLDEST_PK, flag from sys.mlog$ where mowner = :1 and master = :2 for update
END OF STMT
PARSE #21:c=0,e=334,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405097368
EXEC #21:c=0,e=759,p=0,cr=4,cu=3,mis=1,r=0,dep=1,og=4,tim=4405098216
FETCH #21:c=0,e=26,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=4405098280
STAT #21 id=1 cnt=1 pid=0 pos=1 obj=0 op='FOR UPDATE (cr=7 pr=0 pw=0 time=150 us)'
STAT #21 id=2 cnt=2 pid=1 pos=1 obj=165 op='TABLE ACCESS CLUSTER MLOG$ (cr=7 pr=0 pw=0 time=76 us)'
STAT #21 id=3 cnt=2 pid=2 pos=1 obj=164 op='INDEX UNIQUE SCAN I_MLOG# (cr=2 pr=0 pw=0 time=16 us)'
=====================
PARSING IN CURSOR #21 len=85 dep=1 uid=0 oct=6 lid=0 tim=4405098812 hv=546770187 ad='6846d298'
update sys.slog$ set snaptime = :1 where snapid = :2 and mowner = :3 and master = :4
END OF STMT
PARSE #21:c=0,e=387,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405098808
EXEC #21:c=0,e=802,p=0,cr=4,cu=5,mis=1,r=1,dep=1,og=4,tim=4405099699
STAT #21 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE SLOG$ (cr=4 pr=0 pw=0 time=173 us)'
STAT #21 id=2 cnt=1 pid=1 pos=1 obj=166 op='TABLE ACCESS CLUSTER SLOG$ (cr=4 pr=0 pw=0 time=52 us)'
STAT #21 id=3 cnt=1 pid=2 pos=1 obj=164 op='INDEX UNIQUE SCAN I_MLOG# (cr=1 pr=0 pw=0 time=10 us)'
=====================
PARSING IN CURSOR #4 len=126 dep=1 uid=0 oct=3 lid=0 tim=4405099918 hv=2198721921 ad='66a9b9e0'
select 1 from sys.cdc_change_tables$ where source_schema_name = :1 and source_table_name = :2 and bitand(mvl_flag, 128)=128
END OF STMT
PARSE #4:c=0,e=19,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4405099915
EXEC #4:c=0,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4405100021
FETCH #4:c=0,e=24,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=4405100068
STAT #4 id=1 cnt=0 pid=0 pos=1 obj=524 op='TABLE ACCESS FULL CDC_CHANGE_TABLES$ (cr=3 pr=0 pw=0 time=23 us)'
=====================
PARSING IN CURSOR #32 len=165 dep=1 uid=0 oct=3 lid=0 tim=4405101823 hv=153806713 ad='66b3315c'
select log, sysdate, youngest, youngest+1/86400, oldest, oldest_pk, oldest_oid, oldest_new, oldest_seq from sys.mlog$ where master = :2 and mowner = :1 for update
END OF STMT
PARSE #32:c=0,e=1660,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405101819
EXEC #32:c=0,e=1270,p=0,cr=4,cu=2,mis=1,r=0,dep=1,og=4,tim=4405103269
FETCH #32:c=0,e=23,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=4405103347
STAT #32 id=1 cnt=1 pid=0 pos=1 obj=0 op='FOR UPDATE (cr=6 pr=0 pw=0 time=165 us)'
STAT #32 id=2 cnt=2 pid=1 pos=1 obj=165 op='TABLE ACCESS CLUSTER MLOG$ (cr=6 pr=0 pw=0 time=79 us)'
STAT #32 id=3 cnt=2 pid=2 pos=1 obj=164 op='INDEX UNIQUE SCAN I_MLOG# (cr=2 pr=0 pw=0 time=20 us)'
=====================
PARSING IN CURSOR #32 len=83 dep=1 uid=0 oct=3 lid=0 tim=4405103961 hv=3452788871 ad='68718398'
select snaptime from sys.slog$ where master = :2 and mowner = :1 order by snaptime
END OF STMT
PARSE #32:c=0,e=468,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405103958
EXEC #32:c=0,e=843,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405104923
FETCH #32:c=0,e=42,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=4405105009
STAT #32 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=2 pr=0 pw=0 time=49 us)'
STAT #32 id=2 cnt=3 pid=1 pos=1 obj=166 op='TABLE ACCESS CLUSTER SLOG$ (cr=2 pr=0 pw=0 time=21 us)'
STAT #32 id=3 cnt=1 pid=2 pos=1 obj=164 op='INDEX UNIQUE SCAN I_MLOG# (cr=1 pr=0 pw=0 time=9 us)'
=====================
PARSING IN CURSOR #32 len=61 dep=1 uid=0 oct=7 lid=0 tim=4405106234 hv=2308340864 ad='687cb0b0'
delete from "PM"."MLOG$_MY_SOURCE_TAB" where snaptime$$ <= :1
END OF STMT
PARSE #32:c=0,e=1097,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405106230
EXEC #32:c=0,e=575,p=0,cr=3,cu=0,mis=1,r=0,dep=1,og=4,tim=4405106926
STAT #32 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE MLOG$_MY_SOURCE_TAB (cr=3 pr=0 pw=0 time=34 us)'
STAT #32 id=2 cnt=0 pid=1 pos=1 obj=48300 op='TABLE ACCESS FULL MLOG$_MY_SOURCE_TAB (cr=3 pr=0 pw=0 time=30 us)'
=====================
PARSING IN CURSOR #32 len=150 dep=1 uid=0 oct=6 lid=0 tim=4405107522 hv=4261414056 ad='68755f50'
update sys.mlog$ set oldest = :1, oldest_pk = :2, oldest_oid = :3, oldest_new = :4, youngest = :5, oldest_seq = :6 where master = :7 and mowner = :8
END OF STMT
PARSE #32:c=0,e=474,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=4405107519
EXEC #32:c=15625,e=1305,p=0,cr=4,cu=1,mis=1,r=1,dep=1,og=4,tim=4405108959
STAT #32 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE MLOG$ (cr=4 pr=0 pw=0 time=126 us)'
STAT #32 id=2 cnt=1 pid=1 pos=1 obj=165 op='TABLE ACCESS CLUSTER MLOG$ (cr=4 pr=0 pw=0 time=64 us)'
STAT #32 id=3 cnt=1 pid=2 pos=1 obj=164 op='INDEX UNIQUE SCAN I_MLOG# (cr=1 pr=0 pw=0 time=14 us)'
STAT #37 id=1 cnt=1 pid=0 pos=1 obj=0 op='UPDATE SNAP_REFTIME$ (cr=1 pr=0 pw=0 time=58 us)'
STAT #37 id=2 cnt=1 pid=1 pos=1 obj=221 op='INDEX UNIQUE SCAN I_SNAP_REFTIME1 (cr=1 pr=0 pw=0 time=9 us)'
STAT #25 id=1 cnt=1 pid=0 pos=1 obj=220 op='TABLE ACCESS BY INDEX ROWID SNAP_REFTIME$ (cr=2 pr=0 pw=0 time=33 us)'
STAT #25 id=2 cnt=1 pid=1 pos=1 obj=221 op='INDEX RANGE SCAN I_SNAP_REFTIME1 (cr=1 pr=0 pw=0 time=20 us)'
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #18 len=11 dep=1 uid=0 oct=44 lid=0 tim=4405120146 hv=1180858989 ad='0'
COMMIT WORK
END OF STMT
EXEC #18:c=0,e=10737,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=4405120137
EXEC #12:c=343750,e=426901,p=2,cr=147,cu=51,mis=0,r=1,dep=0,og=1,tim=4405120315

Thanks,

Fudong

Hemant K Chitale said...

The portion of the tracefile that you have posted is for a single MV refresh. What about the refresh of two MVs ?
(exec dbms_mview.refresh('MY_MV_COMPLETE,MY_DUMMY_MV','C',atomic_refresh=>FALSE); )
That is why I had observed "I found that the TRUNCATE would occur only if one MV was being refreshed. If 2 MVs were being refreshed, even if ATOMIC_REFRESH was set to FALSE, I saw both suffering DELETE and INSERTs."

Anonymous said...

You are right.
The reason I am interested in this post is that we ran into some strange thing recently.
We have a materialized view which is a pure join of four tables.
It happened sometimes the fast refresh created duplicated records.
Among the four tables, three of them are lookup tables. Updates only happened to one table. Only inserts happened to the three lookup table.
In this case, how oracle coordinates the fast refreshing?
How could the duplicates happen?
Our database is running in RAC.

This has troubled me for some time. Please let me know if you have some clue.

Thanks,


Fudong

Hemant K Chitale said...

The Refresh is still executed as a query joining those 4 tables. However, it also uses the MV Logs to identify rows that have changed (INSERT/UPDATE/DELETE) since the last refresh.
Essentially, it "merges" the information together into the target MV.

As you are using RAC, check to see if the Refresh job is running from both instances. It needs to run from only 1 instance but you could have it run from both instances at different times (so that even if one instance fails, the job can keep running from the second instance).

If you really do find that it creates duplicate rows, you might log an SR with Oracle Support.

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...

Hi

I have question about materialized view refresh and accessing the same materialized view while refresh is in progress.

I have two databases (Oracle 10gR2). DB_SOURCE has MyTable with MV logs created.
Aother DB is DB_TARGET has corrosponding materialized view called MyTable_MV . Structure of this MV is same as source table.
MYTABLE_MV never gets modifed in DB_TARGET

I am using MV for replication. Source table has daily around 4 Million rows modified.
MV is refreshed using DBlink.

My Refresh script looks like this:

BEGIN
DBMS_SNAPSHOT.REFRESH(
LIST => 'RPT01.MYTABLE_MV'
,METHOD => 'F'
,PUSH_DEFERRED_RPC => FALSE
,REFRESH_AFTER_ERRORS => FALSE
,PURGE_OPTION => 1
,PARALLELISM => 8
,HEAP_SIZE => 1
,ATOMIC_REFRESH => FALSE
,NESTED => FALSE);
END;
/


When this sript is running, user cannot access this table. Is there any way to avoid this?
Also is this the right method to refresh or there can be something done to improve performance.

Thanks in advance for your guidance.

- Prasad

Hemant K Chitale said...

Prasad,
You should be using DBMS_MVIEW.REFRESH and not using DBMS_SNAPSHOT anymore.

I do not undesrstand your comment "user cannot access this table". Why not ?

Anonymous said...

Nice story as for me. I'd like to read more about that theme.
By the way look at the design I've made myself Young escorts