触发器内的代码封装

实验准备:

hr@ORCL> drop table t purge;

Table dropped.

hr@ORCL> create table t (code number);

Table created.

hr@ORCL> create table t_audit (code number,ins_date date);

Table created.

hr@ORCL> create or replace trigger tri_audit_t

2 before insert

3 on t

4 for each row

5 begin

6 insert into t_audit values(:new.code,sysdate);

7 end;

8 /

同时打开两个session,并做如下配置:

hr@ORCL> alter session set sql_trace=true;

Session altered.

hr@ORCL> alter session set tracefile_identifier='linwaterbin_null';--session_1

Session altered.

hr@ORCL> alter session set tracefile_identifier='linwaterbin'; --session_2

Session altered.

hr@ORCL> set feedback off

session_1的实验:

hr@ORCL> ed

Wrote file afiedt.buf

1 SELECT s.SID,VALUE,NAME

2 FROM v$sesstat s,v$statname n WHERE s.sid = 139

3* AND s.STATISTIC#=n.STATISTIC# AND n.NAME IN( 'parse count (total)','parse count (hard)')

hr@ORCL> /

SID VALUE NAME ---------- ---------- ----------------------------------------------------------------

139 304 parse count (total)

139 99 parse count (hard)

hr@ORCL> insert into t values(1);

hr@ORCL> insert into t values(1);

hr@ORCL> insert into t values(1);

hr@ORCL> insert into t values(1);

hr@ORCL> insert into t values(1);

hr@ORCL> insert into t values(1);

hr@ORCL> insert into t values(1);

hr@ORCL> insert into t values(1);

hr@ORCL> insert into t values(1);

hr@ORCL> insert into t values(1);

hr@ORCL> ed

Wrote file afiedt.buf

1 SELECT s.SID,VALUE,NAME

2 FROM v$sesstat s,v$statname n WHERE s.sid = 139

3* AND s.STATISTIC#=n.STATISTIC# AND n.NAME IN( 'parse count (total)','parse count (hard)')

hr@ORCL> /

SID VALUE NAME ---------- ---------- ----------------------------------------------------------------

139 344 parse count (total)

139 101 parse count (hard)

344-304=40,这里做了40次解析调用

      session_2实验:

hr@ORCL> ed

Wrote file afiedt.buf

1 create or replace procedure pro_t_audit(p_code number)

2 is

3 begin

4 insert into t_audit values(p_code,sysdate);

5* end;

hr@ORCL> /

Procedure created.

hr@ORCL> ed

Wrote file afiedt.buf

1 create or replace trigger tri_audit_t

2 before insert

3 on t

4 for each row

5* call pro_t_audit(:new.code)

hr@ORCL> /

Trigger created.

hr@ORCL> ed

Wrote file afiedt.buf

1 SELECT s.SID,VALUE,NAME

2 FROM v$sesstat s,v$statname n WHERE s.sid = 159

3* AND s.STATISTIC#=n.STATISTIC# AND n.NAME IN( 'parse count (total)','parse count (hard)')

hr@ORCL> /

SID VALUE NAME ---------- ---------- ----------------------------------------------------------------

159 414 parse count (total)

159 176 parse count (hard)

hr@ORCL> insert into t values(1);

hr@ORCL> insert into t values(1);

hr@ORCL> insert into t values(1);

hr@ORCL> insert into t values(1);

hr@ORCL> insert into t values(1);

hr@ORCL> insert into t values(1);

hr@ORCL> insert into t values(1);

hr@ORCL> insert into t values(1);

hr@ORCL> insert into t values(1);

hr@ORCL> insert into t values(1);

hr@ORCL> ed

Wrote file afiedt.buf

1 SELECT s.SID,VALUE,NAME

2 FROM v$sesstat s,v$statname n WHERE s.sid = 159

3* AND s.STATISTIC#=n.STATISTIC# AND n.NAME IN( 'parse count (total)','parse count (hard)')

hr@ORCL> /

SID VALUE NAME ---------- ---------- ----------------------------------------------------------------

159 447 parse count (total)

159 178 parse count (hard)

447-414=33,这里作了33次解析调用
     
      session_1为40
      session_2为33
      为什么呢?跟踪一下trc
     
      未封装触发器内代码的trc文件
      tkprof orcl_ora_6503_linwaterbin_null.trc /home/Oracle/lin.txt sys=no
      trc文件:

INSERT INTO T_AUDIT      
VALUES 
(:B1 ,SYSDATE)     
        
call     count       CPU    elapsed       disk      query    current        rows 
------- ------  -------- ---------- ---------- ---------- ----------  ----------     
Parse       10      0.00       0.00          0          0          0           0     
Execute     10      0.01       0.04          4         10         34          10     
Fetch        0      0.00       0.00          0          0          0           0     
------- ------  -------- ---------- ---------- ---------- ----------  ----------     
total       20      0.01       0.04          4         10         34          10

封装了触发器内代码的trc文件
      tkprof orcl_ora_6261_linwaterbin.trc /home/Oracle/water.txt sys=no
      trc文件:

INSERT INTO T_AUDIT      
VALUES 
(:B1 ,SYSDATE) 

call     count       cpu    elapsed       disk      query    current        rows 
------- ------  -------- ---------- ---------- ---------- ----------  ----------     
Parse        1      0.00       0.00          0          0          0           0     
Execute     10      0.00       0.04          4          1         14          10     
Fetch        0      0.00       0.00          0          0          0           0     
------- ------  -------- ---------- ---------- ---------- ----------  ----------     
total       11      0.00       0.04          4          1         14          10

从这两个trc文件,不难得出:
      ● 解析次数:10 & 1
      ● 逻辑读:    44 & 14
      如果把数据提升到T级、P级呢?是否这个比例更可观呀
      所以呢,大家遵循一个最简单的原则:请避免在触发器中出现SQL,如果触发器需要SQL,请把工作交给过程。

(来源:天新网)

捷迅贵德软件销售热线销售热线:18601265588,18638553188

在线客服:QQ:2236696042QQ:907760449QQ:77247301

诚招分销商 | 关于捷迅贵德 | 访客留言

© 2004-2017 北京捷迅贵德软件有限公司 版权所有