刷新物化视图很慢_Oracle 物化视图快速刷新对性能的影响

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 ro