与VARCHAR2相比,将'TinyString'字符串(仅是示例字符串)插入行中存储的CLOB时,我遇到了显着的性能下降。我的理解是,在启用STORAGE IN ROW的情况下将比插入VARCHAR2的慢15倍。

看下面的代码:

我有很多表,每个表都附有一个COMPOUND TRIGGER,类似于下面的表:

CREATE OR REPLACE TRIGGER mdhl_basic_trigger_compound
  FOR INSERT OR UPDATE OR DELETE ON target_table

  COMPOUND TRIGGER TYPE EVENTS_HIST IS TABLE OF log_table%ROWTYPE INDEX BY PLS_INTEGER;
                                                coll_events_hist EVENTS_HIST;
                                                ctr PLS_INTEGER := 0;
                                                my_bgroup VARCHAR2(3);

  BEFORE EACH ROW IS
    BEGIN

      IF INSERTING OR UPDATING THEN
        my_bgroup  := :NEW.BGROUP;
      ELSE
        my_bgroup  := :OLD.BGROUP;
      END IF;

      ctr := ctr + 1;
      coll_events_hist(ctr).BGROUP := my_bgroup;
      coll_events_hist(ctr).TABLE_NAME := 'BASIC_MDHL';
      coll_events_hist(ctr).EVENT_TS := current_timestamp;
      coll_events_hist(ctr).EVENT_RAW := 'TinyString';

  END BEFORE EACH ROW;

  AFTER STATEMENT IS
    BEGIN
      FORALL counter IN 1 .. coll_events_hist.count()
           INSERT INTO log_table VALUES coll_events_hist(counter);
  END AFTER STATEMENT;
END mdhl_basic_trigger_compound;

target_table进行任何操作时,上述触发器都会将coll_events_hist类型填充的数据存储到log_table中,该数据通过以下方式定义:
CREATE TABLE "USERNAME"."LOG_TABLE"
   (  "BGROUP" VARCHAR2(3) NOT NULL ENABLE,
        "TABLE_NAME" VARCHAR2(255) NOT NULL ENABLE,
      "EVENT_TS" TIMESTAMP (7) DEFAULT current_timestamp,
      "EVENT_RAW" CLOB
   )
  SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
 LOB ("EVENT_RAW") STORE AS BASICFILE "EV_RAW_SEG"(
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 16384 PCTVERSION 5
  CACHE
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

我的设置是:
Windows 7 SP1,
甲骨文11g

*我的基准测试过程在每次迭代中迭代10次以更新target_table上的21k行。

最佳答案

在您的情况下,“tinystring”总是
在FORALL部分中,浪费的时间会浪费在查找已创建的所有临时吊顶上。

您会发现在每行部分插入会获得更好的性能:

例如在我的测试系统上,您的lob触发器是:

SQL> insert into target_Table select 'ABC' from dual connect by level <= 10000;

10000 rows created.

Elapsed: 00:00:10.49

与触发为:
SQL> CREATE OR REPLACE TRIGGER mdhl_basic_trigger
  2    before INSERT OR UPDATE OR DELETE ON target_table for each row
  3  declare
  4
  5  my_bgroup VARCHAR2(3);
  6
  7    v_timer2 number := 0;
  8    v_timer number;
  9  BEGIN
 10
 11        IF INSERTING OR UPDATING THEN
 12          my_bgroup  := :NEW.BGROUP;
 13        ELSE
 14          my_bgroup  := :OLD.BGROUP;
 15        END IF;
 16
 17        INSERT INTO log_table VALUES(my_bgroup, 'BASIC_MDHL', current_timestamp, 'TinyString');
 18
 19  END mdhl_basic_trigger;
 20  /

SQL> insert into target_Table select 'ABC' from dual connect by level <= 10000;

10000 rows created.

Elapsed: 00:00:01.18

如果您知道您的字符串始终小于32k,那么如果将触发器创建为以下内容,则可以让所有人保持速度提升:
SQL> CREATE OR REPLACE TRIGGER mdhl_basic_trigger_compound
  2    FOR INSERT OR UPDATE OR DELETE ON target_table
  3
  4     COMPOUND TRIGGER
  5
  6     type events_rec is record (BGROUP VARCHAR2(3),
  7          TABLE_NAME VARCHAR2(255) ,
  8        EVENT_TS TIMESTAMP (7),
  9        EVENT_RAW varchar2(32767));
 10     TYPE EVENTS_HIST IS TABLE OF events_rec INDEX BY PLS_INTEGER;
 11     coll_events_hist EVENTS_HIST;
 12     ctr PLS_INTEGER := 0;
 13     my_bgroup VARCHAR2(3);
 14
 15  v_timer2 number := 0;
 16  v_timer number;
 17    BEFORE EACH ROW IS
 18      BEGIN
 19
 20        IF INSERTING OR UPDATING THEN
 21          my_bgroup  := :NEW.BGROUP;
 22        ELSE
 23          my_bgroup  := :OLD.BGROUP;
 24        END IF;
 25
 26        ctr := ctr + 1;
 27        coll_events_hist(ctr).BGROUP := my_bgroup;
 28        coll_events_hist(ctr).TABLE_NAME := 'BASIC_MDHL';
 29        coll_events_hist(ctr).EVENT_TS := current_timestamp;
 30        coll_events_hist(ctr).EVENT_RAW := 'TinyString';
 31
 32    END BEFORE EACH ROW;
 33
 34    AFTER STATEMENT IS
 35      BEGIN
 36  v_timer := dbms_utility.get_time;
 37        FORALL counter IN 1 .. coll_events_hist.count()
 38             INSERT INTO log_table VALUES coll_events_hist(counter);
 39  v_timer2 := v_timer2 + (dbms_utility.get_time - v_timer);
 40             dbms_output.put_line(v_timer2/100);
 41    END AFTER STATEMENT;
 42  END mdhl_basic_trigger_compound;
 43  /
SQL> insert into target_Table select 'ABC' from dual connect by level <= 10000;

10000 rows created.

Elapsed: 00:00:00.39

即将lob操作推迟到插入为止。

10-07 19:42
查看更多