11. Juni 2016

Warum have ich soviele "temporary LOBs"? Wissenswertes.

Ja, auf diesem Blog gibt es neue Postings. Nachdem es hier in den letzten drei Monaten etwas ruhiger war, kommt heute wieder ein neues Posting - auch künftig geht es hier regelmäßig mit Themen rund um SQL, PL/SQL und die Oracle-Datenbank weiter.

Heute geht es um temporäre LOBs (Large Objects). Größere Texte werden bekanntlich als CLOB, größere Binärobjekte als BLOB abgespeichert. Da ein solches Objekt sehr groß werden kann, repräsentiert ein CLOB oder BLOB die Daten nicht direkt, es ist vielmehr ein Pointer auf dieselben. Wenn man einen LOB in eine Tabelle speichert, so hängt die tatsächliche Speicherung von der Größe und der STORAGE IN ROW Eigenschaft der Tabelle ab.

  • Ist der LOB größer als 4000 Byte, so wird er immer separat im LOB-Segment gespeichert, die Tabellenspalte selbst erhält nur den Pointer
  • Unter 4000 Byte hängt es von der Eigenschaft STORAGE IN ROW ab: Wurde ENABLE STORAGE IN ROW angegeben (was der Default ist), so wird der LOB (wie ein VARCHAR2 oder RAW) in der Tabelle selbst abgelegt. Wird dagegen DISABLE STORAGE IN ROW angegeben, so werden auch kleine LOBs in das LOB-Segment abgelegt.

So weit - so gut. Das Thema heute lautet aber temporäre LOBs. Das sind solche, die gerade nicht in einer Tabelle abgespeichert werden, sondern nur transient in PL/SQL Programmen verwendet werden. Tatsächlich gespeichert werden diese temporären LOBs (da sie ja größer werden können) im temporary tablespace. Temporäre LOBs können explizit mit der PL/SQL-Prozedur DBMS_LOB.CREATETEMPORARY erzeugt werden - das geht wie folgt:

declare
  l_tclob clob;
begin
  dbms_lob.createtemporary(
    lob_loc   => l_tclob,
    cache     => true,
    dur       => dbms_lob.session
  );

  -- append data to this temp CLOB up to 4 GB
  dbms_lob.writeappend( l_tclob, ... );

  dbms_lob.freetemporary(
    lob_loc   => l_tclob
  );
end;

Der in diesem Beispiel erzeugte temporäre LOB wird in der SGA gecacht (cache => true). Würde man cache auf false setzen, so erfolgt jeder Schreib- und Lesezugriff auf den LOB direkt (physikalisch) im Datafile des temporary tablespace - dazu hatte ich letztes Jahr bereits ein Blog-Posting (String-Operationen auf CLOBs - richtig schnell machen). Ein temporärer LOB wird in PL/SQL gemeinsam mit seiner Variablen zerstört: So existiert ein temporärer LOB in einer Package-Variablen während der ganzen Session, in einer lokalen Variable wird er nach Ablauf des Blocks, der Funktion oder Procedure zerstört. Der Parameter dur von createtemporary ist lediglich ein Hint, wann der temporäre LOB zerstört und der Platz freigegeben werden soll - das ist besonders interessant, wenn temporäre LOBs nicht aus PL/SQL heraus (bspw. aus Java innerhalb oder außerhalb der Datenbank) erzeugt werden. Mit DBMS_LOB.FREETEMPORARY kann man einen temporären LOB auch manuell zerstören.

Mit der Dictionary View V$TEMPORARY_LOBS kann man prüfen, ob in einer Datenbanksession gerade temporäre LOBs existieren. Die folgende Query zeigt: Nix da.

SQL> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS     CON_ID
---------- ---------- ------------ ------------- ----------
       264          0            0             0          3

Temporäre LOBs werden aber nicht nur explizit generiert, sie entstehen auch oft implizit - das folgende Beispiel ist hier ganz interessant: In PL/SQL Prozeduren kann ein CLOB genauso wie ein VARCHAR2 verwendet werden - und de-jure funktioniert es bis 32 Kilobyte auch genauso. Prüfen wir das mal nach: Ein PL/SQL Block fügt 32.767 mal ein Leerzeichen an einen CLOB an - eigentlich dürfte kein temporärer LOB entstehen ...

declare
    l_clob       clob;
    l_ctemplobs  number;
    l_nctemplobs number;
begin
    for i in 1..32767 loop
        l_clob := l_clob || ' ';
    end loop;

    select cache_lobs, nocache_lobs 
      into l_ctemplobs, l_nctemplobs
      from v$temporary_lobs;
 
    dbms_output.put_line('Cached temp lobs:     '|| l_ctemplobs);
    dbms_output.put_line('Non-Cached temp lobs: '|| l_nctemplobs);
end;

Als Ergebnis kommt heraus:

Cached temp lobs:     0
Non-Cached temp lobs: 0

Wie erwartet. Gehen wir auf 40.000 hoch ...

Cached temp lobs:     0
Non-Cached temp lobs: 0

Interessant - immer noch kein temporärer LOB. Man kann das jetzt noch weiter treiben; bis 131.072 ( 4 mal 32.768 ) Zeichen entsteht kein temporärer LOB und PL/SQL arbeitet allein im Hauptspeicher. Sobald der CLOB aber größer als 131.072 Zeichen wird, wird sich die Ausgabe ändern - und nicht nur das: ab hier wird die Performance auch spürbar schlechter - ein temporärer LOB ist eben doch etwas anderes als ein VARCHAR2. Das zeigt der folgende Code ...

declare
    l_clob       clob;
    l_ctemplobs  number;
    l_nctemplobs number;

    l_time       pls_integer;
begin
    for i in 1..10 loop
        l_time := dbms_utility.get_time;
        for j in 1..32767 loop
            l_clob := l_clob || ' ';
        end loop;

        dbms_output.put_line( i || '. 32k chunk: ' || ( dbms_utility.get_time - l_time ) || ' hs');
      
        select cache_lobs, nocache_lobs into l_ctemplobs, l_nctemplobs
        from v$temporary_lobs;
       
        dbms_output.put_line('  .. Cached temp lobs:     '|| l_ctemplobs);
    end loop;   
end;

1. 32k chunk:  10 hs
.. Cached temp lobs:     0
2. 32k chunk:  11 hs
.. Cached temp lobs:     0
3. 32k chunk:  10 hs
.. Cached temp lobs:     0
4. 32k chunk:  10 hs
.. Cached temp lobs:     0
5. 32k chunk:  83 hs
.. Cached temp lobs:     1
6. 32k chunk: 133 hs
.. Cached temp lobs:     1
7. 32k chunk: 176 hs
.. Cached temp lobs:     1
8. 32k chunk: 187 hs
.. Cached temp lobs:     1
9. 32k chunk: 191 hs

Wenn der PL/SQL-Block abgelaufen ist, werden die temporären LOBs zerstört; das zeigt die Kontrolle in V$TEMPORARY_LOBS. Da die PL/SQL-Variablen nach Ablauf des PL/SQL Blocks zerstört werden, werden auch die temporären LOBs zerstört. Gerade das - und die duration verdienen nun eine eigene Betrachtung: Bauen wir eine PL/SQL-Funktion, die einen temporären LOB zurückgibt - diese rufen wir dann auf und prüfen dann V$TEMPORARY_LOBS.

create or replace function get_templob return clob
is
    l_clob clob;
begin
    dbms_lob.createtemporary( l_clob, true, dbms_lob.call );
    return l_clob;
end get_templob;
/

create or replace procedure print_templobs is
    l_templobs   number;
begin
    select cache_lobs
      into l_templobs
      from v$temporary_lobs;
   
    dbms_output.put_line( 'Cached temp lobs: ' || l_templobs );
end;
/

declare
    l_cloblength number := 0;
begin
         
    l_cloblength := dbms_lob.getlength( get_templob );
    print_templobs;
    l_cloblength := dbms_lob.getlength( get_templob );
    print_templobs;
    l_cloblength := dbms_lob.getlength( get_templob );
    print_templobs;
    l_cloblength := dbms_lob.getlength( get_templob );
    print_templobs;
end;
/

Das Ergebnis ...

Cached temp lobs: 1
Cached temp lobs: 2
Cached temp lobs: 3
Cached temp lobs: 4

Interessant, nicht wahr? Obwohl der temporäre LOB in einer lokalen Variable der Funktion GET_TEMPLOB gehalten wird, hat sich die Anzahl der temporären LOBs mit jedem Aufruf erhöht. Nach Abschluß des anonymen Blocks werden jedoch alle zerstört.

SQL> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS     CON_ID
---------- ---------- ------------ ------------- ----------
       264          0            0             0          3

Der Grund für das Ansteigen während der Laufzeit des PL/SQL Blocks ist, dass der temporäre CLOB im anonymen Block nicht einer Variable zugewiesen, sondern unmittelbar mit DBMS_LOB.GETLENGTH weiterverarbeitet wird. Dazu muss der PL/SQL-Compiler intern eine Art "anonyme Variable" erzeugen, den temporären LOB dorthin übernehmen und an DBMS_LOB.GETLENGTH weitergeben. Das Verhalten ist ein anderes, wenn wir den temporären LOB explizit in eine eigene Variable vom Typ CLOB übernehmen.

declare
    l_cloblength number := 0;
    l_clob       clob;
begin
         
    l_clob := get_templob; l_cloblength := dbms_lob.getlength( l_clob );
    print_templobs;
    l_clob := get_templob; l_cloblength := dbms_lob.getlength( l_clob );
    print_templobs;
    l_clob := get_templob; l_cloblength := dbms_lob.getlength( l_clob );
    print_templobs;
    l_clob := get_templob; l_cloblength := dbms_lob.getlength( l_clob );
    print_templobs;

end;
/

Nun kommt ein anderes Ergebnis - jeder Aufruf überschreibt die Variable l_clob, so dass der damit verbundene temporäre LOB ebenfalls überschrieben (und der "alte" damit zerstört) wird.

Cached temp lobs: 1
Cached temp lobs: 1
Cached temp lobs: 1
Cached temp lobs: 1

Packt man den Aufruf in eine SELECT-Abfrage, so ändert sich das Bild nochmals - denn in diesem Fall findet jedes mal ein neuer Top-Level-Call statt.

declare
    l_cloblength number := 0;
begin
         
    select dbms_lob.getlength( get_templob ) into l_cloblength from dual;
    print_templobs;
    select dbms_lob.getlength( get_templob ) into l_cloblength from dual;
    print_templobs;
    select dbms_lob.getlength( get_templob ) into l_cloblength from dual;
    print_templobs;
    select dbms_lob.getlength( get_templob ) into l_cloblength from dual;
    print_templobs;

end;
/

Nach Abschluß werden alle lokalen PL/SQL Variablen zerstört, so dass kein temporärer LOB mehr übrig bleibt.

Cached temp lobs: 0
Cached temp lobs: 0
Cached temp lobs: 0
Cached temp lobs: 0

Um einen temporären LOB für die Dauer der Session "überleben" zu lassen, braucht es zunächst eine Variable, die während der ganzen Session gültig ist - wie eine Package Variable. Aber eine Warnung bereits vorab: Das ist typischerweise der Weg zu Memory oder "temporary lob" leaks.

create or replace package clobholder is
  g_clob clob;
end clobholder;
/

create or replace function get_templob return clob
is
begin
    dbms_lob.createtemporary( clobholder.g_clob, true, dbms_lob.session );
    return clobholder.g_clob;
end get_templob;
/

create or replace procedure print_templobs is
    l_templobs   number;
begin
    select cache_lobs
      into l_templobs
      from v$temporary_lobs;
   
    dbms_output.put_line( 'Cached temp lobs: ' || l_templobs );
end;
/

declare
    l_cloblength number := 0;
    l_clob       clob;
    
begin

    select get_templob  into l_clob from dual;
    print_templobs;
    select get_templob  into l_clob from dual;
    print_templobs;
    select get_templob  into l_clob from dual;
    print_templobs;
    select get_templob  into l_clob from dual;
    print_templobs;

end;
/

Nun arbeitet der Code mit einer CLOB-Variablen in einem Package - diese ist während der ganzen Session gültig. Probiert es aus!

Cached temp lobs: 1
Cached temp lobs: 2
Cached temp lobs: 3
Cached temp lobs: 4

Obgleich mit einer SQL-Query ein neuer Top-Level Call getätigt wurde, bleiben die temporären LOBs erhalten. Im Gegensatz zu vorhin sind sie nun auch nach Abschluß des PL/SQL-Blocks noch da.

SQL> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS     CON_ID
---------- ---------- ------------ ------------- ----------
       264          4            0             0          3

Und wenn man den anonymen Block nun immer wieder ausführt, erhöht sich die Anzahl der temporären LOBs stetig - wir haben ein temporary LOB leak programmiert.

Cached temp lobs: 5
Cached temp lobs: 6
Cached temp lobs: 7
Cached temp lobs: 8

Doch warum ...? Schließlich arbeiten wir doch immer auf der gleichen globalen Package-Variable; und dennoch erhöhen sich die temporären LOBs ständig. Der Grund ist, dass wir auf der gleichen Variablen (clobholder.g_clob) immer wieder dbms_lob.createtemporary aufrufen. Doch auch temporäre LOBs auf Package-Variablen können automatisch zerstört werden, dazu braucht es aber eine Zuweisung zu dieser Variablen mit :=. Wir ändern den Code der Funktion GET_TEMPLOB ganz leicht um ...

create or replace function get_templob return clob
is
begin
    clobholder.g_clob := null;
    dbms_lob.createtemporary( clobholder.g_clob, true, dbms_lob.session );
    return clobholder.g_clob;
end get_templob;
/

Die explizite Zuweisung vor dem erneuten createtemporary sorgt dafür, dass ein etwaiger bereits existierender temporärer LOB zerstört und der Speicherplatz freigegeben wird. Das Problem ist behoben.

Cached temp lobs: 1
Cached temp lobs: 1
Cached temp lobs: 1
Cached temp lobs: 1

Nach Abschluß des PL/SQL-Blocks bleibt genau der eine temporäre LOB übrig.

SQL> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS     CON_ID
---------- ---------- ------------ ------------- ----------
       264          1            0             0          3

Allgemein kann man sagen, dass temporäre LOBs, besonders wenn sie in globalen Package Variablen gehalten werden, immer mit DBMS_LOB.FREETEMPORARY explizit freigegeben werden sollten. Wer eine Package-Funktion schreibt, die einen solchen temporären LOB erzeugt und zurückgibt, sollte auch eine Prozedur schreiben, die das entsprechende FREETEMPORARY ausführt. Der folgende Code geht auf Nummer Sicher.

declare
    l_cloblength number := 0;
    l_clob clob;
begin

    select get_templob  into l_clob from dual;
    dbms_lob.freetemporary( clobholder.g_clob );
    print_templobs;
    select get_templob  into l_clob from dual;
    dbms_lob.freetemporary( clobholder.g_clob );
    print_templobs;
    select get_templob  into l_clob from dual;
    dbms_lob.freetemporary( clobholder.g_clob );
    print_templobs;
    select get_templob  into l_clob from dual;
    dbms_lob.freetemporary( clobholder.g_clob );
    print_templobs;

end;
/

Man sieht, temporäre LOBs können sich ganz interesant verhalten. PL/SQL nimmt uns einige Arbeit ab und zerstört die temporären LOBs, sobald die LOB-Variablen zerstört werden oder explizit ein anderer Wert zugewiesen wird. Gerade mit temporären LOBs, die in Package Variablen gehalten werden, muss man aber aufpassen - hier können immer noch Leaks entstehen, so dass eine Datenbanksession ständig neue temporary LOBs erzeugt. Solche LOBs sollten immer nach Gebrauch mit DBMS_LOB.FREETEMPORARY explizit freigegeben werden.

YES, I'll continue to publish postings on this blog. Since my last posting in March it got a bit quiet here, but here I am again: a new posting about SQL, PL/SQL and the Oracle database.

Today it's about temporary LOBs (LOB = Large Objects). Larger texts or binary values are best stored as CLOB or BLOB. Since such an object can grow pretty large, it does not contain the data directly - instead it's a pointer. When such a LOB is stored in a table, actual data storage depends on LOB size and the table columns storage attributes.

  • LOBs larger than 4.000 byte are always stored in the separate LOB segment; the table column itself only contains the pointer.
  • For LOBs smaller than 4.000 bytes, it depends on the STORAGE IN ROW clause of the table column. ENABLE STORAGE IN ROW stores these LOBs directly in the table row - like a VARCHAR2 column. DISABLE STORAGE IN ROW lets the database store these LOBs in the LOB segment like their larger pendants.

So far - so good. But the topic today is temporary LOBs. These are LOBs which are used in variables within PL/SQL or Java programs and not being stored in a table (the variables and their scope will become important). Temporary LOB data is being stored in the Temporary Tablespace. A temporary LOB is created using DBMS_LOB.CREATETEMPORARY and destroyed with DBMS_LOB.FREETEMPORARY:

declare
  l_tclob clob;
begin
  dbms_lob.createtemporary(
    lob_loc   => l_tclob,
    cache     => true,
    dur       => dbms_lob.session
  );

  -- append data to this temp CLOB up to 4 GB
  dbms_lob.writeappend( l_tclob, ... );

  dbms_lob.freetemporary(
    lob_loc   => l_tclob
  );
end;

The temporary LOB in the above example will be cached in the buffer cache (cache => true). Setting this cache to false would lead to physical I/O access for every read or write operation on that LOB (last year, I published a posting about that: String-Operationen auf CLOBs - richtig schnell machen).

In PL/SQL, a temporary LOB is bound to its variable. When the variable is being destroyed or a new value is being assigned to it, the temporary LOB is being destroyed. So a temporary LOB being hold by a local variable in a function will die after the function as ended. It its being hold by a global package variable, it will be alive until the session ends. The dur parameter in createtemporary is only a hint and of particular interest when the temporary LOB is being created outside of PL/SQL (e.g. Java in the Database).

The dictionary view V$TEMPORARY_LOBS shows how many temporary LOBs have been allocated in that session.

SQL> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS     CON_ID
---------- ---------- ------------ ------------- ----------
       264          0            0             0          3

Not all temporary LOBs are being created explicitly. Sometimes you'll get implicit temporary LOBs; the following example shows this. Within PL/SQL, you cam work with a CLOB just like with VARCHAR2; and de-jure it works the same up to 32.767 bytes. So let's check this: A PL/SQL block adds 32.767 space characters to a CLOB - so we should see no temporary LOB here.

declare
    l_clob       clob;
    l_ctemplobs  number;
    l_nctemplobs number;
begin
    for i in 1..32767 loop
        l_clob := l_clob || ' ';
    end loop;

    select cache_lobs, nocache_lobs 
      into l_ctemplobs, l_nctemplobs
      from v$temporary_lobs;
 
    dbms_output.put_line('Cached temp lobs:     '|| l_ctemplobs);
    dbms_output.put_line('Non-Cached temp lobs: '|| l_nctemplobs);
end;

The result ...

Cached temp lobs:     0
Non-Cached temp lobs: 0

... as expected. Let's move up to 40.000 ...

Cached temp lobs:     0
Non-Cached temp lobs: 0

... still no temporary LOB. Further tests show that you'll get a temporary LOB when going beyond 131.072 space characters. Smaller CLOBs are completely handled in memory - just like VARCHAR2 variables. From 131.072 and higher, PL/SQL will work with a temporary LOB segment. You'll also notice that when looking the the time consumed ... to see that effect, we need to change the code a bit.

declare
    l_clob       clob;
    l_ctemplobs  number;
    l_nctemplobs number;

    l_time       pls_integer;
begin
    for i in 1..10 loop
        l_time := dbms_utility.get_time;
        for j in 1..32767 loop
            l_clob := l_clob || ' ';
        end loop;

        dbms_output.put_line( i || '. 32k chunk: ' || ( dbms_utility.get_time - l_time ) || ' hs');
      
        select cache_lobs, nocache_lobs into l_ctemplobs, l_nctemplobs
        from v$temporary_lobs;
       
        dbms_output.put_line('  .. Cached temp lobs:     '|| l_ctemplobs);
    end loop;   
end;

1. 32k chunk:  10 hs
.. Cached temp lobs:     0
2. 32k chunk:  11 hs
.. Cached temp lobs:     0
3. 32k chunk:  10 hs
.. Cached temp lobs:     0
4. 32k chunk:  10 hs
.. Cached temp lobs:     0
5. 32k chunk:  83 hs
.. Cached temp lobs:     1
6. 32k chunk: 133 hs
.. Cached temp lobs:     1
7. 32k chunk: 176 hs
.. Cached temp lobs:     1
8. 32k chunk: 187 hs
.. Cached temp lobs:     1
9. 32k chunk: 191 hs

After the PL/SQL block has finished, the temporary LOB is being destroyed; that can be checked in the V$TEMPORARY_LOBS dictionary view. So let's have a deeper look at the lifetime of a temporary LOB. For that we build a PL/SQL function returning a temporary LOB - then we'll call that function. We'll monitor the amount of existing temporary LOBs in V$TEMPORARY_LOBS.

create or replace function get_templob return clob
is
    l_clob clob;
begin
    dbms_lob.createtemporary( l_clob, true, dbms_lob.call );
    return l_clob;
end get_templob;
/

create or replace procedure print_templobs is
    l_templobs   number;
begin
    select cache_lobs
      into l_templobs
      from v$temporary_lobs;
   
    dbms_output.put_line( 'Cached temp lobs: ' || l_templobs );
end;
/

declare
    l_cloblength number := 0;
begin
         
    l_cloblength := dbms_lob.getlength( get_templob );
    print_templobs;
    l_cloblength := dbms_lob.getlength( get_templob );
    print_templobs;
    l_cloblength := dbms_lob.getlength( get_templob );
    print_templobs;
    l_cloblength := dbms_lob.getlength( get_templob );
    print_templobs;
end;
/

Results ...

Cached temp lobs: 1
Cached temp lobs: 2
Cached temp lobs: 3
Cached temp lobs: 4

Interesting, isn't it ...? Although the temporary LOB is bound to a local variable within the GET_TEMPLOB function, we'll get a new one with each call. After the block has finished, however, all temporary LOBs are being destroyed.

SQL> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS     CON_ID
---------- ---------- ------------ ------------- ----------
       264          0            0             0          3

The temporary LOB returned by GET_TENPLOB is not being assigned to a variable, instead the code directly passes it to DBNS_LOB.GETLENGTH. The PL/SQL compiler builds - kind of - an "anonymous variable" for it; assigns the returned temporary LOB to it and passes it then to DBMS_LOB.GETLENGTH. So we have four "anonymous variables" containing four temporary LOBs which are alive until the block has finished. Behaviour changes when we explicitly assign the result of GET_TEMPLOB to a CLOB variable - as follows:

declare
    l_cloblength number := 0;
    l_clob       clob;
begin
         
    l_clob := get_templob; l_cloblength := dbms_lob.getlength( l_clob );
    print_templobs;
    l_clob := get_templob; l_cloblength := dbms_lob.getlength( l_clob );
    print_templobs;
    l_clob := get_templob; l_cloblength := dbms_lob.getlength( l_clob );
    print_templobs;
    l_clob := get_templob; l_cloblength := dbms_lob.getlength( l_clob );
    print_templobs;

end;
/

Each assignment to the l_clob variable overwrites the existing value, which leads to the temporary LOB being destroyed.

Cached temp lobs: 1
Cached temp lobs: 1
Cached temp lobs: 1
Cached temp lobs: 1

We can also embed our call into a SQL query. Then we will have a new top level call.

declare
    l_cloblength number := 0;
begin
         
    select dbms_lob.getlength( get_templob ) into l_cloblength from dual;
    print_templobs;
    select dbms_lob.getlength( get_templob ) into l_cloblength from dual;
    print_templobs;
    select dbms_lob.getlength( get_templob ) into l_cloblength from dual;
    print_templobs;
    select dbms_lob.getlength( get_templob ) into l_cloblength from dual;
    print_templobs;

end;
/

After the Query has finished, all local variables are being freed and all temporary LOBs destroyed.

Cached temp lobs: 0
Cached temp lobs: 0
Cached temp lobs: 0
Cached temp lobs: 0

To have a temporary LOB survive until the end of the session, we need to have it bound to a variable which lives for the length of the session - like a package variable. But note the warning: Ba careful with this, otherwise you'll get "temporary LOB" leaks and massive temporary tablespace consumption.

create or replace package clobholder is
  g_clob clob;
end clobholder;
/

create or replace function get_templob return clob
is
begin
    dbms_lob.createtemporary( clobholder.g_clob, true, dbms_lob.session );
    return clobholder.g_clob;
end get_templob;
/

create or replace procedure print_templobs is
    l_templobs   number;
begin
    select cache_lobs
      into l_templobs
      from v$temporary_lobs;
   
    dbms_output.put_line( 'Cached temp lobs: ' || l_templobs );
end;
/

declare
    l_cloblength number := 0;
    l_clob       clob;
    
begin

    select get_templob  into l_clob from dual;
    print_templobs;
    select get_templob  into l_clob from dual;
    print_templobs;
    select get_templob  into l_clob from dual;
    print_templobs;
    select get_templob  into l_clob from dual;
    print_templobs;

end;
/

Now our code works with a global CLOB variable in a package. Try it out.

Cached temp lobs: 1
Cached temp lobs: 2
Cached temp lobs: 3
Cached temp lobs: 4

We still perform a SQL query (and therefore a top-level-call) in our anonymous block. But the temporary LOBs survive. And unlike in the previous example, they are still present after the block has finiahed.

SQL> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS     CON_ID
---------- ---------- ------------ ------------- ----------
       264          4            0             0          3

Running the block again leads to even more temporary LOBs in our session - we have created a temporary LOB leak.

Cached temp lobs: 5
Cached temp lobs: 6
Cached temp lobs: 7
Cached temp lobs: 8

But why - we only work with one global package variable. The reason is that, with each call to GET_TEMPLOB are are executing CREATETEMPORARY on that variable. But we don't do an explicit assignment, so the already existing temporary LOB is not being destroyed. The solution to that problem is pretty easy: to have the temporary LOB of a variable being freed, assign something to that variable with :=- as follows:

create or replace function get_templob return clob
is
begin
    clobholder.g_clob := null;
    dbms_lob.createtemporary( clobholder.g_clob, true, dbms_lob.session );
    return clobholder.g_clob;
end get_templob;
/

With the assignment of NULL to the clobholder.g_clob variable, an existing temporary LOB is being destroyed and memory is being freed.

Cached temp lobs: 1
Cached temp lobs: 1
Cached temp lobs: 1
Cached temp lobs: 1

After the block has finished, exactly one temporary LOB remains.

SQL> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS     CON_ID
---------- ---------- ------------ ------------- ----------
       264          1            0             0          3

Take special care for temporary LOBs in global package variables - it's generally best practice to always free them explicitly with DBMS_LOB.FREETEMPORARY. If you are authoring a function which creates and returns a temporary LOB based on a global package variable, make sure to create a to free it up as well. The following code plays it safe.

declare
    l_cloblength number := 0;
    l_clob clob;
begin

    select get_templob  into l_clob from dual;
    dbms_lob.freetemporary( clobholder.g_clob );
    print_templobs;
    select get_templob  into l_clob from dual;
    dbms_lob.freetemporary( clobholder.g_clob );
    print_templobs;
    select get_templob  into l_clob from dual;
    dbms_lob.freetemporary( clobholder.g_clob );
    print_templobs;
    select get_templob  into l_clob from dual;
    dbms_lob.freetemporary( clobholder.g_clob );
    print_templobs;

end;
/

You see, temporary LOBs can be very interesting. PL/SQL does a lot of work for us and frees up temporary LOBs automatically when their variable lifetime ends or new values are being assiged. Developers should take particular care for temporary LOBs when they are hold by global package variables. It's always best practice to destroy temporary LOBs explicitly using DBMS_LOB.FREETEMPORARY. werden.

1 Kommentar:

Dr. Shadoku hat gesagt…

Prima Artikel. Bitte mehr davon. Gerade Clobs und Blobs stecken voller Geheimnisse und Fallstricke.

Beliebte Postings