Inserting into CLOB very very slow

Question

I am experiencing a significant performance drop when inserting string of 'TinyString' (just an example string) into stored-in-row CLOB, as compared to VARCHAR2. My understanding is that when storing data of < 4000 bytes into a CLOB with STORAGE IN ROW enabled, the data is effectively stored in the same manner as VARCHAR2 (unless it 'overflows' 4000bytes) and there should be no significant performance drop. However, my benchmarking procedure* shows that inserting same data into CLOB is 15 times slower than inserting into VARCHAR2.

Have a look at the code below:

I have got a number of tables, each of which has a COMPOUND TRIGGER attached similar to the one below:

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;

Upon any operation on target_table, the above trigger stores data populated in coll_events_hist type into log_table, which is defined in a following way:

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

My setup is: Windows 7 SP1, Oracle 11g

*My benchamrking procedure iterates 10 times updating 21k rows on target_table in each iteration.

Answer 1

in your case is "tinystring" always <32767?

Your time is going to be wasted in the FORALL part looking up all the temporary lobs you've made.

you'd find better performance with inserts in the for each row part :

eg on my test system with your lob trigger:

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

10000 rows created.

Elapsed: 00:00:10.49

vs having the trigger as:

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

if you KNOW your strings are always <32k you can keep the forall to get that speed boost if you create your trigger as:

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

i.e. defer the lob operation until the insert.