Materialized View Log Overhead

To understand the overhead of materialized view log, I did a test by comparing the logical reads between the cases of presence and absence of materialized view log. I found that Oracle needs to do 14.6% more work in the case of materialized view log.

In the test, I firstly created a table called big_table in the scott schema from dba_objects with 1,000,000 rows. Then in the case of existing of materialized view log, I created a materialized view log on the table scott.big_table. Finally, I executed some update/delete/insert statments against this table with and without materialized view log.

The test script is as follows:

 

alter session set tracefile_identifier = mvlog;
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';

-- update

begin
for rec in (select id from scott.big_table where rownum <=100000 )
loop
   update scott.big_table set status=lower(status) where id=rec.id;
   commit;
end loop;
end;
/

-- insert
declare
 maxid number;
begin
   select max(id) into maxid from scott.big_table;
   for rec in (select * from scott.big_table where rownum <=100000 )
   loop
      maxid := maxid +1;
      insert into scott.big_table values (
           maxid
          ,rec.OWNER
          ,rec.OBJECT_NAME
          ,rec.SUBOBJECT_NAME
          ,rec.OBJECT_ID
          ,rec.DATA_OBJECT_ID
          ,rec.OBJECT_TYPE
          ,sysdate
          ,sysdate
          ,rec.TIMESTAMP
          ,rec.STATUS
          ,rec.TEMPORARY
          ,rec.GENERATED
          ,rec.SECONDARY
      );
      commit;
   end loop;
end;
/

---  delete 100,000 rows

begin
for rec in (select id from scott.big_table where rownum <=100000 )
loop
   delete from scott.big_table  where id=rec.id;
   commit;
end loop;
end;
/
exit;

Below is the TKPROF analysis result summary:

SQL Statement: 
              select id from scott.big_table where rownum <=100000"
MVLOG         query + current = 200266
NO MVLOG      query + current = 200266


SQL Statement: 
              select * from scott.big_table where rownum <=100000
MVLOG         query + current = 100109
NO MVLOG      query + current = 100133


SQL Statement: 
              DELETE from scott.big_table where id=:b1
MVLOG         query + current = 812628
NO MVLOG      query + current = 811684



SQL Statement: 
             INSERT into scott.big_table values (:b12 ,:b11 ,:b10 ,:b9  ,:b8  
             ,:b7 ,:b6 ,sysdate ,sysdate ,:b5  ,:b4 ,:b3 ,:b2 ,:b1  )
MVLOG         query + current = 516815
NO MVLOG      query + current = 515709


SQL Statement: 
              UPDATE scott.big_table set status=lower(status)
MVLOG         query + current = 503087
NO MVLOG      query + current = 501878

SQL Statement: 
              INSERT /*+ IDX(0) */ INTO "SCOTT"."MLOG$_BIG_TABLE" 
              (dmltype$$,old_new$$, snaptime$$,change_vector$$,"ID") VALUES 
              (:d,:o,to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,:1)
MVLOG         query + current = 309375


The total logical reads
   MVLOG : 2442240
NO MVLOG : 2129630

Overhead:  (2442240 - 2129630 ) / 2129630 = 14.6 %