阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

Oracle 物化视图快速刷新对性能的影响

174次阅读
没有评论

共计 10476 个字符,预计需要花费 27 分钟才能阅读完成。

Oracle 一个表上存在物化视图日志和基于物化视图日志快速刷新的物化视图,如果对这个表进行 DML 操作,则 Redolog 产生量将翻数倍,并且执行时间加长,影响并发操作。

下面主要通过在 Redolog 产生量和执行时间上做对比:
DB Version:12.1.0.2.0
OS:CentOS 6.6

[oracle@ct6603 ~]$ sqlplus system/system

SQL*Plus: Release 12.1.0.2.0 Production on Sat Nov 5 17:11:31 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sat Nov 05 2016 17:11:12 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
# 建测试表
SQL> create table tb_rowid tablespace users as select * from dba_objects;

Table created.
# 记录时间
SQL> set timing on
# 设定自动提交
SQL> set autocommit on
# 跟踪统计信息
SQL> set autotrace on stat

# 表 tb_rowid 上无物化视图日志
# 插入 9999 笔记录,Redolog 产生量 1249324,耗时 00:00:00.21
SQL> insert into tb_rowid select * from tb_rowid where rownum<10000;

9999 rows created.

Commit complete.
Elapsed: 00:00:00.21

Statistics
———————————————————-
        42  recursive calls
      1105  db block gets
        497  consistent gets
        508  physical reads
    1249324  redo size
        859  bytes sent via SQL*Net to client
        870  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      9999  rows processed
# 插入 9999 笔记录,Redolog 产生量 1248532,耗时 00:00:00.17
SQL> insert into tb_rowid select * from tb_rowid where rownum<10000;

9999 rows created.

Commit complete.
Elapsed: 00:00:00.17

Statistics
———————————————————-
          4  recursive calls
      1087  db block gets
        324  consistent gets
        245  physical reads
    1248532  redo size
        861  bytes sent via SQL*Net to client
        870  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      9999  rows processed
# 删除 9999 笔记录,Redolog 产生量 4147948,耗时 00:00:00.50
SQL> delete tb_rowid where rownum<10000;

9999 rows deleted.

Commit complete.
Elapsed: 00:00:00.50

Statistics
———————————————————-
          9  recursive calls
      11277  db block gets
        225  consistent gets
        276  physical reads
    4147948  redo size
        861  bytes sent via SQL*Net to client
        842  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      9999  rows processed
# 删除 9999 笔记录,Redolog 产生量 4164704,耗时 00:00:00.60
SQL> delete tb_rowid where rownum<10000;

9999 rows deleted.

Commit complete.
Elapsed: 00:00:00.60

Statistics
———————————————————-
          3  recursive calls
      11293  db block gets
        319  consistent gets
        104  physical reads
    4164704  redo size
        863  bytes sent via SQL*Net to client
        842  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      9999  rows processed
# 更新 9999 笔记录,Redolog 产生量 2725824,耗时 00:00:00.48
SQL> update tb_rowid set object_id=1 where rownum<10000;

9999 rows updated.

Commit complete.
Elapsed: 00:00:00.48

Statistics
———————————————————-
          8  recursive calls
      10233  db block gets
        548  consistent gets
        145  physical reads
    2725824  redo size
        863  bytes sent via SQL*Net to client
        858  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      9999  rows processed
# 更新 9999 笔记录,Redolog 产生量 957056,耗时 00:00:00.13
SQL> update tb_rowid set object_id=2 where rownum<10000;

9999 rows updated.

Commit complete.
Elapsed: 00:00:00.13

Statistics
———————————————————-
          8  recursive calls
        294  db block gets
        548  consistent gets
          0  physical reads
    957056  redo size
        864  bytes sent via SQL*Net to client
        858  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      9999  rows processed
# 更新 9999 笔记录,Redolog 产生量 961224,耗时 00:00:00.14
SQL> update tb_rowid set object_id=2 where rownum<10000;

9999 rows updated.

Commit complete.
Elapsed: 00:00:00.14

Statistics
———————————————————-
          1  recursive calls
        294  db block gets
        489  consistent gets
          0  physical reads
    961224  redo size
        864  bytes sent via SQL*Net to client
        858  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      9999  rows processed

# 表 tb_rowid 上有物化视图日志
# 建物化视图日志
SQL> create  materialized view log on tb_rowid with rowid including new values;

Materialized view log created.

Elapsed: 00:00:00.34

# 插入 9999 笔记录,Redolog 产生量 10905808,耗时 00:00:03.73
SQL> insert into tb_rowid select * from tb_rowid where rownum<10000;

9999 rows created.

Commit complete.
Elapsed: 00:00:03.73

Statistics
———————————————————-
        176  recursive calls
      43316  db block gets
      1227  consistent gets
        104  physical reads
  10905808  redo size
        862  bytes sent via SQL*Net to client
        870  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
        14  sorts (memory)
          0  sorts (disk)
      9999  rows processed

# 插入 9999 笔记录,Redolog 产生量 11015104,耗时 00:00:04.03
SQL> insert into tb_rowid select * from tb_rowid where rownum<10000;

9999 rows created.

Commit complete.
Elapsed: 00:00:04.03

Statistics
———————————————————-
        32  recursive calls
      42863  db block gets
      6438  consistent gets
          2  physical reads
  11015104  redo size
        865  bytes sent via SQL*Net to client
        870  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      9999  rows processed
# 删除 9999 笔记录,Redolog 产生量 11019692,耗时 00:00:03.88
SQL> delete tb_rowid where rownum<10000;

9999 rows deleted.

Commit complete.
Elapsed: 00:00:03.88

Statistics
———————————————————-
        43  recursive calls
      42877  db block gets
        572  consistent gets
        27  physical reads
  11019692  redo size
        865  bytes sent via SQL*Net to client
        842  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      9999  rows processed
# 删除 9999 笔记录,Redolog 产生量 11010468,耗时 00:00:03.73
SQL> delete tb_rowid where rownum<10000;

9999 rows deleted.

Commit complete.
Elapsed: 00:00:03.73

Statistics
———————————————————-
        18  recursive calls
      42846  db block gets
        592  consistent gets
          0  physical reads
  11010468  redo size
        865  bytes sent via SQL*Net to client
        842  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      9999  rows processed

# 更新 9999 笔记录,Redolog 产生量 16150340,耗时 00:00:06.94
SQL> update tb_rowid set object_id=2 where rownum<10000;

9999 rows updated.

Commit complete.
Elapsed: 00:00:06.94

Statistics
———————————————————-
        51  recursive calls
      73132  db block gets
      1292  consistent gets
        109  physical reads
  16150340  redo size
        865  bytes sent via SQL*Net to client
        858  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      9999  rows processed

# 更新 9999 笔记录,Redolog 产生量 16078152,耗时 00:00:07.19
SQL> update tb_rowid set object_id=2 where rownum<10000;

9999 rows updated.

Commit complete.
Elapsed: 00:00:07.19

Statistics
———————————————————-
        30  recursive calls
      91767  db block gets
      1160  consistent gets
          1  physical reads
  16078152  redo size
        865  bytes sent via SQL*Net to client
        858  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      9999  rows processed

# 表 tb_rowid 上有快速刷新物化视图
# 建物化视图 mv_tb_rowid
SQL> create materialized view mv_tb_rowid tablespace users refresh fast on commit with rowid as select * from  tb_rowid;

Materialized view created.

Elapsed: 00:00:29.52

# 插入 9999 笔记录,Redolog 产生量 20177192,耗时 00:00:08.98
SQL> insert into tb_rowid select * from tb_rowid where rownum<10000;

9999 rows created.

Commit complete.
Elapsed: 00:00:08.98

Statistics
———————————————————-
      1415  recursive calls
      98178  db block gets
      4696  consistent gets
        412  physical reads
  20177192  redo size
        866  bytes sent via SQL*Net to client
        870  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
        92  sorts (memory)
          0  sorts (disk)
      9999  rows processed
     
# 插入 9999 笔记录,Redolog 产生量 19942160,耗时 00:00:07.26
SQL>  insert into tb_rowid select * from tb_rowid where rownum<10000;

9999 rows created.

Commit complete.
Elapsed: 00:00:07.26

Statistics
———————————————————-
        223  recursive calls
      97346  db block gets
      7576  consistent gets
          1  physical reads
  19942160  redo size
        866  bytes sent via SQL*Net to client
        871  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
        24  sorts (memory)
          0  sorts (disk)
      9999  rows processed
# 删除 9999 笔记录,Redolog 产生量 25751700,耗时 00:00:08.75
SQL> delete tb_rowid where rownum<10000;

9999 rows deleted.

Commit complete.
Elapsed: 00:00:08.75

Statistics
———————————————————-
        227  recursive calls
    136425  db block gets
      2362  consistent gets
          0  physical reads
  25751700  redo size
        866  bytes sent via SQL*Net to client
        842  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
        24  sorts (memory)
          0  sorts (disk)
      9999  rows processed
# 删除 9999 笔记录,Redolog 产生量 25890548,耗时 00:00:08.73
SQL> delete tb_rowid where rownum<10000;

9999 rows deleted.

Commit complete.
Elapsed: 00:00:08.73

Statistics
———————————————————-
        204  recursive calls
    136332  db block gets
      2223  consistent gets
        241  physical reads
  25890548  redo size
        868  bytes sent via SQL*Net to client
        842  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
        22  sorts (memory)
          0  sorts (disk)
      9999  rows processed
# 更新 9999 笔记录,Redolog 产生量 42848860,耗时 00:00:18.52
SQL> update tb_rowid set object_id=2 where rownum<10000;

9999 rows updated.

Commit complete.
Elapsed: 00:00:18.52

Statistics
———————————————————-
        902  recursive calls
    249586  db block gets
      5487  consistent gets
        292  physical reads
  42848860  redo size
        868  bytes sent via SQL*Net to client
        858  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
        24  sorts (memory)
          0  sorts (disk)
      9999  rows processed
# 更新 9999 笔记录,Redolog 产生量 43267360,耗时 00:00:16.95
SQL> update tb_rowid set object_id=2 where rownum<10000;

9999 rows updated.

Commit complete.
Elapsed: 00:00:16.95

Statistics
———————————————————-
        215  recursive calls
    250097  db block gets
      4048  consistent gets
          0  physical reads
  43267360  redo size
        868  bytes sent via SQL*Net to client
        858  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
        22  sorts (memory)
          0  sorts (disk)
      9999  rows processed
       
总结:
        表上无物化视图日志    表上有物化视图日志    表上有物化视图日志且有一个快速刷新的物化视图
插入        1M/0.21 秒            10M/3.73 秒                20M/8.98 秒
删除        4M/0.5 秒            10M/3.88 秒                25M/8.75 秒
更新        1M/0.13 秒            15M/6.94 秒                40M/18.52 秒

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址:http://www.linuxidc.com/Linux/2016-11/137170.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计10476字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中