Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Reducing CLOB concatenation

with 5 comments

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';
 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';
 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.

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.

Written by Dion Cho

August 22, 2009 at 12:25 am

Posted in Misc.

5 Responses

Subscribe to comments with RSS.

  1. Dion

    I posted similar findings and a simple procedure for buffered LOB appends in 2006/7.

    Regards Nigel

    Nigel Thomas

    August 22, 2009 at 9:12 am

    • Nigel.

      Thanks for the sharing.


      Dion Cho

      August 25, 2009 at 9:11 am

  2. Hi Dion:
    But there is a limiation to varchar2 and if it exceeds 4000 this logic will fail. Right?
    Also could you share the query that gave the above ouput from v$sesstat and v$latch.

    Is this an application which takes input from a user and generates the sql based on criteria enterered by user and that is why they are entering the sql text in a parameter? I am curious on how third party applications are handling this kind of scenario where they allow the user to specify different criteria and build the query based on their input. They may hit similar issue.

    Thank you
    – Kumar


    August 25, 2009 at 9:00 am

    • Kumar.

      1. The upper limit of VARCHAR2 variable in PL/SQL is 20,000 byte(if my memory servers it right). Even with this extension, the long string would fail with VARCHAR2.

      2. This is how I collect general performance data.

      -- do something1
      -- do something2

      All the scripts are browseable here.

      3. The customer was in a very unique situation. They were using very complex Java application who execute tens of thousands of short SQL statements to implement complex report viewer, which was very under-performant and inefficient to support. Thus they decided to implement it with one big efficient SQL statement.

      One of the problem is that Oracle does not support that long SQL text with ref cursor(but 11g supports it!). So all they can do was to make very long SQL text and return it to Java client. Seems like very silly appliation, but was much better than previous version. :)

      Dion Cho

      August 25, 2009 at 9:19 am

  3. […] Dion Cho- Reducing CLOB concatenation […]

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: