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.

Answer 2

Even when a CLOB is stored in-line, there is some overhead compared to a standard VARCHAR2, as described in appendix C of the LOB performance guideline.

When the length of a LOB is less than 3964 bytes then it is stored inline with a 36 bytes header. A VARCHAR2 of length X will be stored as X bytes of data with an extra one or two bytes of overhead.

I think this overhead will carry into memory, which means PLSQL CLOB objects will be less efficient than VARCHAR2 of comparable size.

The 34-35 extra bytes will add up as demonstrated with the following script:

SQL> create table test_var(a varchar2(4000));

Table created

SQL> create table test_clob(a clob);

Table created

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    l_time TIMESTAMP := systimestamp;
  3  BEGIN
  4    FOR i IN 1..100000 LOOP
  5      INSERT INTO test_var VALUES (rpad('x', 1000, 'x'));
  6    END LOOP;
  7    dbms_output.put_line(systimestamp - l_time);
  8  END;
  9  /
+000000000 00:00:16.180299000

SQL> DECLARE
  2    l_time TIMESTAMP := systimestamp;
  3  BEGIN
  4    FOR i IN 1..100000 LOOP
  5      INSERT INTO test_clob VALUES (rpad('x', 1000, 'x'));
  6    END LOOP;
  7    dbms_output.put_line(systimestamp - l_time);
  8  END;
  9  /
+000000000 00:00:27.180716000

It takes more time to insert CLOBs, which can be explained by the extra space consumed:

SQL> EXEC dbms_stats.gather_table_stats(USER, 'TEST_VAR');

PL/SQL procedure successfully completed.

SQL> EXEC dbms_stats.gather_table_stats(USER, 'TEST_CLOB');

PL/SQL procedure successfully completed.

SQL> select blocks, table_name from user_tables where table_name like 'TEST_%';

    BLOCKS TABLE_NAME
---------- ------------------------------
     33335 TEST_CLOB
     28572 TEST_VAR

The problem is aggravated when we insert smaller strings:

-- after TRUNCATE tables
SQL> DECLARE
  2    l_time TIMESTAMP := systimestamp;
  3  BEGIN
  4    FOR i IN 1..1000000 LOOP
  5      INSERT INTO test_var VALUES (rpad('x', 10, 'x'));
  6    END LOOP;
  7    dbms_output.put_line(systimestamp - l_time);
  8  END;
  9  /

+000000000 00:00:51.916675000

SQL> DECLARE
  2    l_time TIMESTAMP := systimestamp;
  3  BEGIN
  4    FOR i IN 1..1000000 LOOP
  5      INSERT INTO test_clob VALUES (rpad('x', 10, 'x'));
  6    END LOOP;
  7    dbms_output.put_line(systimestamp - l_time);
  8  END;
  9  /

+000000000 00:01:57.377676000

-- Gather statistics

SQL> select blocks, table_name from user_tables where table_name like 'TEST_%';

    BLOCKS TABLE_NAME
---------- ------------------------------
      7198 TEST_CLOB
      2206 TEST_VAR