Consolidate several Oracle triggers. Any performance impact?

Question

Most of our tables have one BEFORE INSERT OR UPDATE trigger, in order to set ID's BEFORE INSERT and to set creation / modification users / dates BEFORE INSERT OR UPDATE.

There are a couple of tables with additional triggers, which were separated from the previous one in order to make their intent more clear. Also, those additional triggers could be disabled independently from the previous one, which should never be disabled.

In most cases, the additional triggers also fire BEFORE INSERT OR UPDATE and the order is unimportant, as they cover different columns / use-cases. So generally, they could be combined to single triggers.

Are there any studies about the speed of calling 1 or n triggers on a table? Or is that pretty much irrelevant for single-row inserts / updates? In other words, is there only 1 global SQL -> PL/SQL context switch, or will there be 1 context switch per trigger.

Answer 1

I have now benchmarked this situation and I came to the conclusion that there is a significant performance loss most likely due to PL/SQL context switches, when adding 1 trigger. The loss is by factor 8 in my benchmark. Adding a second "compatible" trigger, however, doesn't have any significant impact anymore. By "compatible", I mean both triggers always fire at the same event in any order.

So I'm concluding that there is most likely only 1 SQL -> PL/SQL context switch for all triggers

---

Here's the benchmark:

Create a 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 a sequence

CREATE SEQUENCE s_test;

A typical trigger setting ID, creation/modification 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 irrelevant)

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 run of the test script

-- ------------------------------------
-- +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
-- ------------------------------------