合并多个 Oracle 触发器.任何性能影响? [英] Consolidate several Oracle triggers. Any performance impact?

问题描述

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

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屋！