Consolidate multiple Oracle triggers. Any performance impact?

Question

Most of our tables have one BEFORE INSERT OR UPDATE trigger to set the BEFORE INSERT ID and set the users / creation / change dates of BEFORE INSERT OR UPDATE .

There are several tables with additional triggers that have been separated from the previous one to make their intentions clearer. In addition, these additional triggers can be disabled regardless of the previous one, which should never be disabled.

In most cases, additional triggers also trigger BEFORE INSERT OR UPDATE , and the order is not important as they span different columns / use cases. Thus, they can be combined with single triggers.

Are there any studies on the call speed of 1 or n triggers per table? Or is this very inappropriate for single-line inserts / updates? In other words, there is only 1 global context switch SQL -> PL/SQL or there will be one context switch per trigger.

Answer 1

Now I am comparing this situation, and I have come to the conclusion that a significant performance loss is most likely due to PL / SQL context switches when adding 1 trigger. Loss of 8 times compared with my figure. However, adding a second “compatible” trigger has no significant effect. By "compatible" I mean that both triggers always fire in the same event in any order.

So, I will conclude that for all triggers, only 1 SQL -> PL/SQL most likely SQL -> PL/SQL context switch

---

Here's the benchmark:

Create table

 -- A typical table with primary key, creation/modification user/date, and -- other data columns CREATE TABLE test( id number(38) not null, -- pk uc varchar2(400) not null, -- creation user dc date not null, -- creation date um varchar2(400), -- modification user dm date, -- modification date data number(38) ); 

... and sequence

 CREATE SEQUENCE s_test; 

Typical trigger installation identifier, create / modify user / date

 CREATE OR REPLACE TRIGGER t_test BEFORE INSERT OR UPDATE ON test FOR EACH ROW BEGIN IF inserting THEN SELECT s_test.nextval INTO :new.id FROM dual; :new.uc := USER; :new.dc := SYSDATE; :new.um := NULL; :new.dm := NULL; END IF; IF updating THEN :new.um := USER; :new.dm := SYSDATE; :new.uc := :old.uc; :new.dc := :old.dc; END IF; END t_test; 

Insert 1000, 10000, 100000 records

 declare procedure run (limit number) is t timestamp; begin t := systimestamp; insert into test (data) select level from dual connect by level < limit; dbms_output.put_line(to_char(systimestamp - t)); rollback; end; begin run(1000); run(10000); run(100000); end; 

results

 -- ------------------------------------ -- +000000000 00:00:00.086603000 -- +000000000 00:00:00.844333000 -- +000000000 00:00:08.429186000 -- ------------------------------------ 

Another "compatible" trigger (execution order does not matter)

 CREATE OR REPLACE TRIGGER t_test_other BEFORE INSERT OR UPDATE ON test FOR EACH ROW BEGIN :new.data := 42; END t_test_other; 

Results of another script test run

 -- ------------------------------------ -- +000000000 00:00:00.088551000 -- +000000000 00:00:00.876028000 -- +000000000 00:00:08.731345000 -- ------------------------------------ 

Deactivate Triggers

 alter trigger t_test disable; alter trigger t_test_other disable; 

Run a slightly different test script

 declare procedure run (limit number) is t timestamp; begin t := systimestamp; insert into test (id, uc, dc, data) select s_test.nextval, user, sysdate, level from dual connect by level < limit; dbms_output.put_line(to_char(systimestamp - t)); rollback; end; begin run(1000); run(10000); run(100000); end; 

results

 -- ------------------------------------ -- +000000000 00:00:00.012712000 -- +000000000 00:00:00.104903000 -- +000000000 00:00:01.043984000 -- ------------------------------------ 

Answer 2

I know there is a significant difference in the generation of a repeat between triggers and one trigger, but not about the difference between 1 and n triggers. However, I can’t imagine that there is no impact, as there will be more context switching between SQL and PL / SQL engines.

Answer 3

I would suggest that you examine the effect of using a sequence in a trigger - when creating a sequence, sow the sequence with a cache value (CACHE indicates how many sequence values ​​the database preallocates and stores in memory for faster access). I experienced factors far superior to 8 related to sequences. In any case, in order to compare the effect of a trigger in terms of context switching, the use of the sequence must either be eliminated for the test or taken into account when evaluating the results.