I would start by mentioning that the case below is not my own, I just tried to reproduce an example
from Tom Kyte's book - Effective Oracle By Design. Thanks Tom for the great material!
The results show that the implementation of using SQL in triggers does not cause excessive parsing in 11g.
In previous releases, the SQL statements used in a trigger were cached only for the duration of the call to the server; each separate call to the server would have caused the statements to be soft parsed over and over.
We will do the setup by using the emp table and a summary table of counts of employees by deptno.
The counts will be maintained by a trigger after INSERT, UPDATE, DELETE on the emp tale.
SQL> select banner from v$version where rownum <= 1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SQL> create table emp as
2 select ename, empno, deptno
3 from scott.emp;
Table created.
SQL> create table emp_dept_cnt
2 (deptno primary key, cnt)
3 organization index
4 as
5 select deptno, count(*)
6 from emp
7 group by
8 deptno;
Table created.
SQL> create trigger emp_dept_cnt_trigger
2 after insert or update or delete on emp
3 for each row
4 begin
5 if (inserting or updating)
6 then
7 merge into emp_dept_cnt in_trigger
8 using (select :new.deptno deptno from dual) n
9 on (in_trigger.deptno = n.deptno)
10 when matched then
11 update set cnt=cnt+1
12 when not matched then
13 insert (deptno, cnt) values (:new.deptno,1);
14 end if;
15 if (updating or deleting)
16 then
17 update emp_dept_cnt in_trigger
18 set cnt = cnt-1
19 where deptno = :old.deptno;
20 end if;
21 end;
22 /
Trigger created.
Now we are tracing the activity that will fire the trigger.
SQL> alter session set sql_trace=true;
Session altered.
SQL> insert into emp (ename,empno,deptno)
2 values ('john',123,10);
1 row created.
SQL> insert into emp (ename,empno,deptno)
2 values ('mike',123,10);
1 row created.
SQL> delete from emp;
16 rows deleted.
In the trace file produced, we are interested to see the parse count for the MERGE statement and for the UPDATE.
Since we had two insert operations, we observe that the SQL statements are cached, so we will have only one parse call, and two executions.
---
MERGE INTO EMP_DEPT_CNT IN_TRIGGER USING
(SELECT :B1 DEPTNO FROM DUAL) N ON
(IN_TRIGGER.DEPTNO = N.DEPTNO) WHEN MATCHED THEN UPDATE SET CNT=CNT+1 WHEN
NOT MATCHED THEN INSERT (DEPTNO, CNT) VALUES (:B1 ,1)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.01 0.00 0 15 10 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.00 0 15 10 2
UPDATE EMP_DEPT_CNT IN_TRIGGER SET CNT = CNT-1
WHERE
DEPTNO = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 16 0.00 0.00 0 16 16 16
---
In previous releases, this would have been possible only by packaging the SQL and invoke the packed procedures in the trigger.
SQL> create or replace package emp_dept_cnt_pkg
2 as
3 procedure insert_update(p_deptno in number);
4 procedure update_delete(p_deptno in number);
5 end;
6 /
Package created.
SQL> create or replace package body emp_dept_cnt_pkg
2 as
3 procedure insert_update (p_deptno in number)
4 as
5 begin
6 merge into emp_dept_cnt in_package
7 using (select p_deptno deptno from dual) n
8 on (in_package.deptno = n.deptno)
9 when matched then
10 update set cnt=cnt+1
11 when not matched then
12 insert (deptno, cnt) values (p_deptno,1);
13 end;
14 procedure update_delete (p_deptno in number)
15 as
16 begin
17 update emp_dept_cnt in_package
18 set cnt = cnt-1
19 where deptno = p_deptno;
20 end;
21 end;
22 /
Package body created.
The trigger will now be build as it follows:
SQL> create or replace trigger emp_dept_cnt_trigger
2 after insert or update or delete on emp
3 for each row
4 begin
5 if (inserting or updating)
6 then
7 emp_dept_cnt_pkg.insert_update (:new.deptno);
8 end if;
9 if (updating or deleting)
10 then
11 emp_dept_cnt_pkg.update_delete (:old.deptno);
12 end if;
13 end;
14 /
Trigger created.
SQL> alter session set sql_trace=true;
Session altered.
SQL> insert into emp (ename,empno,deptno)
2 values ('bob',124,10);
1 row created.
SQL> insert into emp (ename,empno,deptno)
2 values ('ken',124,10);
1 row created.
SQL> delete from emp;
2 rows deleted.
SQL> alter session set sql_trace=false;
Session altered.
-----
The trace shows the parse count is one, our sql is cached.
MERGE INTO EMP_DEPT_CNT IN_PACKAGE USING
(SELECT :B1 DEPTNO FROM DUAL) N ON
(IN_PACKAGE.DEPTNO = N.DEPTNO) WHEN MATCHED THEN UPDATE SET CNT=CNT+1 WHEN
NOT MATCHED THEN INSERT (DEPTNO, CNT) VALUES (:B1 ,1)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.01 0.01 0 11 6 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 11 6 2
