【原创】物化视图日志对性能的影响测试

最新推荐文章于 2021-04-07 21:15:04 发布

weixin_34233856

最新推荐文章于 2021-04-07 21:15:04 发布

阅读量323

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/zhangxsh/p/3494265.html

版权

物化视图日志（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倍的结果可能有些不准确。

转载于:https://www.cnblogs.com/zhangxsh/p/3494265.html

weixin_34233856

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
【原创】物化视图日志对性能的影响测试

物化视图日志（mview log）使用触发器实现，如果对表建立mview log，那么相比没有建立之前，操作表(dml)，到底额外产生多大的性能开销？为此我专门做了一个测试：测试原理：进行db操作时，数据库都会生成一系列性能指标衡量诸种操作的开销，通过对一个测试表进行批量的dml操作，比较建立mview log前后的开销对比性能差距。过程如下：1.首先记录没有任何操作时的性能统计信息2...
复制链接

扫一扫