物化视图刷新原理与性能诊断

参考文档：Materialized View Refresh: Locking, Performance, Monitoring (文档 ID 258252.1)

How to Monitor the Progress of a Materialized View Refresh (MVIEW) (文档 ID 258021.1)

1、名词解释： 基表

指的是英文里面的Master Table和Master Materialized View，并不只是只一个表，而是创建MView的时候所需要用到的n个表或者是相关的上一级的MView。

MView

就是Materialized View了，物化视图。

源数据库端

Master Site和Master Materialized View Site，指的是基表所在的数据库

MView端

Materialized View Site，MView所在的数据库

2、常见用法：

例子：

SQL>create materialized view log on table_tst;

SQL>create materialized view table_tst [on prebuilt table]  refresh   fast as select * from table_tst@lnk_db_master; 

SQL>exec dbms_mview.refresh('table_tst',method => 'Complete');

SQL>exec dbms_mview.refresh('table_tst'); 

SQL> declare jobid number;

begin

sys.dbms_job.submit(job => jobid,

what => 'dbms_mview.refresh(''table_tst'');',

next_date => sysdate,

interval => 'sysdate+5/1440');

commit;

end;

 /

其中

第1步是在需要复制的主表(master table)上创建mv log。

第2步是在远程站点（想复制到的那个站点）上创建mv，注意，如果选择了选项on prebuild table的话，表示在已经存在的表上创建mv，需要已经存在的表的字段与select的字段必须要对应，这个已经存在的表中可以没有数据。

第3步是全同步，如果没有选择on prebuild table，这一步可以省略，因为默认创建mv的时候，就会全刷新

第4步是增量刷新，在完成全刷新以后的情况下，一般都只需要做增量刷新即可。

第5步是创建一个自动刷新的作业来进行刷新，如每5分钟刷新一次，这个操作也可以同crontab来代替。

我们举个测试栗子：

创建带有主键的表（不能用sys用户，因为sys用户的表不能创建mv 日志）：ORA-12010: 不能在 SYS 拥有的表上创建实体化视图日志。
create table table_tst (a int, b varchar2(50), constraint pk_tst primary key(a));
 
创建对应的MV名为
create materialized view table_tst_mv as select * from table_tst;

此时表SYS.SNAP$中多了一条新记录：



创建视图日志：
create materialized view log on table_tst;
此时表sys.MLOG$中多了一条新记录：



向表中插入数据：
insert into table_tst select rownum, object_name from all_objects;


检查当前表和物化视图里的数据是否一致：
select count(*) from table_tst
 COUNT(*)
----------
     85722

select count(*) from table_tst_mv
  COUNT(*)
----------
0

刷新到物化视图：
exec dbms_mview.refresh('table_tst_mv');

SQL> select count(*) from table_tst_mv;

  COUNT(*)
----------
      85722

此时物化视图已经刷新了，在看下记录表（已经记录了现在youngest的时间，最新时间）：

同事sys.SNAP$ 里面也记录了最新RSCN：

在实际应用的环境中表T和MView MVT并不是在同一个机器上，而是分散在两个以上的机器上，同时基表也可能不止一个，可能存在多个。

下面列举了MView在实际中的主要作用：

• 减轻网络负担：通过MV将数据从一个数据库分发到多个不同的数据库上，通过对多个数据库访问来减轻对单个数据库的网络负担。
• 搭建分发环境：通过从一个中央数据库将数据分发到多个节点数据库，达到分发数据的目的。
• 复制数据子集：MV可以进行行级/列级的筛选，这样可以复制需要的那一部分数据。
• 支持离线计算：MV不需要专用的数据库连接，用户可以按照自己的需求来复制所需要的那一部分数据。

3、物化视图相关的重要的表：

dba_mviews记录了远程站点上mv的数目与属性，需要在创建MV的站点上查询。

sys.mlog$ 则记录了主站点上的mv的log数目，如果一个master对应到多个站点，也只有一条记录，对应到dba_mview_logs视图，需要在主站点查询。

sys.slog$记录了主站点上已经注册成功的主表信息，如果一个主表被复制到多个站点，则对应多条记录，在主站点查询。

dba_snapshot_logs存放了mv的log日志，如果对应到多个站点，则每个站点都对应一条记录，因为远程站点的snapshot_id是不一样的。其实sys.mlog$与sys.slog$的关联就是组成dba_snapshot_logs的一个部分，通过查询dba_views可以看到其脚本。

dba_registered_snapshots记录了远程站点的注册信息，只记录注册成功的远程站点，通过snapshot_id可以与dba_snapshot_logs关联。

4、物化视图刷新的方式：

下面接着说说MView刷新这个事。

MView里面的数据是不会和基表保持实时的同步的，它只是基表在某时时间点（刷新的时间点）的一个一致性的数据的镜像，因此，要保持MView尽可能的和基表同步的话就需要我们定期的对MView进行刷新。

MView刷新的分类

Oracle支持三种种方式的刷新：完全刷新和快速刷新以及强制刷新。

完全刷新（complete refresh）

对一个MView进行全部刷新的时候差不多是将MView重建了，在进行MView全部刷新的时候会现将MView中现有的数据删除（版本在10G或以上）或者TRUNCATE（版本低于9i），然后在根据创建MView时候的查询生成数据插入到MView中。

对于多层的MView来说，当master MView全部刷新之后对应的下一级的MView也需要全部刷新，否则将会收到ORA-12034的错误。

快速刷新（fast refresh）

快速刷新是一种比完全刷新快的多的刷新方式，快速刷新只刷新自上次刷新以来修改的数据，因为快速刷新所要操作的数据量少，使用这种方法能大大的节省带宽.快速刷新要求在基表上面有MView Log。

 exec dbms_mview.refresh('table_tst_mv', 'F');

强制刷新（force refresh）

当进行强制刷新的时候系统会首先尝试进行快速刷新，如果快速刷新无法进行的时候系统将会进行完全刷新。其实就是一个快速刷新和完全刷新的结合体。

指定刷新方式

既然有那么多种的刷新方式那我们怎么指定他们呢？

在Oracle中有两种方法来制定所用的刷新方式，第一种在上面我们已经看过了，就是在执行刷新MView语句的时候制定刷新方式，比如说

exec dbms_mview.refresh('table_tst_mv', 'F');

用来指定对MView mvt进行快速刷新，将其中的”F”改成”C”就是指定对mvt进行完全刷新了。

exec dbms_mview.refresh('table_tst_mv', 'C');

另外一种方法是直接执行

exec dbms_mview.refresh('table_tst_mv');

就是不指定刷新的参数，这个时候MView的刷新方式将是根据创建时候由REFRESH语句指定的刷新方法来进行刷新了，REFRESH语句一共有下面几种使用方法

[refresh [fast | complete | force]
  ......
  ]

• FAST: 采用增量刷新，只刷新自上次刷新以后进行的修改
• COMPLETE: 对整个实体化视图进行完全的刷新
• FORCE(默认): Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用Fast方式，否则采用Complete的方式，Force选项是默认选项

什么时候刷新

现在怎么刷新的问题解决了，接下来就是考虑我们什么时候进行刷新了。

我们从上面已经知道我们需要定期的对MView进行刷新以保证基表和MView的数据同步，这个定期的方法就是使用job，同样我们有两种方法来创建刷新MView的job。

使用DBMS_JOB包来创建

-- 提交一个JOB用来刷新MView
USER @ orcl >  variable   job1   number ;
USER @ orcl >  exec dbms_job . submit ( : job1 , ' dbms_mview.refresh( '' "user"."table_tst_mv" '' ); ' , sysdate , ' sysdate+1/24 ' ) ;
PL / SQL   procedure   successfully   completed .
 
USER @ orcl >  commit ;
Commit   complete .
 
-- 查看一下结果
USER @ orcl >  select   JOB , NEXT_DATE , NEXT_SEC , INTERVAL , WHAT   from user_jobs ;
        JOB   NEXT_DATE             NEXT_SEC                   INTERVAL                         WHAT
-------- -- ------------------- ------------------------ ------------------------------ ----------------------------------------
         23   2009 - 01 - 13   13 : 52 : 14   13 : 52 : 14                   sysdate + 1 / 24                     dbms_mview . refresh ( ' "user"."table_tst_mv" ' ) ;

使用这种方法相对下面的方法来说不同之处在于这种方法可以自己指定刷新的语句，这样灵活性相对高一些。

在创建MView的时候指定REFRESH语句

没错，还是REFRESH语句，这个语句的用法还是挺多的，下面列出这个语句的其他用法：

[ refresh  ......
         [ start   with   date ]
         [ next   date ]
  ......
  ]

• START WITH: 第一次刷新时间
• NEXT: 刷新时间间隔

说明：指定上面两个选项的任意一个都将会在系统中产生一个新的JOB，用来对所建立的MV进行刷新，这个JOB可以从DBA_JOBS查到，同时删除MV之后该JOB也会被删除。下面我们看一个例子：

-- 创建一个MView，并指定刷新时间为sysdate和刷新间隔为一个小时
create   materialized   view   mvt2 
refresh   start   with   sysdate  next   sysdate + 1 / 24   as   select  *  from   t ;
Materialized   view   created .

每两个小时刷新一次

CREATE MATERIALIZED VIEW mvt2
refresh complete
Start With Sysdate Next trunc(sysdate, 'HH24')+1/12
as 
select * from users where isbest=1;

start with指定第一次同步的时间，next则是下次执行时间了
 
-- 现在我们看一下job
-- 请注意这里的WHAT那一栏中MView刷新的代码是不带刷新方式的，也就是说按照创建时候的刷新方式进行刷新
USER @ orcl >  select   JOB , NEXT_DATE , NEXT_SEC , INTERVAL , WHAT   from user_jobs ;
JOB   NEXT_DATE             NEXT_SEC    INTERVAL        WHAT
- -- ------------------- --------- ------------- ----------------------------------------
  21   2009 - 01 - 13   13 : 20 : 20   13 : 20 : 20    sysdate + 1 / 24   dbms_refresh . refresh ( ' "USER"."MVT2" ' ) ;

另外这里所说的JOB定时刷新只是针对于单个MView来说的，而对于存在多个MView要刷新的时候我们就要开始考虑刷新组了，这个部分以后再慢慢说明。