[20161003]触发器与redo.txt

[20161003]触发器与redo.txt

--对于触发器,我个人认为对于dba是最讨厌的东西,它使得维护变得困难,不小心就陷入陷阱里面.
--我曾经跟开发讲过建立一个触发器相当于给表建立一个索引.除非万不得以不要建立触发器.
--昨天看了一个例子,重复作者的测试来说明问题:

http://orasql.org/2016/09/22/how-even-empty-trigger-increases-redo-generation/

1.环境:

SCOTT@test01p> @ ver1
PORT_STRING          VERSION    BANNER                                                                       CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production      0

set feed on;
-- simple table:
create table xt_curr1 as select level a,level b from dual connect by level<=1e4;
-- same table but with empty trigger:
create table xt_curr2 as select level a,level b from dual connect by level<=1e4;
create or replace trigger tr_xt_curr2 before update on xt_curr2 for each row
begin
  null;
end;
/

set autot trace stat;
update xt_curr1 set b=a;
--commit;
set autot off;

set autot trace stat;
update xt_curr2 set b=a;
--commit;
set autot off;
set feed off

drop table xt_curr1 purge;
drop table xt_curr2 purge;

2.测试结果:


update xt_curr1 set b=a;

Statistics
----------------------------------------------------------
          8  recursive calls
        142  db block gets
         30  consistent gets
         18  physical reads
     960068  redo size
        862  bytes sent via SQL*Net to client
        824  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      10000  rows processed

update xt_curr2 set b=a;
Statistics
----------------------------------------------------------
         10  recursive calls
      20386  db block gets
         40  consistent gets
         19  physical reads
    4731300  redo size
        862  bytes sent via SQL*Net to client
        824  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      10000  rows processed

update xt_curr2 set b=a;

--可以发现即使是NULL的操作,也会导致redo增加不少.