触发器内的代码打包
日期：2014-05-16 浏览次数：20510 次
触发器内的代码封装
同时打开两个session，并做如下配置：
session_1的实验：
344-304=40，这里做了40次解析调用
实验准备：
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
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
