Performance problems concatenating LOBs

Greg Pike talks about some potential performance pitfalls with LOBs; I would comment on his blog, but it's a login only. Anyway, I posted on these problems last year (specifically with DBMS_LOB.APPEND), and posted some code to deal with it. Batching up appends makes a big difference - a 15-20 fold improvement, in the example shown (against 9.2.0.5).

Buffered LOB_APPEND

Jens Ulrik asked to see the code for a buffered LOB_APPEND mentioned in an earlier post LOB_APPEND eats resources....

Here it is as part of a package. Apologies for the rush job at the time; inelegant, but it was worth it...

    gi_chunk_length integer := 0;
    gv_chunk varchar2(32767);

    -- add a character value to the CLOB being built up; catch null values
  -- NOTE - final flush is done elsewhere
  --        and we assume max(length(av_value))  < ki_maxchunksize

    PROCEDURE lob_append (at_doc_clob IN OUT NOCOPY CLOB, av_value IN VARCHAR2)
    IS
     li_value_length integer;
    li_new_chunk_length integer;
    ki_maxchunksize constant integer := 32000;
    BEGIN
        -- prt_common_pkg.print_xml_string(av_value);
        IF av_value IS NOT NULL THEN
        li_value_length := length(av_value);
    li_new_chunk_length := gi_chunk_length + li_value_length;
        
    if li_new_chunk_length >= ki_maxchunksize 
    then
      -- pad out the chunk as much as possible
      gv_chunk := gv_chunk || substr(av_value,1,ki_maxchunksize - gi_chunk_length);
      -- write out
      DBMS_LOB.append (at_doc_clob, gv_chunk);
      -- initialise remainder into next chunk
      gi_chunk_length := li_new_chunk_length - ki_maxchunksize;
      gv_chunk := substr(av_value,li_value_length +1 - gi_chunk_length);
    else
      gv_chunk := gv_chunk || av_value;
      gi_chunk_length := li_new_chunk_length;
    end if;
        ELSE
            prt_common_pkg.LOG ('lob_append null ignored',
                                prt_common_pkg.klog_ret
                               );
        END IF;
    END;

The final flush of the last chunk of a LOB is called like this:

    
    IF gi_chunk_length > 0 THEN
       -- flush out the last chunk of the CLOB
     dbms_lob.append(at_doc_clob, gv_chunk);
     gi_chunk_length := 0;
     gv_chunk := null;
    END IF;

DBMS_LOB.APPEND eats resources on 9.2.0.5

We're generating and storing XML data into CLOB columns as part of our reporting solution. We've been aware for a while that there are some performance issues with LOBs in 9.2.0.5, eg:

• 4280096 - HIGH BUFFER GETS FOR INSERT INTO LOB (fixed 9.2.0.8, 10.1.0.5 and 10.2)
• 3315549 - POOR PERFORMANCE WITH USING DBMS_LOB.WRITEAPPEND IN PLSQL LOOP
• 2602264 - DBMS_LOB.WRITEAPPEND() POOR PERFORMANCE WITH LARGE LOB AND SMALL CHUNK SIZE
• 2420525 - DBMS_LOB.WRITEAPPEND() PERFORMANCE ISSUES
• 2595083 - (description not published, although it's a base bug for some of the previous
  ones)

But I'd never sat down and actually measured how much difference it can make - until today. My test appended a short string (around 35 characters) 10,000 times, using each of DBMS_LOB.append, DBMS_LOB.WriteAppend, and TEST.LOB_APPEND - a procedure that buffers the appended strings, and only flushes to the CLOB when the buffer is full:

for i in 1..:loopcount loop
dbms_lob.append(lt_doc_clob, '('||i||')'||lv_padding);
end loop;

Here are the results from tkprof, for 10000 iterations, 30 chars of padding:

call	count	cpu	elapsed	disk	query	current	rows
Append	1	1.57	1.96	0	20087	194567	1
WriteAppend	1	0.45	0.48	0	9999	74343	1
MyAppend	1	0.10	0.10	0	182	2485	1

Repeating 1,000,000 times, timing from SQL*Plus, and trying to help by wrapping the whole lot in DBMS_LOB.OPEN and DBMS_LOB.CLOSE:



dbms_lob.append without open/close: 00:03:38.07
dbms_lob.append with open/close: 00:03:40.07
Simple dbms_lob.writeappend: 00:01:12.02
Append with buffering: 00:00:10.08


We used APPEND for lazy reasons (no need to keep working out the length of the buffer, which WRITEAPPEND needs) - but the subsequent cast from VARCHAR2 to CLOB clearly contributes substantially to the problem.

In our case these LOBs are also being manipulated by many concurrent sessions on a RAC environment - so we've seen evidence of (space management) contention for the LOB segments. Reducing lio's seems to help more than a little... at least while we wait for our client to upgrade to the latest patch - which doesn't look likely any time soon!