物化视图日志（mview log）使用触发器实现，如果对表建立mview log，那么相比没有建立之前，操作表(dml)，到底额外产生多大的性能开销？为此我专门做了一个测试：
测试原理：进行db操作时，数据库都会生成一系列性能指标衡量诸种操作的开销，通过对一个测试表进行批量的dml操作，比较建立mview log前后的开销对比性能差距。
过程如下：
1.首先记录没有任何操作时的性能统计信息 2.测试表进行频繁的dml操作。 3.记录统计信息s1（集合） 4.对表建立mview log，重复步骤2 5.记录统计信息s2（集合） 6.计算s1-s2（集合差值）
具体步骤如下
首先建立测试表：
creare table t_mv_test as select * from dba_objects where 1=2;
建立包：
create or replace package PKG_MV_TEST as --入口方法 procedure PR_MV_ENTRANCE; --插入测试 procedure PR_MV_INSERT; --删除测试 procedure PR_MV_DELETE; --更新测试 procedure PR_MV_UPDATE; --查询测试 procedure PR_MV_SELECT; end;
建立包体：
create or replace package body PKG_MV_TEST as --入口方法 procedure PR_MV_ENTRANCE as begin PR_MV_INSERT; PR_MV_update; PR_MV_select; PR_MV_delete; end; --插入测试 procedure PR_MV_INSERT as begin execute immediate 'truncate table t_mv_test'; for info in (select * from dba_objects) loop insert into t_mv_test values (info.OWNER, info.OBJECT_NAME, info.SUBOBJECT_NAME, info.OBJECT_ID, info.DATA_OBJECT_ID, info.OBJECT_TYPE, info.CREATED, info.LAST_DDL_TIME, info.TIMESTAMP, info.STATUS, info.TEMPORARY, info.GENERATED, info.SECONDARY); end loop; commit; end; --删除测试 procedure PR_MV_DELETE as begin for info in (select object_id from t_mv_test) loop delete from t_mv_test where object_id = info.object_id; commit; end loop; end; --更新测试 procedure PR_MV_UPDATE as begin for info in (select * from t_mv_test) loop update t_mv_test t set t.object_id = info.object_id, t.owner = info.owner, t.object_name = info.object_name, t.subobject_name = info.subobject_name, t.object_type = info.object_type where t.object_id = info.object_id; commit; end loop; end; --查询测试 procedure PR_MV_SELECT as n_count number(10); begin for info in (select object_id from t_mv_test) loop select count(*) into n_count from t_mv_test where object_id = info.object_id; commit; end loop; end; end;
测试过程：
SQL> set serveroutput on size 1000000; SQL> exec gather_stats.begin; PL/SQL procedure successfully completed SQL> exec pkg_mv_test.PR_MV_ENTRANCE; PL/SQL procedure successfully completed SQL> exec gather_stats.after; PL/SQL procedure successfully completed SQL> SQL> exec pkg_mv_test.PR_MV_ENTRANCE; PL/SQL procedure successfully completed SQL> gather_stats.end(100000);
测试结果：
SQL> exec gather_stats.end(100000); Run1 ran in1094hsecs--无mv log时的执行时间 Run2 ran in6035hsecs--有mv log时的执行时间 run 1 ran in 18.13%of the time Name Run1 Run2 Diff STAT...execute count 224,133 392,186 168,053 STAT...recursive calls 451,114 620,619 169,505 STAT...redo entries 289,196 461,724 172,528 STAT...db block gets from cach 414,314 593,259 178,945 STAT...db block gets 414,314 593,259 178,945 STAT...session logical reads 989,568 1,170,597 181,029 STAT...session uga memory 327,416 65,464 -261,952 latchlibrary cache pin 449,195 789,779 340,584 STAT...db block changes 575,565 917,724 342,159 latchlibrary cache 449,678 794,524 344,846 STAT...session uga memory max 516,332 65,464 - 450,868 STAT...session pga memory 524,288 65,536 -458,752 STAT...session pga memory max 655,360 131,072 -524,288 latchcache buffers chains 2,852,468 3,975,752 1,123,284 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 4,145,219 6,123,178########## 67.70% PL/SQL procedure successfully completed
如果每条sql都提交一次的话：
SQL> exec gather_stats.end(100000); Run1 ran in1699hsecs Run2 ran in6728hsecs run 1 ran in 25.25%of the time Name Run1 Run2 Diff STAT...IMU Flushes 3,020 103,295 100,275 STAT...IMU commits 101,546 0 -101,546 STAT...enqueue releases 224,691 336,914 112,223 STAT...enqueue requests 224,691 336,914 112,223 STAT...commit cleanouts 169,231 281,668 112,437 STAT...commit cleanouts succes 169,225 281,663 112,438 STAT...execute count 224,159 392,168 168,009 STAT...recursive calls 787,292 956,327 169,035 STAT...redo entries 305,989 480,265 174,276 STAT...db block gets 736,761 913,431 176,670 STAT...db block gets from cach 736,761 913,431 176,670 STAT...session logical reads 1,312,294 1,489,999 177,705 latchIn memory undo latch 470,969 671,951 200,982 latchdml lock allocation 224,260 448,662 224,402 latchenqueue hash chains 450,145 675,745 225,600 latchlibrary cache pin 449,116 788,329 339,213 latchlibrary cache 617,465 958,788 341,323 STAT...db block changes 852,286 1,194,856 342,570 STAT...session uga memory 392,880 0 -392,880 STAT...session uga memory max 581,796 73,048 -508,748 latchshared pool 224,163 785,565 561,402 STAT...session pga memory max 720,896 131,072 -589,824 STAT...session pga memory 589,824 0 -589,824 latchcache buffers chains 4,864,287 5,965,172 1,100,885 latchrow cache objects 2,726 1,685,157 1,682,431 STAT...undo change vector size############################## STAT...IMU undo allocation siz############################## STAT...redo size ############################## STAT...IMU Redo allocation siz 1,016,744#################### Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct ############################## 68.13% PL/SQL procedure successfully completed
可见，有mv log存在的情况下，耗时约是没有mv log的4倍左右。
而小事务的话会造成更多的提交开销，会产生更长时间的等待，因为不但有latch争用，还有写mv log的时间开销。
而我这个测试只是一个会话，如果多并发的话，这个代价可能会更大。
可能由于本人机器性能的限制，这个4倍的结果可能有些不准确。
而小事务的话会造成更多的提交开销，会产生更长时间的等待，因为不但有latch争用，还有写mv log的时间开销。
而我这个测试只是一个会话，如果多并发的话，这个代价可能会更大。
可能由于本人机器性能的限制，这个4倍的结果可能有些不准确。