Re: Anybody studied refresh mechanism of refresh group?
- From: amit bansal <amit.bansal82@xxxxxxxxx>
- To: yong321@xxxxxxxxx
- Date: Mon, 3 May 2010 11:52:38 +0530
If you run a query on master site, you will find that mview refresh session
will run query select ... from mlog$ wheer snaptime$$ > :1
:1 -> Bind contains the date..
I think this bind will remain same for mviews in refresh group..
-Amit
On Sat, May 1, 2010 at 8:10 AM, Yong Huang <yong321@xxxxxxxxx> wrote:
> 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
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
