17. März 2015

String-Operationen auf CLOBs - richtig schnell machen!

Making string operations on a CLOB really fast
Dieses Blog Posting behandelt das Thema CLOB, Stringoperationen und PL/SQL - es geht um verschiedene Varianten, wie man mit einer PL/SQL Prozedur, per Zeichenverkettung, einen CLOB zusammenbauen kann. Das kommt in der Praxis ja gar nicht so selten vor. Interessant ist, dass die Performance je nach gewähltem Ansatz wirklich völlig verschieden sein kann. In meinem Beispiel braucht - bei gleichen Datenmengen - die langsamste Methode über 4 Minuten, wogegen die schnellste in 0.2 Sekunden fertig ist.
Zunächst zur Aufgabe: Die etwa 920.000 Zeilen der Demotabelle SALES im Schema SH ...
SQL> select * from sh.sales 

PROD_ID CUST_ID TIME_ID             CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
------- ------- ------------------- ---------- -------- ------------- -----------
     13     987 10.01.1998 00:00:00          3      999             1        1232
     13    1660 10.01.1998 00:00:00          3      999             1        1232
     13    1762 10.01.1998 00:00:00          3      999             1        1232
     13    1843 10.01.1998 00:00:00          3      999             1        1232
     13    1948 10.01.1998 00:00:00          3      999             1        1232
     13    2273 10.01.1998 00:00:00          3      999             1        1232
      :       :                   :          :        :             :           : 
... sollen, semikolon-separiert, in einen CLOB geladen werden.
13;987;10.01.1998 00:00:00;3;999;1;1232,16
13;1660;10.01.1998 00:00:00;3;999;1;1232,16
13;1762;10.01.1998 00:00:00;3;999;1;1232,16
13;1843;10.01.1998 00:00:00;3;999;1;1232,16
13;1948;10.01.1998 00:00:00;3;999;1;1232,16
:
Damit die Tests nicht so lange dauern, beschränke ich mich bei meinen Tests auf die ersten 10.000 Zeilen. Der erste Versuch ist der naivste, ganz einfach "herunterprogrammiert": Ein impliziter Cursor wird geöffnet und in der Cursor-Loop wird der CLOB Schritt für Schritt zusammenkonkateniert. Man kann das mit einen CLOB tatsächlich genauso machen wie mit einem VARCHAR2.
declare
  l_clob clob := '';
begin
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
   l_clob := l_clob || to_char(i.PROD_ID) || ';' || 
                       to_char(i.CUST_ID) || ';' ||  
                       to_char(i.TIME_ID) || ';' ||  
                       to_char(i.CHANNEL_ID) || ';' || 
                       to_char(i.PROMO_ID) || ';' || 
                       to_char(i.QUANTITY_SOLD) || ';' || 
                       to_char(i.AMOUNT_SOLD)|| chr(10);
  end loop;
  dbms_output.put_line(length(l_clob));
  dbms_output.put_line(substr(l_clob, 1, 400));
end;
/
sho err
Da wir schon im Vorfeld wissen, dass das Ergebnis größer als 32.767 Byte sein wird, ist die Variable l_clob nicht vom Typ VARCHAR2, sondern CLOB. Trotzdem kann man in PL/SQL, ganz normal, mit dem || zur Zeichenverkettung arbeiten. Allerdings läuft diese Prozedur lange - allein für 10.000 Zeilen braucht sie etwa 4 Minuten. Über die Laufzeit für alle 920.000 Zeilen möchte ich gar nicht nachdenken.
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:04:23.60
Anhand der Data Dictionary-View V$TEMPORARY_LOBS kann man feststellen, dass, während die Prozedur läuft, temporäre LOBs entstehen. Ein temporärer LOB ist ein LOB-Objekt, was nicht in einer Tabelle liegt, sondern nur transient existiert - beispielsweise in einer PL/SQL-Variable. Ein LOB ist immer persistent - in einer Tabelle - oder temporär.
SQL> select * from v$temporary_lobs

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ------------ -------------
        20          0            0             0
        38          0            0             0
       272          6            0             0
Tatsächlich läuft obiger Code nicht optimal. Vor allem das Anhängen der einzelnen Tabellenspalten an den CLOB sorgt dafür, dass sehr viele Konvertierungen von VARCHAR2 nach CLOB stattfinden, und dass sehr viele LOB-Operationen erfolgen. Generell ist das Anhängen eines Strings an einen CLOB wesentlich teurer als an einen VARCHAR2 - hier der Beweis: Zunächst 30.000 Zeichenverkettungen auf einem CLOB ...
declare
  v_result clob;
begin
  for i in 1..30000 loop
    v_result := v_result || '*';
  end loop;
end;
/

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.56
... dann 30.000 Zeichenverkettungen mit einem VARCHAR2 ...
declare
  v_result varchar2(32000);
begin
  for i in 1..30000 loop
    v_result := v_result || '*';
  end loop;
end;
/

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.01
Das scheint zumindest etwa der Faktor 60 zu sein; wenn nicht sogar noch mehr. Der nächste Schritt wäre also, dafür zu sorgen, dass unser Code weniger LOB-Verkettungen und mehr VARCHAR2-Verkettungen macht. Das ist auch ganz einfach: Eine Tabellenzeile wird zuerst in eine Variable vom Typ VARCHAR2 geladen und erst dann an den CLOB angehängt ...
declare
  l_clob clob := '';
  l_str  varchar2(32000);
begin
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
    l_str := to_char(i.PROD_ID) || ';' || 
             to_char(i.CUST_ID) || ';' ||  
             to_char(i.TIME_ID) || ';' ||  
             to_char(i.CHANNEL_ID) || ';' || 
             to_char(i.PROMO_ID) || ';' || 
             to_char(i.QUANTITY_SOLD) || ';' || 
             to_char(i.AMOUNT_SOLD)|| chr(10);
    l_clob := l_clob || l_str;
  end loop;
  dbms_output.put_line(length(l_clob));
  dbms_output.put_line(substr(l_clob, 1, 400));
end;
/
sho err
Das Ergebnis kann sich sehen lassen ...
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:01.61
Damit sind wir von 4 Minuten auf 2 Sekunden herunter - das ist stark. Die ganze Datenmenge von 920.000 Zeilen wird dann also etwa 3 Minuten brauchen. Man kann sogar schon nachdenken, hier aufzuhören ... aber wir sind noch nicht fertig.
Wieder schauen wir in die Dictionary View V$TEMPORARY_LOBS, und diesmal zeigt sie nur noch zwei temporäre LOBs an (vorher waren es sechs). Wenn man aber genauer nachdenkt, so müsste einer eigentlich ausreichen. Es braucht einen einzigen temporären LOB (für das Ergebnis); an den wird alles angehängt. Der PL/SQL Code braucht aber zwei. Nun, im PL/SQL-Code haben wir die Syntax für VARCHAR2 verwendet und es der Engine überlassen, das korrekt auf den CLOB anzuwenden. Machen wir die Arbeit doch mal selbst: Alle Zeichenoperationen, die auf dem CLOB stattfinden, sind nun mit dem Paket DBMS_LOB realisiert. Außerdem legen wir unseren temporären LOB (ebenfalls mit DBMS_LOB) selbst an.
declare
  l_clob clob := '';
  l_str  varchar2(32000);
begin
  dbms_lob.createtemporary(l_clob, true, dbms_lob.call);
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
    l_str := to_char(i.PROD_ID) || ';' || 
             to_char(i.CUST_ID) || ';' ||  
             to_char(i.TIME_ID) || ';' ||  
             to_char(i.CHANNEL_ID) || ';' || 
             to_char(i.PROMO_ID) || ';' || 
             to_char(i.QUANTITY_SOLD) || ';' || 
             to_char(i.AMOUNT_SOLD)|| chr(10);
    dbms_lob.writeappend(l_clob, length(l_str), l_str);
  end loop;
  dbms_output.put_line(dbms_lob.getlength(l_clob));
  dbms_output.put_line(dbms_lob.substr(l_clob, 400, 1));
end;
/
sho err
Wieder gibt es eine Verbesserung. Der Mehraufwand für die "Übersetzung" der Stringoperationen auf das manchmal umständliche DBMS_LOB hat sich gelohnt: Nochmals Faktor 4.
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.35
Kann man noch mehr rausholen? Ja, es geht noch was: Wir haben festgestellt, dass eine Zeichenverkettung für einen LOB wesentlich teurer ist als für ein VARCHAR2. Derzeit bauen wir eine Tabellenzeile als VARCHAR2 zusammen und hängen sie an den CLOB an. Wir haben also soviele LOB-Operationen, wie es Tabellenzeilen gibt. Nun könnte man aber doch mehrere Tabellenzeilen zu einem VARCHAR2 zusammenfassen und die LOB-Operation erst dann machen, wenn der VARCHAR2 "Puffer" mit 32.000 Bytes "voll" ist. In Code sieht das so aus:
declare
  l_clob  clob := '';
  l_str   varchar2(32000) := '';
  l_block varchar2(32000) := '';
begin
  dbms_lob.createtemporary(l_clob, true, dbms_lob.call);
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
    l_str := to_char(i.PROD_ID) || ';' || 
             to_char(i.CUST_ID) || ';' ||  
             to_char(i.TIME_ID) || ';' ||  
             to_char(i.CHANNEL_ID) || ';' || 
             to_char(i.PROMO_ID) || ';' || 
             to_char(i.QUANTITY_SOLD) || ';' || 
             to_char(i.AMOUNT_SOLD)|| chr(10);
    if length(l_block) + length(l_str) > 32000 then
      dbms_lob.writeappend(l_clob, length(l_block), l_block);
      l_block := '';
    end if;
    l_block := l_block || l_str;
  end loop;
  dbms_lob.writeappend(l_clob, length(l_block), l_block);
  dbms_output.put_line(dbms_lob.getlength(l_clob));
  dbms_output.put_line(dbms_lob.substr(l_clob, 400, 1));
end;
/
sho err
Das war Mehraufwand ... aber es hat sich tatsächlich nochmal gelohnt.
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.13
Und damit wäre ich am Optimierungsende angekommen. Es ist schon erstaunlich, was man mit geschicktem Coding herausholen kann. Bei dieser Prozedur traue ich mich nun, die ROWNUM-Beschränkung herauszuholen und den CLOB für die ganze Tabelle generieren zu lassen - der 39MB große CLOB ist in 6 Sekunden erzeugt. Es zeigt sich deutlich, dass, beim Arbeiten mit LOBs, am PL/SQL Paket DBMS_LOB kein Weg vorbeiführt.
Viel Spaß beim Ausprobieren ...
This blog posting is about the CLOB datatype, string operations and PL/SQL. I will elaborate about four different methods to "build" a CLOB from table data with PL/SQL. For 10000 rows, the execution time will vary from 4 Minutes to 0.2 seconds. If you want to know, why the DBMS_LOB package is so important and how to make string operations on CLOB variables really fast, read on.
The exercise: We want to build a CLOB, containing semicolon-separated data, from the well-known demo table SALES within the schema SH. This table contains about 920000 rows.
SQL> select * from sh.sales 

PROD_ID CUST_ID TIME_ID             CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
------- ------- ------------------- ---------- -------- ------------- -----------
     13     987 10.01.1998 00:00:00          3      999             1        1232
     13    1660 10.01.1998 00:00:00          3      999             1        1232
     13    1762 10.01.1998 00:00:00          3      999             1        1232
     13    1843 10.01.1998 00:00:00          3      999             1        1232
     13    1948 10.01.1998 00:00:00          3      999             1        1232
     13    2273 10.01.1998 00:00:00          3      999             1        1232
      :       :                   :          :        :             :           : 
Our result will look like this:
13;987;10.01.1998 00:00:00;3;999;1;1232,16
13;1660;10.01.1998 00:00:00;3;999;1;1232,16
13;1762;10.01.1998 00:00:00;3;999;1;1232,16
13;1843;10.01.1998 00:00:00;3;999;1;1232,16
13;1948;10.01.1998 00:00:00;3;999;1;1232,16
:
To save some time, I will start with focusing on the first 10000 rows. We'll run our procedure for the full dataset, when we have found the most efficient approach. Our first approach: We'll loop using an implicit cursor and use PL/SQL string concatenation operators in order to build the CLOB (as we would do for VARCHAR2). Pretty simple.
declare
  l_clob clob := '';
begin
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
   l_clob := l_clob || to_char(i.PROD_ID) || ';' || 
                       to_char(i.CUST_ID) || ';' ||  
                       to_char(i.TIME_ID) || ';' ||  
                       to_char(i.CHANNEL_ID) || ';' || 
                       to_char(i.PROMO_ID) || ';' || 
                       to_char(i.QUANTITY_SOLD) || ';' || 
                       to_char(i.AMOUNT_SOLD)|| chr(10);
  end loop;
  dbms_output.put_line(length(l_clob));
  dbms_output.put_line(substr(l_clob, 1, 400));
end;
/
sho err
We already know that our result will be larger than 32767 bytes. So we cannot use a VARCHAR2, we must use CLOB as datatype for the variable l_clob. But we can use "normal" PL/SQL string concatenation syntax, though. For 10000 rows, this procedure takes about 4 Minutes - which is long. I don't even want to think about the time needed for the whole table ...
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL procedure successfully completed.

Elapsed: 00:04:23.60
While the procedure runs, it's worthful to have a look into the V$TEMPORARY_LOBS data dictionary view. You'll see that your database session creates some temporary lobs.
SQL> select * from v$temporary_lobs

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ------------ -------------
        20          0            0             0
        38          0            0             0
       272          6            0             0
Let's get this straight: This code is far away from being optimal. All these string concatenation operations on the CLOB variable lead to (too) many temporary lobs being created. And string concatenations on CLOBs are, in general, far more expensive then on VARCHAR2s. Try it out: Here are 30000 string concatenations on a CLOB ...
declare
  v_result clob;
begin
  for i in 1..30000 loop
    v_result := v_result || '*';
  end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.56
... and here is the same on a VARCHAR2:
declare
  v_result varchar2(32000);
begin
  for i in 1..30000 loop
    v_result := v_result || '*';
  end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
That is at least factor 50 to 60. So, our next step must be to eliminate string concatenations on the CLOB. This is more easy than you might think: We can build a VARCHAR2 for a complete table row and finally append this (full row) to the CLOB.
declare
  l_clob clob := '';
  l_str  varchar2(32000);
begin
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
    l_str := to_char(i.PROD_ID) || ';' || 
             to_char(i.CUST_ID) || ';' ||  
             to_char(i.TIME_ID) || ';' ||  
             to_char(i.CHANNEL_ID) || ';' || 
             to_char(i.PROMO_ID) || ';' || 
             to_char(i.QUANTITY_SOLD) || ';' || 
             to_char(i.AMOUNT_SOLD)|| chr(10);
    l_clob := l_clob || l_str;
  end loop;
  dbms_output.put_line(length(l_clob));
  dbms_output.put_line(substr(l_clob, 1, 400));
end;
/
sho err
The result is nothing less than impressive ...
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.61
We are down from 4 Minutes to 2 seconds; just with a very tiny code adjustment. For the complete dataset of 920000 rows, we now have to expect an execution time of about 3 minutes. But we are not finished here - the story continues ...
Again, we have a look into V$TEMPORARY_LOBS, while the procedure is running. It shows us that two temporary LOBs are in use. But we really need only one temporary LOB and nothing more. Why? Because our result (which is a CLOB) must be hold as temporary LOB; all other temporary content can be handled as VARCHAR2. Since we used plain PL/SQL string operations, we left it up to the engine, how these operations are to be exeucuted on the CLOB. Thus, in our third approach, we'll do the job ourselves: All string operations are now done with the procedures and functions of the DBMS_LOB package. And ... we'll also create the temporary LOB explicitly and ourselves.
declare
  l_clob clob := '';
  l_str  varchar2(32000);
begin
  dbms_lob.createtemporary(l_clob, true, dbms_lob.call);
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
    l_str := to_char(i.PROD_ID) || ';' || 
             to_char(i.CUST_ID) || ';' ||  
             to_char(i.TIME_ID) || ';' ||  
             to_char(i.CHANNEL_ID) || ';' || 
             to_char(i.PROMO_ID) || ';' || 
             to_char(i.QUANTITY_SOLD) || ';' || 
             to_char(i.AMOUNT_SOLD)|| chr(10);
    dbms_lob.writeappend(l_clob, length(l_str), l_str);
  end loop;
  dbms_output.put_line(dbms_lob.getlength(l_clob));
  dbms_output.put_line(dbms_lob.substr(l_clob, 400, 1));
end;
/
sho err
Using this obscure DBMS_LOB package seems to pay off: Performance improvement by factor 4.
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.35
That's all ...? No, we can get even better: We have learned, that a string operation on a CLOB is far more expensive than on a VARCHAR2. Now, we build each table row as VARCHAR2 and append it to the CLOB. So we have as many CLOB concatenations as there are table rows. But we could also aggregate multiple table rows into the VARCHAR2, up to its limit of 32000 bytes. When the VARCHAR buffer is full, we append it to the CLOB and start again. Translated to code, this looks as follows ...
declare
  l_clob  clob := '';
  l_str   varchar2(32000) := '';
  l_block varchar2(32000) := '';
begin
  dbms_lob.createtemporary(l_clob, true, dbms_lob.call);
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
    l_str := to_char(i.PROD_ID) || ';' || 
             to_char(i.CUST_ID) || ';' ||  
             to_char(i.TIME_ID) || ';' ||  
             to_char(i.CHANNEL_ID) || ';' || 
             to_char(i.PROMO_ID) || ';' || 
             to_char(i.QUANTITY_SOLD) || ';' || 
             to_char(i.AMOUNT_SOLD)|| chr(10);
    if length(l_block) + length(l_str) > 32000 then
      dbms_lob.writeappend(l_clob, length(l_block), l_block);
      l_block := '';
    end if;
    l_block := l_block || l_str;
  end loop;
  dbms_lob.writeappend(l_clob, length(l_block), l_block);
  dbms_output.put_line(dbms_lob.getlength(l_clob));
  dbms_output.put_line(dbms_lob.substr(l_clob, 400, 1));
end;
/
sho err
It's faster - again.
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.13
And this is the end of the optimization. These differences are stunning: We started with 4 minutes and came down to 0.2 seconds; which is a factor of 1200. Now, I'm totally relaxed when running the code for whole table of 920000 rows - the CLOB of 39MB is being build in just 6 seconds. As so often, the first and "most easy" approach isn't the best: When it's about working with CLOBs, there is now way around the (kind of obscure) DBMS_LOB package. Getting familiar with it, is (as we see) absolutely worth the effort ...
As always - habe a lot of fun while playing with this ...

Kommentare:

Dejan Topalovic hat gesagt…

Das ist ein Wahnsinn!!! Habe früher viel programmiert, aber auf solche "Kleinigkeiten" habe ich nicht gedacht! :(
Herzlichsten Dank für diese hervoragende Analyse und perfekte Beschreibung aller Beispiele!

LG
Dejan

Pascal Glauser hat gesagt…

Schönes Beispiel, Danke.
Ich frage mich beim Erzeugen temporärer LOBs jeweils, was die tiefere Bedeutung der beiden Parameter cache und duration ist. Bislang habe ich keinen Unterschied festgestellt, ausser dass duration_call manchmal nicht geht, z.B. aus JDBC heraus.
Was heisst es, wenn mit cache-> false das LOB nicht im Buffer Cache landet ? Dass jede Operation es wieder vom Temptablespace liest ?

Carsten Czarski hat gesagt…

Hallo Pascal,

DURATION_CALL bedeutet, dass der temporäre LOB nach Abschluß des Database Calls (also der SQL Query oder dem PL/SQL Block) zerstört werden soll. Das geht in der Tat in einigen Umgebungen nicht; bspw. bei JDBC. Denn wenn man hier mit dem Java-Aufruf "createTemporary" einen temp. LOB erzeugt, wäre der "Call" ja unmittelbar danach beendet und der LOB müsste zerstört werden. Daher hier nur Session. Ohnehin würde ich mich auf das automatische Aufräumen (auch wenn es meist gut funktioniert) niemals verlassen und (wenn möglich) temporäre LOBs immer selbst freigeben.

Naja - und mit dem Buffer Cache ..? Richtig: wenn man CACHE auf false setzt, wird das temporäre LOB nicht in den Buffer Cache geladen; das bedeutet tendenziell mehr I/O ... das merkt man schon, wenn mal etwa 1000 Mal DBMS_LOB.WRITEAPPEND auf einem tempLOB mit cache = TRUE und mit CACHE = false ausführt. CACHE = false führt zu massiv längeren Laufzeiten, eben weil kein Cache. Für Beispiele wie im Blog-Posting, wo ein CLOB "zusammengebaut" wird, geht an cache = true kein Weg vorbei ...

Beste Grüße

Carsten


Beliebte Postings