Code encapsulation in a trigger

Test preparation for code encapsulation in the trigger: www.2cto.com [SQL] 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/open two sessions at the same time, and make the following configuration: www.2cto.com [SQL] 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 experiment: [SQL] 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 ---------- -------- limit 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); h R @ 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 (har D) ') hr @ ORCL>/sid value name ---------- limit 139 344 parse count (total) 139 101 parse count (hard) 344-304 = 40, here we have made 40 parsing calls to session_2 experiment: [SQL] 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 int O 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 ---------- ------------------------------------------------------ 15 9 447 parse count (total) 159 178 parse count (hard) 447-414 = 33. Here we make 33 parsing calls. Why is session_1 40 session_2 33? Trace the trc file tkprof orcl_ora_rj3_linwaterbin_null.trc/home/oracle/lin.txt sys = no trc file in trc without encapsulating trigger code: [SQL] 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 4 10 34 10 Fetch 0 0.04 0 0 0 0 0 0 -------------------------------------- --- ---------- Total 20 0.01 0.04 4 10 34 10 www.2cto.com encapsulates the trc file tkprof orcl_ora_62620.linwaterbin.trc/home/oracle/water.txt sys = no trc file: [SQL] INSERT INTO T_AUDIT VALUES (: B1, SYSDATE) call count cpu elapsed disk query current rows ------- ------ -------- ---------- Parse 1 0.00 0.00 0 0 0 Execute 10 0.00 4 1 14 10 Fetch 0 0.00 0.00 0 0 0 ------- ------ -------- ---------- total 11 0.00 0.04 4 1 14 10 from these two trc files, it is not difficult to conclude: ● number of resolutions: 10 & 1 ● logical read: 44 & 14 What if I upgraded the data to the T and P levels? Is this proportion more impressive? So everyone follows the simplest principle: Please avoid SQL in the trigger. If the trigger requires SQL, please hand over the work to the process.