Re: Anybody studied refresh mechanism of refresh group?
- From: amit bansal <amit.bansal82@xxxxxxxxx>
- To: Yong Huang <yong321@xxxxxxxxx>, oracle-l L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 10 May 2010 19:59:36 +0530
Yong,
Adding freelist back so as to reach wider audience and also to get response
from other's
Sql should be similar to below (depending on table/log table name)
SELECT /*+ */
"A2"."CO_ID","A2"."TMCODE","A2"."SPCODE","A2"."SNCODE","A2"."CS_SEQNO",
"A2"."CS_CHANNEL_NUM","A2"."CS_STAT_CHNG","A2"."CS_ON_CBB","A2"."CS_DATE_BILLED",
"A2"."CS_REQUEST","A2"."SN_CLASS","A2"."CS_OVW_SUBSCR","A2"."CS_SUBSCRIPT",
"A2"."CS_OVW_ACCESS","A2"."CS_OVW_ACC_PRD","A2"."CS_OVW_ACC_FIRST","A2"."CS_ACCESS",
"A2"."CS_PENDING_STATE","A2"."CS_CHANNEL_EXCL","A2"."CS_DIS_SUBSCR","A2"."CS_ADV_CHARGE",
"A2"."CS_SRV_TYPE","A2"."SUBPAYER","A2"."USGPAYER","A2"."ACCPAYER","A2"."CS_ENTDATE",
"A2"."CS_OVW_LAST","A2"."INSTALL_DATE","A2"."TRIAL_END_DATE","A2"."CS_ADV_CHARGE_END_DATE",
"A2"."PRM_VALUE_ID","A2"."CURRENCY","A2"."CS_ADV_CHARGE_CURRENCY",
"A2"."REC_VERSION","A2"."SRV_SUBTYPE"
FROM "*CONTR_SERVICES*" "A2",
(SELECT DISTINCT "A3"."CO_ID" "CO_ID","A3"."SNCODE"
"SNCODE","A3"."CS_SEQNO" "CS_SEQNO"
FROM "*SYSADM"."MLOG$_CONTR_SERVICES*" "A3"
WHERE "A3"."*SNAPTIME$$*">:1
AND "A3"."*DMLTYPE$$"<>'D*') "A1"
WHERE "A2"."CO_ID"="A1"."CO_ID"
AND "A2"."SNCODE"="A1"."SNCODE"
AND "A2"."CS_SEQNO"="A1"."CS_SEQNO";
Source -
http://sandeepredkar.blogspot.com/2008/09/materialized-view-fast-refresh-and.html
In above query you will see table name CONTR_SERVICES. MLOG$_CONTR_SERVICES
is the mview log table. You can see snaptime$$ (time) and DMLTYPE$$
indicating that <> D i.e inserts and updates. (These are columns in
mlog$_<table_name>)
Meanwhile I am not sure why you are not able to find this query in source
database.
Cheers
Amit
On Fri, May 7, 2010 at 2:18 AM, Yong Huang <yong321@xxxxxxxxx> wrote:
> Amit,
>
> In case you're still interested in this topic, I added some comments to
>
> http://yong321.freeshell.org/oranotes/RefreshGroup.txt
>
> Specifically the following:
>
> 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).
>
>
> Thank you for taking time to look into this.
>
> Best regards,
>
> Yong Huang
>
> --- On *Tue, 5/4/10, Yong Huang <yong321@xxxxxxxxx>* wrote:
>
>
> From: Yong Huang <yong321@xxxxxxxxx>
>
> Subject: Re: Anybody studied refresh mechanism of refresh group?
> To: "amit bansal" <amit.bansal82@xxxxxxxxx>
> Date: Tuesday, May 4, 2010, 4:56 PM
>
>
> Hi, Amit,
>
> Thanks for your message. I saved my SQL trace to the URL (shown in my last
> message). I don't see snaptime or snaptime$$. Those columns exist in these
> tables:
>
> SQL> select owner, table_name, column_name
> 2 from dba_tab_columns
> 3 where column_name like 'SNAPTIME%' and owner in ('SYS', 'YHUANG')
> 4 order by 1, 2, 3;
>
> OWNER TABLE_NAME COLUMN_NAME
> ------ -------------------- ------------------------------
> SYS EXU8SLOG SNAPTIME
> SYS EXU8SLOGU SNAPTIME
> SYS SLOG$ SNAPTIME
> SYS SNAP$ SNAPTIME
> SYS SNAP_LOGDEP$ SNAPTIME
> SYS SNAP_REFTIME$ SNAPTIME
> YHUANG MLOG$_A SNAPTIME$$
> YHUANG MLOG$_B SNAPTIME$$
> YHUANG MLOG$_C SNAPTIME$$
> YHUANG MLOG$_CHILD SNAPTIME$$
> YHUANG MLOG$_D SNAPTIME$$
> YHUANG MLOG$_PARENT SNAPTIME$$
> YHUANG MLOG$_UAS_USER SNAPTIME$$
>
> I agree that a query that only finds rows with greater than certain
> snaptime (or snaptime$$) makes perfect sense. Unfortunately, my SQL trace
> indicates they're NOT queried. It's strange. I enable SQL trace by first
> identifying the remote session in the remote DB and exec
> dbms_system.set_ev(sid, serial#, 10046, 4, '') against it. Then back to the
> source DB and refresh (either group or mview).
>
> Yong Huang
>
> --- On *Mon, 5/3/10, amit bansal <amit.bansal82@xxxxxxxxx>* wrote:
>
>
> From: amit bansal <amit.bansal82@xxxxxxxxx>
> Subject: Re: Anybody studied refresh mechanism of refresh group?
> To: yong321@xxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> Date: Monday, May 3, 2010, 1:22 AM
>
> 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
>
>
>
>
>
Other related posts:
