Re: materialized view question
- From: "Dba DBA" <oracledbaquestions@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 21 Aug 2008 13:03:30 -0400
I am in RAC, How do you determine which nodes the materialized view refresh
will take place on? We need about 15 rollups. If I was doing a package, it
would run from schedule and attached to a service.
On Thu, Aug 21, 2008 at 12:39 PM, Jared Still <jkstill@xxxxxxxxx> wrote:
> On Thu, Aug 21, 2008 at 9:08 AM, Dba DBA <oracledbaquestions@xxxxxxxxx>wrote:
>
>> 1. can you set the materialized view to rollup using parallel query so it
>> runs faster ?
>>
>
> Haven't tried it myself, but I can't imagine why not.
>
>
>>
>> 2. how does the materialized view know which records are new and need to
>> be added to the rollup? Doesn't it add some kind of loggng table? doesn't
>> cause overhead ?
>>
>
>
> There is a logging table on the source.
> Yes there is overhead, but I personally have not found this to be a
> problem, with caveats.
> As of 9i (or was it 8i?) the triggers to populate the logs are integrated
> into the kernel,
> and are quite fast.
>
> When creating a MV log, there's an index you should create that is not
> created by default.
>
> eg.
>
> create index SAPR3."MLOG$_QPGT_IDX1"
> on SAPR3."MLOG$_QPGT" (SNAPTIME$$)
> pctfree 5
> tablespace sap_mv_medium
> /
>
> Doing so will greatly reduce the IO on the MV logs.
> There's an ML note on this somewhere I believe.
>
> Refreshes are tracked by SCN.
>
> Here's how to see the current SCN of the MV Log on the source db:
>
> select o.owner, o.object_name, t.spare3
> from sys.tab$ t, dba_objects o
> where t.obj# = o.object_id
> and o.object_type = 'TABLE'
> and nvl(t.spare3,0) > 0
> order by 1
> /
>
> And here's how to see the current SCN of the MV on the Target:
>
> select o.owner, o.object_name, s.lastrefreshscn
> from sys.sum$ s, dba_objects o
> where s.obj# = o.object_id
> order by 1,2
> /
>
> An example:
>
> Source DB:
>
> OWNER OBJECT NAME SPARE3
> ---------- ------------------------------ ----------
> SAPR3 AFIH 6174112193
> SAPR3 AFKO 6174112194
> SAPR3 AFVC 6174112142
> SAPR3 AFVV 6174112143
> SAPR3 AUSP 6174087371
>
>
>
> Notice that the SCN's are all different as the updates have occurred at
> different
> times for each table.
>
> Target DB:
>
> OWNER OBJECT NAME LASTREFRESHSCN
> ---------- ------------------------------ --------------
> SAP_MV AFIH_MV 6174111583
> SAP_MV AFKO_MV 6174111583
> SAP_MV AFVC_MV 6174111583
> SAP_MV AFVV_MV 6174111583
> SAP_MV AUSP_MV 6174111583
>
>
> The SCN at the target is the current SCN at the source DB at the time of
> the last refresh.
> These MV's are refreshed as a group, so the SCN is the same for each.
>
> After the last registered MV is refreshed from a MV log, the entries
> earlier
> than the earliest SCN of a registered MV are removed from the log via
> DELETE.
>
> BTW, should you do any complete refreshes, be sure to google for
> atomic_refresh,
> as the DELETE vs. TRUNCATE table behavior changed in 10g.
>
> Another bit of trivia: the MV's are tracked internally with
> sys.slog$.snapid.
>
> The only place this column is exposed is not in MVIEW views, but in
> various SNAPSHOT views.
>
> OWNER TABLE NAME COLUMN
> DATA_TYPE
> ---------- ------------------------------ ------------------------------
> --------------------
> SYS ALL_REGISTERED_SNAPSHOTS SNAPSHOT_ID
> NUMBER
> ALL_SNAPSHOT_LOGS SNAPSHOT_ID
> NUMBER
> DBA_REGISTERED_SNAPSHOTS SNAPSHOT_ID
> NUMBER
> DBA_SNAPSHOT_LOGS SNAPSHOT_ID
> NUMBER
> REG_SNAP$ SNAPSHOT_ID
> NUMBER
> USER_REGISTERED_SNAPSHOTS SNAPSHOT_ID
> NUMBER
> USER_SNAPSHOT_LOGS SNAPSHOT_ID
> NUMBER
>
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>
>
Other related posts:
