触发器内的代码封装

       验准备:

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,请把工作交给过程。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值