[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增加不少.