CLOB Concatenation

Adding string data to a CLOB in Oracle can be a tad slow, when compared to other string concatenation situations.  I first became aware of this concatenation performance issue from my own coding experience, but also from threads on Oracle Technical Network and a blog post by Jonathan Lewis.

The reason for this, Jonathan’s article highlights…

Essentially, every time you add a piece on the end of the LOB, Oracle creates and updates a new temporary LOB (hence two lob writes). Since they are temporary LOBs – associated therefore with the temporary tablespace – all those “db block changes” don’t cause any redo to be generated, but the overhead is significant.

A few months ago, during a thread on OTN, where Jonathan and I were both contributors, CLOB concatenation came up again. I decided to look at what strategy was the best approach when concatenating to a CLOB, so I created the following tests.  This concatenates a large string to a clob variable in various ways and I take some metrics to see which one performs the best.

Run on Oracle 11.2.0.2.0

declare
  vStart timestamp;
  vClob clob;
  vClobVar clob;
  vMethod varchar2(100);
  vVarch varchar2(32767);
  ITERATIONS constant pls_integer := 10000;
  STR_LEN constant integer := 32000;
  cursor curStats is
    select *
    from (
      select n.name, m.value value
      from   v$mystat    m
      join   v$statname  n on m.statistic# = n.statistic#
      where  n.name in (
        'session logical reads',
        'db block gets',
        'consistent gets',
        'db block changes',
        'consistent changes',
        'free buffer requested',
        'lob reads',
        'lob writes'
        )
      )
    pivot (
      min(value)
      for name in (
        'session logical reads' session_logical_reads,
        'db block gets'         db_block_gets,
        'consistent gets'       consistent_gets,
        'db block changes'      db_block_changes,
        'consistent changes'    consistent_changes,
        'free buffer requested' free_buffer_requested,
        'lob reads'             lob_reads,
        'lob writes'            lob_writes
        )
    );
  vLastStats curStats%ROWTYPE;
  function VersionInfo return varchar2 is
    vResult varchar2(80);
  begin
    select banner
    into vResult
    from v$version
    where banner like 'Oracle%';
    return vResult;
  end;
  function GetStats return curStats%ROWTYPE is
  -- Cursor curStats was defined to give %ROWTYPE record, so have to fetch from it
    vStats curStats%ROWTYPE;
  begin
    open curStats;
    fetch curStats into vStats;
    close curStats;
    return vStats;
  end;
  function OPStats(pStats curStats%ROWTYPE) return varchar2 is
  begin
    return ', session_logical_reads = '   || to_char(pStats.session_logical_reads   - vLastStats.session_logical_reads  )||
           ', db_block_gets = '           || to_char(pStats.db_block_gets           - vLastStats.db_block_gets          )||
           ', consistent_gets = '         || to_char(pStats.consistent_gets         - vLastStats.consistent_gets        )||
           ', db block changes = '        || to_char(pStats.db_block_changes        - vLastStats.db_block_changes       )||
           ', consistent changes = '      || to_char(pStats.consistent_changes      - vLastStats.consistent_changes     )||
           ', free buffer requested = '   || to_char(pStats.free_buffer_requested   - vLastStats.free_buffer_requested  )||
           ', lob_reads = '               || to_char(pStats.lob_reads               - vLastStats.lob_reads              )||
           ', lob_writes = '              || to_char(pStats.lob_writes              - vLastStats.lob_writes             );
  end;
  procedure RunTest(pMethod varchar2 default null) is
  begin
    if vMethod is not null then
      dbms_output.put_line (to_char (systimestamp - vStart)|| ' : '||vMethod||OPStats(GetStats));
    end if;
    if pMethod is null then
      vClob := empty_clob;  -- Deallocate (probably not necessary as will fall out of scope but anyway...)
    else
      vMethod    := pMethod;
      vStart     := systimestamp;
      vClob      := ' ';  -- Some tests like DBMS.Append need the clob initialised, so for consistency do for all
      vLastStats := GetStats;
    end if;
  end;
begin
  dbms_output.put_line (VersionInfo);
  dbms_output.put_line ('Iterations = '||ITERATIONS||', StrLen = '||STR_LEN);
  --vLastStats := GetStats;
  RunTest('CLOB := CLOB || VARCHAR2');
  for i in 1..ITERATIONS loop
    vClob := vClob || lpad('x', STR_LEN, 'x');
  end loop;
----------------
  RunTest('CLOB := CLOB || VARCHAR2_VARIABLE');
  for i in 1..ITERATIONS loop
    vVarch := lpad('x', STR_LEN, 'x');
    vClob  := vClob || vVarch;
  end loop;
----------------
  RunTest('CLOB := CLOB || TO_CLOB(VARCHAR2)');
  for i in 1..ITERATIONS loop
    vClob := vClob || to_clob (lpad('x', STR_LEN, 'x'));
  end loop;
----------------
  RunTest('CLOB := CLOB || CLOB_VARIABLE');
  for i in 1..ITERATIONS loop
    vClobVar := lpad('x', STR_LEN, 'x');
    vClob := vClob || vClobVar;
  end loop;
----------------
  RunTest('DBMS_LOB.Append');
  for i in 1..ITERATIONS loop
    dbms_lob.append (vClob, lpad('x', STR_LEN, 'x'));
  end loop;
----------------
  RunTest('DBMS_LOB.WriteAppend');
  for i in 1..ITERATIONS loop
    vVarch := lpad('x', STR_LEN, 'x');
    dbms_lob.writeappend (vClob, length(vVarch), vVarch);
  end loop;
----------------
  RunTest;  -- Ends test
end;


Interval

Method

session_logical_reads

db_block_gets

consistent_gets

db_block_changes

consistent_changes

free_buffer_requested

lob_reads

lob_writes
+000000000 00:00:01.422000000 CLOB := CLOB || VARCHAR2 374316 364316 10000 134083 134083 39401 0 10000
+000000000 00:00:02.422000000 CLOB := CLOB || VARCHAR2_VARIABLE 332017 322017 10000 118831 118831 39353 0 10000
+000000000 00:00:01.734000000 CLOB := CLOB || TO_CLOB(VARCHAR2) 411387 322037 89350 118837 118837 39359 0 10001
+000000000 00:00:02.344000000 CLOB := CLOB || CLOB_VARIABLE 411408 322058 89350 118844 118844 39356 0 10001
+000000000 00:00:03.329000000 DBMS_LOB.Append 771375 682025 89350 258831 258831 79352 0 20000
+000000000 00:00:02.343000000 DBMS_LOB.WriteAppend 332025 322025 10000 118831 118831 39351 0 10000

Here’s a description of the metrics. Note, I’ve tried to give an indicator as to whether something is a good thing when it is low or high (although it’s not always a simple low or high as there are interdependence considerations, but I’ve tried to anyway).

Session Logical Reads = number of read requests for a data block from the SGA. Logical reads may result in a physical read if the requested block does not reside with the buffer cache.
Low can be good if not resulting in physical reads.

Consistent Gets = a normal reading of a block from the buffer cache. A check will be made if the data needs reconstructing from rollback info to give you a view consistent at a point in time, taking into account your changes and other people’s changes, commited or not, but most of the time a reconstruction is not needed, so the block is just got from the cache.
Low is good as it means not much checking for reconstruction has been needed to get a current block from the buffer cache.

DB Block Gets = Number of CURRENT blocks accessed in the buffer cache for INSERT, UPDATE, DELETE, and SELECT FOR UPDATE statements. The value for this statistic plus the value of consistent gets statistic constitute what is referred to as logical read.
Higher when compared to Consistent Gets is good.

Consistent Changes = The number of times a database block has applied rollback entries to perform a consistent read on the block. Work loads that produce a great deal of consistent changes can consume a great deal of resources.
Low is good.

Free Buffer Requested = The count of the number of times a reusable buffer or a free buffer was requested to create or load a block.
Low is good.

Lob Reads = the number of LOB API read operations performed in the session/system. A single LOB API read may correspond to multiple physical/logical disk block reads.
Low is good.

Lob Writes = the number of LOB API write operations performed in the session/system. A single LOB API write may correspond to multiple physical/logical disk block writes.
Low is good.

Now on deciding which is the best approach, we have to consider performance as well as resource usage. Why? Well because performant processes often rely on resources to achieve that speed. Resource usage can create more I/O (logical or physical), can eat more memory and CPU cycles – this can and will affect other parts of the system. In other words, a fast CLOB operation may cause a slowing down for someone or something else on your database and that isn’t a good thing. With this in mind, the winner on balance of performance and resources is DBMS_LOB.WriteAppend. Not only is it fast, but it performs well across the resources metrics.

DBMS_LOB.WriteAppend

Although DBMS_LOB.WriteAppend is the best way to concatenate to a CLOB it isn’t without its issues.

It cannot be used to append to an empty clob.

declare
  vClob clob := empty_clob();
  vVarch varchar2(10) := ' ';
begin
  DBMS_LOB.WriteAppend(vClob, length(vVarch), vVarch);
end;

Error at line 1
ORA-22275: invalid LOB locator specified
ORA-06512: at "SYS.DBMS_LOB", line 1146
ORA-06512: at line 5

You cannot append an empty string to it.

declare
  vClob clob := ' ';
  vVarch varchar2(10);
begin
  DBMS_LOB.WriteAppend(vClob, length(vVarch), vVarch);
end;

Error at line 8
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_LOB", line 1146
ORA-06512: at line 5

With these two issues in mind, I’ve written my own WriteAppend procedure. I’ve also added buffered writes, which improves further on WriteAppend by using a varchar2 buffer, and only flushing when it reaches the buffer limit.

create or replace procedure WriteAppend(pClob        in out nocopy clob,
                                        pBuffer      in out nocopy varchar2,
                                        pText        in varchar2,
                                        pFlushBuffer in boolean := False) is
  vFlushBuffer boolean := pFlushBuffer;
begin
  if not pFlushBuffer then
    begin
      pBuffer := pBuffer || pText;
    exception
      when VALUE_ERROR then
        vFlushBuffer := True;
    end;
  end if;
  if vFlushBuffer then
    if DBMS_LOB.GetLength(pClob) = 0 then
      pClob := pBuffer;
    else
      if length(pBuffer) > 0 then
        DBMS_LOB.WriteAppend(pClob, length(pBuffer), pBuffer);
      end if;
    end if;
    pBuffer := pText;
  end if;
end;

Here’s an example of it in use, writing 50MB of data in about a second…

declare
  vClob clob;
  vBuffer varchar2(32767);
begin
  DBMS_LOB.CreateTemporary(vClob, True);
  for n in 1..10000
  loop
    WriteAppend(vClob, vBuffer, lpad(to_char(n), n, to_char(n)));
  end loop;
  WriteAppend(vClob, vBuffer, null, True); -- Always remember to flush the buffer, in case there's anything left in it!
  DBMS_OUTPUT.PUT_LINE ( 'vClob length = ' || DBMS_LOB.GetLength(vClob) );
end;

In my tests, the speed improvement of using the buffered wrapper WriteAppend procedure approach is anywhere from 15% upwards, however the real benefit is the dramatic lessening of resources used.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s