Subject: Improving the Performance of Database Triggers
Author: pnarth
Last Revision Date: 15 November 1994
Improving The Performance Of Database Triggers
----------------------------------------------
With the Oracle7 server it is possible to write application logic at the
database level using Stored Procedures and Database Triggers. These objects are
documented in "The Oracle7 Server Concepts Manual" (chapters 14 and 15), "The
Oracle7 Application Developer's Guide" (chapters 7 and 8), and in "The Oracle7
Server SQL Language Reference Manual" (see chapter 4 for details on the actual
syntax).
Database triggers are PL/SQL modules that are implicitly executed whenever
an INSERT, UPDATE, or DELETE statement is issued against the associated table.
Stored Procedures, on the other hand, have to be explicitly executed by a user
or application call.
This article provides a method of improving the performance of a database
trigger by using a combination of triggers and stored procedures. Consider the
trigger below, based on the EMP demonstration tables owned by the Oracle user
scott.
CREATE OR REPLACE TRIGGER update_emp
AFTER INSERT OR UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
INSERT INTO emp1 VALUES (:new.empno,:new.ename,:new.job,:new.mgr,
:new.hiredate,:new.sal,:new.comm,:new.deptno);
INSERT INTO emp2 VALUES (:new.empno,:new.ename,:new.job,:new.mgr,
:new.hiredate,:new.sal,:new.comm,:new.deptno);
INSERT INTO emp3 VALUES (:new.empno,:new.ename,:new.job,:new.mgr,
:new.hiredate,:new.sal,:new.comm,:new.deptno);
INSERT INTO emp4 VALUES (:new.empno,:new.ename,:new.job,:new.mgr,
:new.hiredate,:new.sal,:new.comm,:new.deptno);
INSERT INTO emp5 VALUES (:new.empno,:new.ename,:new.job,:new.mgr,
:new.hiredate,:new.sal,:new.comm,:new.deptno);
END;
/
Everytime the sal column is updated or a new record is inserted the trigger
copies the new/updated record to five other tables. An alternative method
of performing the same action is to take the PL/SQL out of the trigger and
put it into a stored procedure which is then called by a trigger, ie :
CREATE OR REPLACE TRIGGER update_emp
AFTER INSERT OR UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
emp_mgmt.insert_emp(:new.empno,:new.ename,:new.job,:new.mgr,:new.hiredate,
:new.sal,:new.comm,:new.deptno);
END;
/
where emp_mgmt.insert_emp is:
CREATE OR REPLACE PACKAGE emp_mgmt AS
PROCEDURE insert_emp
(new_empno number,new_ename varchar2,new_job varchar2,new_mgr number,
new_hiredate date, new_sal number,new_comm number,new_deptno number);
END emp_mgmt;
CREATE OR REPLACE PACKAGE BODY emp_mgmt AS
PROCEDURE insert_emp
(new_empno number,new_ename varchar2,new_job varchar2,new_mgr number,
new_hiredate date, new_sal number,new_comm number,new_deptno number)
IS
BEGIN
INSERT INTO emp1 VALUES
(new_empno,new_ename,new_job,new_mgr,new_hiredate,
new_sal,new_comm,new_deptno);
INSERT INTO emp2 VALUES
(new_empno,new_ename,new_job,new_mgr,new_hiredate,
new_sal,new_comm,new_deptno);
INSERT INTO emp3 VALUES
(new_empno,new_ename,new_job,new_mgr,new_hiredate,
new_sal,new_comm,new_deptno);
INSERT INTO emp4 VALUES
(new_empno,new_ename,new_job,new_mgr,new_hiredate,
new_sal,new_comm,new_deptno);
INSERT INTO emp5 VALUES
(new_empno,new_ename,new_job,new_mgr,new_hiredate,
new_sal,new_comm,new_deptno);
END insert_emp;
END emp_mgmt;
Although functionally both examples are the same, in an application where there
are many tables with a large number of columns, reducing a database trigger to
a call to a stored procedure (as in the second case) can greatly reduce the
execution time of the trigger. There are several reasons why this occurs.
First, triggers are not stored in a compiled form prior to Oracle 7.3. When a
trigger is called for the first time in a session, it is necessary to load the
statement and compile it before it can be executed. The larger the trigger the
more contiguous memory is required for the compilation (with the 7.1.5 release
of the server, there will be init.ora parameters to allocate a contiguous area
which may be used for this purpose). Stored procedures on the other hand are
compiled, and stored, in a compiled form upon creation.
As of the 7.0.15.4 (or later) release of the server, it is possible to "pin" or
"keep" packages into the shared pool. The shared pool is an area of memory in
the System Global Area where SQL and PL/SQL statements are stored and shared
between users. Pinning packages in the shared pool means that the package body
(ie the procedures, functions etc.) are not flushed out after a certain period
of time (a process known as ageing-out). In the case of large packages, ageing-
out may mean that it is not possible to re-load it due to fragmentation in the
shared pool.
Note that in some releases it is only possible to pin packages, and not
individual procedures or functions.
Finally, consider the trigger in the previous example which fired after an
insert or an update of the sal column on the emp table. Due to a bug which is
fixed in the 7.1 release of the server, anonymous blocks of PL/SQL are not
shared between users. Triggers are anonymous blocks, stored procedures are not.
This can be demonstrated by performing the following steps:
1. Execute the trigger as one user
2. Type the following query in SQL*Plus when connected as either
SYSTEM or SYS:
SELECT version_count FROM v$sqlarea
WHERE sql_text = 'the_trigger_text';
3. Execute the trigger as another user
4. Re-execute the select statement in step 2 above.
Upon re-executing the select statement, the version_count will have increased,
indicating another copy of the trigger text in the shared pool. Obviously the
larger the trigger text, the more memory is taken up every time a copy of the
trigger is loaded.
So, in summary, it is important that a trigger body consists of no more than:
begin
package.procedure_name(argument_list);
end;
Note also that the trigger body can be prevented from being executed at all
when it is not needed, by the use of a WHEN clause e.g. :
WHEN (new.sal <= old.sal)
begin
<plsql>;
end;
rather than:
begin
if old.sal > new.sal then
<plsql>;
end if;
end;