title: Inserting into CLOB very very slow
body: <p>I am experiencing a significant performance drop when inserting string of <code>'TinyString'</code> (just an example string) into stored-in-row CLOB, as compared to VARCHAR2. My understanding is that when storing data of &lt; 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 <strong>15 times slower</strong> than inserting into VARCHAR2.</p>
  
  <p>Have a look at the code below:</p>
  
  <p>I have got a number of tables, each of which has a COMPOUND TRIGGER attached similar to the one below:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Upon any operation on <code>target_table</code>, the above trigger stores data populated in <code>coll_events_hist</code> type into <code>log_table</code>, which is defined in a following way:</p>
  
  <pre><code>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))
  </code></pre>
  
  <p>My setup is:
  Windows 7 SP1, 
  Oracle 11g</p>
  
  <p>*My benchamrking procedure iterates 10 times updating 21k rows on target_table in each iteration.</p>