マテリアライズドビュー検証まてマテマテビュー　その１０

＜マテリアライズドビュー検証まてマテマテビュー　その１０＞
ペンネーム：クリープ

前回で終了しましたマテリアライズドビューの検証でしたが、ご好評にお応え
して！？早くも帰ってきました。題して、

またマタマテビュー！

。。。

読者からありましたON COMMITについての質問にお応え致します。

■■■■■今回のあらすじ■■■■■
１）Ｑ１：ON COMMITに設定した時のパフォーマンスが気になります。

■環境
Microsoft Windows XP Pro
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production

■Ａ１
REFRESH ON COMMITは、元表のデータが変更されCOMMITされた時に自動的にリ
フレッシュされるようになります。
前回までの検証で、REFRESH ON COMMITについては扱っていませんでしたので、
ここで少し確認してみましょう。
まずは、マテビュー作成！

SQL> create materialized view log on emp with rowid( job, sal ),
     sequence including new values

SQL> create materialized view mv_emp refresh fast on commit
     as select job, sum( sal ) sum_sal from emp group by job;

ON COMMITできるのは、高速リフレッシュが可能な場合のみとなります。
この状態で元表に対して変更を行うと、COMMITと同時にリフレッシュも行われ
ます。では実行!

SQL> select * from mv_emp;

JOB          SUM_SAL
--------- ----------
CLERK           4150
SALESMAN        5600
PRESIDENT       5000
MANAGER         8275
ANALYST         6000

SQL> insert into emp values
     (1, 'TOM', 'PART-TIME', null, sysdate, 300, null, 10 );

SQL> commit;

SQL> select * from mv_emp;

JOB          SUM_SAL
--------- ----------
CLERK           4150
SALESMAN        5600
PRESIDENT       5000
MANAGER         8275
ANALYST         6000
PART-TIME        300　<==COMMIT後に追加されている

コミットした時点でマテビューが更新されています。リフレッシュを行う必要
がなく、コミットした時点なのでマテビューを常に最新の状態に保つことがで
きます。
とこのように、ON COMMITは非常に便利な機能である反面、コミット時にマテ
ビューに更新処理が発生する為、処理時間が気になるところです。
実際に確認してみましょう。

SQL> alter table emp modify empno number(7);

SQL> create table emp_100man as select * from emp where 0 = 1;

SQL> insert into emp_100man select rownum, e1.ename, e1.job, e1.mgr,
     e1.hiredate, e1.sal, e1.comm, e1.deptno from emp e1, emp e2, emp e3,
     emp e4, emp e5, emp e6 where rownum <= 1000000;

SQL> truncate table emp;

SQL> exec dbms_mview.refresh( 'mv_emp', 'c');

100万件のデータが入っているテーブルemp_100manを作成しました。ちなみに、
最後に完全リフレッシュを行っているのは、truncate文はDDL文なのでON
COMMITしてもリフレッシュされないからです。
truncate文には気をつけましょう。では、実際にデータ挿入！

SQL> insert into emp select * from emp_100man;

SQL> commit;

INSERTの実行に360秒、COMMITの実行に180秒かかりました。やはり、ON COMMIT
時にMV_EMPへの更新処理が実行されていることが原因なのでしょうか。実際に
MV_EMPへのMERGE文の処理時間を確認してみましょう。

SQL> select sql_text, executions, cpu_time/1000000, elapsed_time/1000000
     from v$sql where sql_text like '%MERGE%MV_EMP%';

SQL_TEXT
-----------------------------------------------------------------------
/* MV_REFRESH (MRG) */ MERGE INTO "SCOTT"."MV_EMP" "SNA$" USING (SELECT...

EXECUTIONS CPU_TIME/1000000 ELAPSED_TIME/1000000
---------- ---------------- --------------------
         1 2.653816 17.051233



SQL>
※一部抜粋

MERGE文の処理時間(ELAPSED_TIME)は17秒なので、INSERT文の実行時間(360秒)
が長い原因とは直接関係無いようです。また今回の場合、MV_EMPへの更新は数
行のみなので、処理時間はほとんどかからないと考えられます。

と、ここで忘れてはいけないのが、MLOGの存在。

SQL> select sql_text, executions, cpu_time/1000000, elapsed_time/1000000
     from v$sql where sql_text like '%MLOG$_EMP%';

SQL_TEXT
---------------------------------------------------------------------
delete from "SCOTT"."MLOG$_EMP" where snaptime$$ <= :1
INSERT /*+ IDX(0) */ INTO "SCOTT"."MLOG$_EMP" (dmltype$$,old_new$$,s ...
update "SCOTT"."MLOG$_EMP" set snaptime$$ = :1 where snaptime$$ > t ...
/* MV_REFRESH (MRG) */ MERGE INTO "SCOTT"."MV_EMP" "SNA$" USING (SEL ...
select sql_text, executions, cpu_time/1000000, elapsed_time/1000000 ...
SELECT 'N', COUNT(*) FROM "SCOTT"."MLOG$_EMP" WHERE SNAPTIME$$ > ...

EXECUTIONS CPU_TIME/1000000 ELAPSED_TIME/1000000
---------- ---------------- --------------------
         1 18.497255 90.379788 <= COMMIT時
   1000000 78.286569 255.295785 <= INSERT時
         1 8.564129 52.3996 <= COMMIT時
         1 2.653816 17.051233 <= COMMIT時
         1 .012786 .020343 <= COMMIT時
         1 .623514 9.688754 <= COMMIT時

※一部抜粋

2つ目のINSERT以外はコミット時に実行されている処理であり、合計で168秒か
かっている為、コミットの処理に180秒近くかかってしまっています。
ちなみに、INSERT処理に実行されているMLOGへの挿入処理も255秒と全処理（3
60秒）の半分以上の処理時間がかかっています。
つまり、INSERTからCOMMITまでの処理時間560秒のうち、約75%に相当する時間
(168秒 + 255秒 = 423秒)がMLOGの処理に対して費やされたということになり
ます。
大量の更新処理が発生するような環境でON COMMITを使用する場合は注意が必
要なようです。

以上、マテビューのご質問にお応えしました。
次回も引き続きマテビューＱアンドＡをお送りする予定です。

気持ちだけは常にフレッシャーズ、なつもりのフケッシャーズ　恵比寿にて

マテリアライズドビュー検証 まて マテ マテビュー その１０

マテリアライズドビュー検証まてマテマテビュー　その１０