Anybody studied refresh mechanism of refresh group?
Date: Fri, 30 Apr 2010 19:40:06 -0700 (PDT)
Message-ID: <350447.7615.qm_at_web80601.mail.mud.yahoo.com>
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 at http://yong321.freeshell.org/oranotes/RefreshGroup.txt 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?
Yong Huang
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 30 2010 - 21:40:06 CDT