Reducing CLOB concatenation

One of my customers had this requirement – producing a SQL statement dynamically whose text length is over 300KB(Do not blame me for this).

Their first PL/SQL code was like following.
(Let me tell you this is a very simplified version of their code)

UKJA@ukja1021> create or replace function make_long_sql(p_idx in number)
  2  return clob
  3  is
  4  	     v_clob	     clob;
  5  begin
  6  	     v_clob := 'select ';
  7  	     for idx in 1 .. p_idx loop
  8  		     v_clob := v_clob||to_char(p_idx)||', '||to_char(p_idx+1)||','||to_char(p_idx+2)||',';
  9  	     end loop;
 10  	     v_clob := v_clob || '1 from dual';
 11  
 12  	     return v_clob;
 13  end;
 14  /

Function created.

No special things. The code is just concatenating CLOB variable with a couple of strings.

But the performance was very disappointing. Following code takes 46sec.

UKJA@ukja1021> exec :c := make_long_sql(10000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:46.00  

Their actual PL/SQL code took over 5 minutes to make 150KB of SQL text. So they called me for help.

My prescription was to use temporary VARCHAR2 variable instead of direct CLOB concatenation, like following.

UKJA@ukja1021> create or replace function make_long_sql2(p_idx in number)
  2  return clob
  3  is
  4  	     v_clob	     clob;
  5  	     v_varchar	     varchar2(1000);  -- Note this
  6  begin
  7  	     v_clob := 'select ';
  8  	     for idx in 1 .. p_idx loop
  9  		     v_varchar := to_char(p_idx)||', '||to_char(p_idx+1)||','||to_char(p_idx+2)||',';  -- Use temporary VARCHAR2
 10  		     v_clob := v_clob||v_varchar;
 11  	     end loop;
 12  	     v_clob := v_clob || '1 from dual';
 13  
 14  	     return v_clob;
 15  end;
 16  /

Function created.

Here I have a dramatic result. The elapsed time got reduced from 42 sec to 0.5 sec.

UKJA@ukja1021> exec :c := make_long_sql2(10000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.50

Their actual PL/SQL code now takes 5 sec instead of 5 min.

What makes this big difference? V$SESTAT view and V$LATCH view show some hints for this performance variation.

NAME                                           VALUE1       VALUE2         DIFF
---------------------------------------- ------------ ------------ ------------
session logical reads                      17,376,991      133,035  -17,243,956
db block gets from cache                   15,764,933      102,971  -15,661,962
db block gets                              15,764,933      102,971  -15,661,962
consistent changes                          6,024,802       17,697   -6,007,105
db block changes                            6,024,814       17,710   -6,007,104
consistent gets from cache                  1,612,058       30,064   -1,581,994
consistent gets                             1,612,058       30,064   -1,581,994
free buffer requested                       1,432,143           49   -1,432,094
calls to get snapshot scn: kcmgss             480,030       40,019     -440,011
lob writes                                    120,008       10,004     -110,004
lob writes unaligned                          119,962       10,004     -109,958
lob reads                                      60,001       10,001      -50,000
...

LATCH_NAME                         D_GETS   D_MISSES   D_SLEEPS  D_IM_GETS
------------------------------ ---------- ---------- ---------- ----------
cache buffers chains            -45091211          0          0   -1399062
object queue header operation    -2864253          0          0          0
cache buffers lru chain          -2864138          0          0          0
simulator hash latch              -164417          0          0          0
simulator lru latch               -112459          0          0     -51952
...

Do you see the difference between logical reads, LOB reads and LOB writes? Also of the latch acquistion?

Jonathan Lewis has already given a good explanation here.
http://jonathanlewis.wordpress.com/2009/07/09/concatenating-lobs/

There are a couple of things you should keep in mind when using CLOB for producing a big SQL statement, and this post is just abount one of them. I would cover other stuffs in detail next time.

About these ads