觸發器內的代碼封裝

       實驗準備：

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_1Session altered.hr@ORCL> alter session set tracefile_identifier='linwaterbin';  --session_2Session altered.hr@ORCL> set feedback off

       session_1的實驗：

hr@ORCL> edWrote 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> edWrote 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> edWrote 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> edWrote 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> edWrote 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> edWrote 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           0Execute     10      0.01       0.04          4         10         34          10Fetch        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           0Execute     10      0.00       0.04          4          1         14          10Fetch        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,請把工作交給過程。