触发器做数据验证的性能问题

还是继续上次的话题，用触发器做数据验证，上次已经讨论了用触发器做数据验证有可能会出错，是功能方面的问题。这次想讨论一下性能问题。

首先建立两个测试表：
create table t1 (c1 number,constraint myt1_ck1 check（ c1 between 0 and 100000）) ;
create talbe t2 (c1 number);
create or replace
TRIGGER MYT2_TRG
AFTER INSERT OR UPDATE ON T2
FOR EACH ROW
BEGIN
IF :NEW.C1<0 OR :NEW.C1>100000 THEN
RAISE_APPLICATION_ERROR(-20001,'Value is too large or too small.');
END IF;
END;

然后set timing on
运行以下语句：
insert into t1 select rownum from all_objects where rownum<=10000;
insert into t2 select rownum from all_objects where rownum<=10000;
第一个语句耗时0.51M
第二个语句耗时0.59M
insert into t1 select rownum from all_objects where rownum<=100000;
insert into t2 select rownum from all_objects where rownum<=100000;
第一个语句耗时35.25M
第二个语句耗时32.56M

这是在没有错误发生的情况下测试，如果有错误发生，性能相差更大，有兴趣可以写个小例子测试一下。

接下来使用tom老人家的runstats工具来进行比较测量。

fy@ORCL> exec runstats_pkg.rs_start();

PL/SQL procedure successfully completed.

fy@ORCL> insert into myt1 select rownum from all_objects where rownum<=100000;

71844 rows created.

fy@ORCL> exec runstats_pkg.rs_middle();

PL/SQL procedure successfully completed.

fy@ORCL> insert into myt2 select rownum from all_objects where rownum<=100000;

71844 rows created.

fy@ORCL> exec runstats_pkg.rs_stop(1000);
Run1 ran in 10488 hsecs
Run2 ran in 12552 hsecs
run 1 ran in 83.56% of the time

Name Run1 Run2 Diff
LATCH.object queue header oper 502 1,616 1,114
LATCH.simulator lru latch 9,022 12,771 3,749
LATCH.simulator hash latch 9,053 12,905 3,852
STAT...no work - consistent re 81,400 72,775 -8,625
STAT...consistent gets from ca 225,445 216,746 -8,699
STAT...consistent gets 225,445 216,746 -8,699
STAT...buffer is not pinned co 283,146 274,428 -8,718
STAT...buffer is pinned count 74,052 82,792 8,740
STAT...consistent gets from ca 82,070 73,301 -8,769
STAT...session uga memory -15,772 0 15,772
STAT...physical read total byt 32,768 49,152 16,384
STAT...physical read bytes 32,768 49,152 16,384
STAT...session logical reads 226,812 290,606 63,794
STAT...HSC Heap Segment Block 405 71,858 71,453
STAT...redo entries 900 72,356 71,456
STAT...db block gets from cach 1,367 73,860 72,493
STAT...db block gets 1,367 73,860 72,493
STAT...calls to kcmgrs 37,858 119,922 82,064
STAT...db block changes 1,384 144,841 143,457
STAT...session pga memory -196,608 0 196,608
LATCH.cache buffers chains 237,315 583,505 346,190
STAT...undo change vector size 172,280 4,600,964 4,428,684
STAT...redo size 1,120,676 17,033,476 15,912,800

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
838,049 1,190,743 352,694 70.38%

PL/SQL procedure successfully completed.

上面 Diff的差值是负数的情况，估计是由于数据缓存造成的，在真实环境下，应该不会有这种情况发生。主要可以看到不论是运行时间还是latch数量，第一个方法都小于第二个方法。因此，用什么方法做数据验证，大家应该都没问题了吧。