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

xuanzi 2016-11-15

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秒

相关推荐