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

366次阅读

共计 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

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 秒