【オリジナル】マテリアライズド・ビュー・ログのパフォーマンスへの影響テスト


マテリアライズド・ビュー・ログ(mview log)はフリップフロップを使用して実装され、テーブルにmview logを作成すると、作成前に操作テーブル(dml)が作成されていないのに比べて、いったいどれだけのパフォーマンス・オーバーヘッドが発生しますか?このため、db操作を行うと、データベースは一連の性能指標を生成して各操作のオーバーヘッドを測定し、1つのテストテーブルに対してロットの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ごとに1回コミットされる場合:
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倍の结果は少し正确ではありません.