首页    技术博客    PHP教程    数据库技术    前端开发   HTML5    Nginx    php论坛
新用户注册 | 会员登录
加入会员，解锁专属内容
取消
热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

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

作者： | 来源：互联网 | 2023-10-12 20:31
参考文档：MaterializedViewRefresh:Locking,Performance,Monitoring(文档ID258252.1)HowtoMonit

参考文档&＃xff1a;Materialized View Refresh: Locking, Performance, Monitoring (文档 ID 258252.1)

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

1、名词解释&＃xff1a; 基表 指的是英文里面的Master Table和Master Materialized View&＃xff0c;并不只是只一个表&＃xff0c;而是创建MView的时候所需要用到的n个表或者是相关的上一级的MView。 MView 就是Materialized View了&＃xff0c;物化视图。 源数据库端 Master Site和Master Materialized View Site&＃xff0c;指的是基表所在的数据库 MView端 Materialized View Site&＃xff0c;MView所在的数据库

2、常见用法&＃xff1a;

例子&＃xff1a;

SQL>create materialized view log on table_tst;

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

SQL>exec dbms_mview.refresh(&＃39;table_tst&＃39;,method &＃61;> &＃39;Complete&＃39;);

SQL>exec dbms_mview.refresh(&＃39;table_tst&＃39;); 

SQL> declare jobid number;

begin

sys.dbms_job.submit(job &＃61;> jobid,

what &＃61;> &＃39;dbms_mview.refresh(&＃39;&＃39;table_tst&＃39;&＃39;);&＃39;,

next_date &＃61;> sysdate,

interval &＃61;> &＃39;sysdate&＃43;5/1440&＃39;);

commit;

end;

 /

其中

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

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

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

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

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


我们举个测试栗子&＃xff1a;

创建带有主键的表&＃xff08;不能用sys用户&＃xff0c;因为sys用户的表不能创建mv 日志&＃xff09;&＃xff1a;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$中多了一条新记录&＃xff1a;



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



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


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


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

刷新到物化视图&＃xff1a;
exec dbms_mview.refresh(&＃39;table_tst_mv&＃39;);

SQL> select count(*) from table_tst_mv;

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


此时物化视图已经刷新了&＃xff0c;在看下记录表&＃xff08;已经记录了现在youngest的时间&＃xff0c;最新时间&＃xff09;&＃xff1a;



同事sys.SNAP$ 里面也记录了最新RSCN&＃xff1a;





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

下面列举了MView在实际中的主要作用&＃xff1a;

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


3、物化视图相关的重要的表&＃xff1a;

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

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

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

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

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



4、物化视图刷新的方式&＃xff1a;

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

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


MView刷新的分类

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

完全刷新&＃xff08;complete refresh&＃xff09;

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

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

快速刷新&＃xff08;fast refresh&＃xff09;

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

 exec dbms_mview.refresh(&＃39;table_tst_mv&＃39;, &＃39;F&＃39;);

强制刷新&＃xff08;force refresh&＃xff09;

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


指定刷新方式

既然有那么多种的刷新方式那我们怎么指定他们呢&＃xff1f;

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

exec dbms_mview.refresh(&＃39;table_tst_mv&＃39;, &＃39;F&＃39;);

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

exec dbms_mview.refresh(&＃39;table_tst_mv&＃39;, &＃39;C&＃39;);

另外一种方法是直接执行

exec dbms_mview.refresh(&＃39;table_tst_mv&＃39;);

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

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


什么时候刷新

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

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

使用DBMS_JOB包来创建

-- 提交一个JOB用来刷新MView
USER&＃64;orcl> variablejob1number;
USER&＃64;orcl> execdbms_job.submit(:job1,&＃39;dbms_mview.refresh(&＃39;&＃39;"user"."table_tst_mv"&＃39;&＃39;);&＃39;,sysdate,&＃39;sysdate&＃43;1/24&＃39;);
PL/SQLproceduresuccessfullycompleted.
 
USER&＃64;orcl> commit;
Commitcomplete.
 
-- 查看一下结果
USER&＃64;orcl> selectJOB,NEXT_DATE,NEXT_SEC,INTERVAL,WHATfromuser_jobs;
       JOBNEXT_DATENEXT_SECINTERVALWHAT
---------- ------------------- ------------------------ ------------------------------ ----------------------------------------
        232009-01-1313:52:1413:52:14sysdate&＃43;1/24dbms_mview.refresh(&＃39;"user"."table_tst_mv"&＃39;);

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

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

没错&＃xff0c;还是REFRESH语句&＃xff0c;这个语句的用法还是挺多的&＃xff0c;下面列出这个语句的其他用法&＃xff1a;

[refresh......
         [startwithdate]
         [nextdate]
  ......
  ]
  • START WITH: 第一次刷新时间
  • NEXT: 刷新时间间隔

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

-- 创建一个MView&＃xff0c;并指定刷新时间为sysdate和刷新间隔为一个小时
creatematerializedviewmvt2 
refreshstartwithsysdate nextsysdate&＃43;1/24asselect* fromt;
Materializedviewcreated.

每两个小时刷新一次

CREATE MATERIALIZED VIEW mvt2
refresh complete
Start With Sysdate Next trunc(sysdate, &＃39;HH24&＃39;)&＃43;1/12
as 
select * from users where isbest&＃61;1;

start with指定第一次同步的时间&＃xff0c;next则是下次执行时间了

-- 现在我们看一下job
-- 请注意这里的WHAT那一栏中MView刷新的代码是不带刷新方式的&＃xff0c;也就是说按照创建时候的刷新方式进行刷新
USER&＃64;orcl> selectJOB,NEXT_DATE,NEXT_SEC,INTERVAL,WHATfromuser_jobs;
JOBNEXT_DATENEXT_SECINTERVALWHAT
--- ------------------- --------- ------------- ----------------------------------------
 212009-01-1313:20:2013:20:20sysdate&＃43;1/24dbms_refresh.refresh(&＃39;"USER"."MVT2"&＃39;);

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




推荐阅读
  • range

    游标的使用笔记

    本文介绍了游标的使用方法，并以一个水果供应商数据库为例进行了说明。首先创建了一个名为fruits的表，包含了水果的id、供应商id、名称和价格等字段。然后使用游标查询了水果的名称和价格，并将结果输出。最后对游标进行了关闭操作。通过本文可以了解到游标在数据库操作中的应用。 ... [详细]
  • const

    用SQL语句怎么把已存在的列加上IDENTITY(1,1)属性

    ALTERTABLE通过更改、添加、除去列和约束，或者通过启用或禁用约束和触发器来更改表的定义。语法ALTERTABLEtable{[ALTERCOLUMNcolu ... [详细]
  • io

    MyBatis多表查询与动态SQL使用

    MyBatis多表查询与动态SQL使用
    本文介绍了MyBatis多表查询与动态SQL的使用方法，包括一对一查询和一对多查询。同时还介绍了动态SQL的使用，包括if标签、trim标签、where标签、set标签和foreach标签的用法。文章还提供了相关的配置信息和示例代码。 ... [详细]
  • io

    SQL日志收缩及截断方法详解

    本文详细介绍了SQL日志收缩的方法，包括截断日志和删除不需要的旧日志记录。通过备份日志和使用DBCC SHRINKFILE命令可以实现日志的收缩。同时，还介绍了截断日志的原理和注意事项，包括不能截断事务日志的活动部分和MinLSN的确定方法。通过本文的方法，可以有效减小逻辑日志的大小，提高数据库的性能。 ... [详细]
  • io

    eclipse学习（第三章：ssh中的Hibernate）——11.Hibernate的缓存（2级缓存，get和load）

    eclipse学习（第三章：ssh中的Hibernate）——11.Hibernate的缓存（2级缓存，get和load）
    本文介绍了eclipse学习中的第三章内容，主要讲解了ssh中的Hibernate的缓存，包括2级缓存和get方法、load方法的区别。文章还涉及了项目实践和相关知识点的讲解。 ... [详细]
  • io

    图解redis的持久化存储机制RDB和AOF的原理和优缺点

    图解redis的持久化存储机制RDB和AOF的原理和优缺点
    本文通过图解的方式介绍了redis的持久化存储机制RDB和AOF的原理和优缺点。RDB是将redis内存中的数据保存为快照文件，恢复速度较快但不支持拉链式快照。AOF是将操作日志保存到磁盘，实时存储数据但恢复速度较慢。文章详细分析了两种机制的优缺点，帮助读者更好地理解redis的持久化存储策略。 ... [详细]
  • substring

    Java String与StringBuffer的区别及其应用场景

    Java String与StringBuffer的区别及其应用场景
    本文主要介绍了Java中String和StringBuffer的区别，String是不可变的，而StringBuffer是可变的。StringBuffer在进行字符串处理时不生成新的对象，内存使用上要优于String类。因此，在需要频繁对字符串进行修改的情况下，使用StringBuffer更加适合。同时，文章还介绍了String和StringBuffer的应用场景。 ... [详细]
  • text

    VB.NET在线急等问题解决方法，如何统计数据库字段下的数据并显示在文本框里？

    本文介绍了一个在线急等问题解决方法，即如何统计数据库中某个字段下的所有数据，并将结果显示在文本框里。作者提到了自己是一个菜鸟，希望能够得到帮助。作者使用的是ACCESS数据库，并且给出了一个例子，希望得到的结果是560。作者还提到自己已经尝试了使用"select sum(字段2) from 表名"的语句，得到的结果是650，但不知道如何得到560。希望能够得到解决方案。 ... [详细]
  • io

    Java中vector的使用详解

    本文详细介绍了Java中vector的使用方法和相关知识，包括vector类的功能、构造方法和使用注意事项。通过使用vector类，可以方便地实现动态数组的功能，并且可以随意插入不同类型的对象，进行查找、插入和删除操作。这篇文章对于需要频繁进行查找、插入和删除操作的情况下，使用vector类是一个很好的选择。 ... [详细]
  • io

    高质量SQL书写的30条建议

    高质量SQL书写的30条建议
    本文提供了30条关于优化SQL的建议，包括避免使用select *，使用具体字段，以及使用limit 1等。这些建议是基于实际开发经验总结出来的，旨在帮助读者优化SQL查询。 ... [详细]
  • instance

    问题2：重新命名或者移动数据文件、日志文件到新的位置

    本文讨论了在数据库打开和关闭状态下，重新命名或移动数据文件和日志文件的情况。针对性能和维护原因，需要将数据库文件移动到不同的磁盘上或重新分配到新的磁盘上的情况，以及在操作系统级别移动或重命名数据文件但未在数据库层进行重命名导致报错的情况。通过三个方面进行讨论。 ... [详细]
  • instance

    iOS数据库Sqlite的SQL语句分类和常见约束关键字

    本文介绍了iOS数据库Sqlite的SQL语句分类和常见约束关键字。SQL语句分为DDL、DML和DQL三种类型，其中DDL语句用于定义、删除和修改数据表，关键字包括create、drop和alter。常见约束关键字包括if not exists、if exists、primary key、autoincrement、not null和default。此外，还介绍了常见的数据库数据类型，包括integer、text和real。 ... [详细]
  • io

    Python SQLAlchemy库的使用方法详解

    Python SQLAlchemy库的使用方法详解
    本文详细介绍了Python中使用SQLAlchemy库的方法。首先对SQLAlchemy进行了简介，包括其定义、适用的数据库类型等。然后讨论了SQLAlchemy提供的两种主要使用模式，即SQL表达式语言和ORM。针对不同的需求，给出了选择哪种模式的建议。最后，介绍了连接数据库的方法，包括创建SQLAlchemy引擎和执行SQL语句的接口。 ... [详细]
  • text

    CSS3选择器的使用方法详解，提高Web开发效率和精准度

    CSS3选择器的使用方法详解，提高Web开发效率和精准度
    本文详细介绍了CSS3新增的选择器方法，包括属性选择器的使用。通过CSS3选择器，可以提高Web开发的效率和精准度，使得查找元素更加方便和快捷。同时，本文还对属性选择器的各种用法进行了详细解释，并给出了相应的代码示例。通过学习本文，读者可以更好地掌握CSS3选择器的使用方法，提升自己的Web开发能力。 ... [详细]
  • text

    PHP中的MySQL函数库及其常用函数介绍

    本文由编程笔记小编整理，介绍了PHP中的MySQL函数库及其常用函数，包括mysql_connect、mysql_error、mysql_select_db、mysql_query、mysql_affected_row、mysql_close等。希望对读者有一定的参考价值。 ... [详细]
author-avatar
Tags | 热门标签
RankList | 热门文章
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有