合并几个Oracle触发器.对性能有影响吗? [英] Consolidate several Oracle triggers. Any performance impact?

问题描述

我们的大多数表都有一个BEFORE INSERT OR UPDATE触发器，以便设置ID的BEFORE INSERT并设置创建/修改用户/日期BEFORE INSERT OR UPDATE.

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.

在大多数情况下，其他触发器也会触发BEFORE INSERT OR UPDATE，并且顺序并不重要，因为它们涵盖了不同的列/用例.因此，通常，它们可以组合为单个触发器.

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.

是否有关于在表上调用1或n触发器的速度的研究?还是与单行插入/更新几乎没有关系?换句话说，是否只有1个全局SQL -> PL/SQL上下文切换，或者每个触发器将只有1个上下文切换.

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.

推荐答案

我现在已经对这种情况进行了基准测试，得出的结论是，添加1个触发器时，很可能由于PL/SQL上下文切换而导致性能显着下降.在我的基准中，损失是第8倍.但是，添加第二个兼容"触发器不再具有任何重大影响. 兼容"是指两个触发器始终以任意顺序在同一事件中触发.

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.

所以我得出结论，所有触发器很可能只有1个SQL -> PL/SQL上下文切换

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

这里是基准:

-- 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)
);

...和序列

CREATE SEQUENCE s_test;

典型的触发器设置ID，创建/修改用户/日期

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;

插入1000、10000、100000条记录

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;

结果

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

另一个兼容"触发器(与执行顺序无关)

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

再次运行测试脚本的结果

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

停用触发器

alter trigger t_test disable;
alter trigger t_test_other disable;

运行稍微不同的测试脚本

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;

结果

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

这篇关于合并几个Oracle触发器.对性能有影响吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！