31. März 2008

Damit es keine Memory Leaks gibt: Datenbankressourcen immer schließen

English title: Always close your resources!
So, nun geht es wieder weiter mit dem Thema "Datenbank-Performance aus Sicht des Anwendungsentwicklers". Heute geht es um das wichtige Thema, nicht mehr benötigte Ressourcen in der Oracle-Datenbank freizugeben.
Here is the next post about database performance from the developers' point of view. Today's topic is database resources and the importance of proper free'ing them
Fangen wir mit einem einfachen Beispiel an - Insbesondere im Java-Umfeld (aber auch für .NET oder C/C++-Programmierer) wird immer wieder betont, wie wichtig das explizite Schließen der Datenbank-Cursors ist. Das folgende kleine Java-Programm illustriert das ...
Let's start with a simple example. Most java (but also .NET oder C/C++ programmers) have frequently read about the importance to close database cursors. The following simple Java program illustrates this ...
 
import java.sql.*;

public class CursorLeak {
  static Connection        con = null;
  static PreparedStatement pstmt = null;
  static ResultSet         rs = null;

  static int               iCounter = 0;

  public static void main(String args[]) throws Exception {
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.2.140:1521:orcl", "scott", "tiger");
    con.setAutoCommit(false);
    System.out.println("Mit der Datenbank verbunden.");
    while (iCounter < 1000000) {
      // Neues "PreparedStatement" erstellen - der Datenbank-Cursor "hängt"
      // am PreparedStatement-Objekt, *nicht* am ResultSet-Objekt.
      pstmt = con.prepareStatement("select sal from emp where rownum <= 1");
      rs = pstmt.executeQuery();
      // Anweisung an die Java-Runtime, "Garbage-Collection" durchzuführen (hint)
      // nutzt aber nix ...
      System.gc();
      iCounter++;
      if (iCounter % 10 == 0) {
        System.out.print(".");
      }
    }
    rs.close();
    pstmt.close();
    con.close();
  }
}
Startet man das Programm, so ergibt sich folgende Ausgabe ...
The program gives the following output ...
$ java CursorLeak
Mit der Datenbank verbunden.
.............................
Exception in thread "main" java.sql.SQLException: ORA-00604: Fehler auf rekursiver SQL-Ebene 1
ORA-01000: Maximale Anzahl offener Cursor überschritten
ORA-01000: Maximale Anzahl offener Cursor überschritten

       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)
       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282)
       at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639)
       at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:185)
       at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:503)
       at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:965)
       at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:535)
       at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1051)
       at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2984)
       at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3026)
       at CursorLeak.main(CursorLeak.java:19)
Obwohl das Programm eigentlich 1.000.000 mal das Gehalt selektieren soll, ist (in der Regel) nach 300 Durchläufen Schluß - dann ist das Limit für die offenen Cursor (normalerweise 300) erreicht. C/C++-Programmierer sind es ja gewohnt, alle allokierten Ressourcen explizit freizugeben; das gleiche gilt für PL/SQL Entwickler. Java Programmierer verlassen sich dagegen häufig auf den Garbage Collector, der sich um das "Aufräumen" nicht mehr benötigter Ressourcen kümmert. Allerdings lösen die sog. Finalizer der Oracle JDBC-Klassen NICHT das Freigeben der Datenbankressourcen aus: Also wird das Objekt in der Java-Umgebung zerstört - der Datenbankcursor bleibt offen. Und irgendwann läuft die Datenbank voll. Das Limit mit OPEN_CURSORS zu erhöhen verschiebt das Problem lediglich in die Zukunft.
The program look should select the first employees' salary one milltion times, but (usually) after 300 times it aborts with the ORA-1000 error. Well C/C++ developers usally know that they have to explicitly close each resource they allocated. The same applies to PL/SQL (particularly cursors). Java developers mostly rely on the JVM's garbage collector which is responsible for cleaning up objects which are no longer needed. The java garbage collector calls the finalizer method of each object it cleans up - but this finalizer method does not close the associated database resources (here: the cursor) - so the java object is destroyed - the database cursor still exists. And after a while the cursors run out of the database limit (normally 300). Increasing the limit only postpones to problem to the future.
Insofern ist es extrem wichtig, dass der Java-Entwickler explizit die entsprechenden close()-Methoden der ResultSet- und PreparedStatement-Klassen aufruft. In einem üblichen Java try-catch-finally-Block geschieht das am besten im finally-Bereich.
It is therefore essential to explicitly close all database resources by calling the close() methods of the ResultSet- or PreparedStatement classes. If the java programmer uses the usual try-catch-finally blocks this is best done in the finally clause. BTW: The cursor sticks with the PreparedStatement, not with the ResultSet object.
Gut - das Cursor-Beispiel ist weithin bekannt ... es gibt aber noch andere Datenbankressourcen, auf die man ein wenig achtgeben muss ... Zunächst wären da temporäre LOBs zu nennen:
OK - the cursor example is kind of well-known. But there are other kinds of database resources which deserve some developers' attention. Temporary lobs are a good example:
Temporäre Lobs sind wie normale LOBs, sie liegen jedoch nicht in einer Tabelle. Wenn man in einem PL/SQL-Programm einen transienten LOB (CLOB, BLOB) benötigt, der größer als 32.767 Bytes sein soll, benötigt man einen temporären LOB. Das sieht dann etwa so aus:
A temporary LOB works like any other LOB but they don't reside in a table. If a developer needs a bigger (more than 32.767 bytes) text or binary object (CLOB, BLOB) only in a transient context they must use temporary lobs. Typical calls are as follows:
declare
  v_lob clob;
begin
  dbms_lob.createtemporary(
    lob_loc  => v_lob,
    cache    => true,
    dur      => DBMS_LOB.SESSION
  );
  -- do something ...
  dbms_lob.freetemporary(
    lob_loc  => v_lob
  );
end;
/
   
Ein solcher temporärer LOB kann so groß werden wie ein "normaler" LOB in einer Tabelle - nur, dass er eben nicht in einer Tabelle liegt. Wichtig ist der letzte Parameter durder Prozedur createtemporary. Ein DBMS_LOB.SESSION besagt, dass der temporäre LOB spätestens mit dem Ende der Session "stirbt", ein DBMS_LOB.CALL bewirkt, dass dies mit dem Ende des jeweiligen Aufrufs geschieht.
Such a temporary LOB can be as big as an "ordinary" table LOB. The last parameter of the CREATETEMPORARY call is important - it determines how long the temporary lob should exist. DBMS_SESSION lets the LOB live until it being freed or the session ends. DBMS_LOB.CALL lets the temporary LOB only exist for the time of the PL/SQL call.
Eine praktische Anwendung finden temporäre LOBs beim Generieren von XML - dieses XML wird häufig als Textstrom benötigt: Dazu wird mit der XMLTYPE-Methode GETCLOBVAL() ein temporärer LOB generiert, welchen der Entwickler dann weiterbearbeiten kann. Ein Beispiel in PL/SQL:
Temporary LOBs are used when XML is being generated by the database - such XML documents are often needed as text: This text is provided as CLOB by the GETCLOBVAL() function of XMLTYPE. This function creates a temporary lob with the XML text and passes is back to developer. The XML text can then be further processed - an example in PL/SQL:
declare
  v_xmllob clob;
begin
  for i in 1..10000 loop
    select xmlelement("custs", xmlagg(xmlelement("name", cust_last_name))).getclobval()
    into v_xmllob
    from sh.customers;
  end loop;
end;
/
Die getclobval()-Funktion des Typen XMLTYPE erzeugt einen temporären LOB mit dem XML-Text - dies lässt sich anhand der View V$TEMPORARY_LOBS auch feststellen ...
      SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ------------ -------------
      138          1            0             0
Der obige PL/SQL-Block ist noch kein Problem, denn im PL/SQL-Kontext räumt die Datenbank die temporären LOBs selbstständig auf - das merkt man auch anhand der View V$TEMPORARY_LOBS; auch wenn obige PL/SQL-Schleife etwas häufiger durchläuft - es sind stets nur wenige temporäre Lobs aktiv. Das ist auch in der Dokumentation (Temporary Lobs Performance Guidelines) so beschrieben. Anders sieht es aus, wenn man das gleiche "von außen" tut, bspw. mit Java ...
This PL/SQL block works fine since in a pure PL/SQL context the database cleans up unneeded temporary lobs independently. This can be observed by querying the view V$TEMPORARY_LOBS fron another session. Although the PL/SQL loop runs multiple times there are only few active temporary lobs. This is as described in the documentation (Temporary Lobs Performance Guidelines). But if the same is done "from outside" (java, .NET, etc) the picture is different ...
import java.sql.*;
import oracle.sql.*;

public class TempLobLeak {
  static Connection        con = null;
  static PreparedStatement pstmt = null;
  static ResultSet         rs = null;
  static Clob              lob = null;

  static int               iCounter = 0;

  public static void main(String args[]) throws Exception {
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.2.140:1521:orcl", "scott", "tiger");
    con.setAutoCommit(false);
    pstmt = con.prepareStatement(
      "select xmlelement(\"custs\", xmlagg(xmlelement(\"name\", cust_last_name))).getclobval() " +
      "from sh.customers"
    );
    // Erstes Kommandozeilen-Argument legt die Anzahl der Durchläufe fest
    while (iCounter < Integer.parseInt(args[0])) {
      // Query ausführen
      rs = pstmt.executeQuery();
      rs.next();
      // Lob nach Java ziehen
      lob = rs.getClob(1);
      iCounter++;
      if (iCounter % 10 == 0) {
        System.out.print(".");
      }
      rs.close();
    }
    pstmt.close();
    con.close();
  }
}
Lassen wir dieses Programm mal 10.000 mal laufen ...
Let's loop this program 10.000 times
$ java TempLobLeak 10000
........
Von einer anderen Session kann man sich nun die aktiven temporären LOBs anzeigen lassen - nun wächst die Zahl stetig an:
And from another session we'll monitor V$TEMPORARY_LOBS - now the active instances will increase continuingly:
      SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ------------ -------------
      138          0            0             0
      143          0            0             0
      148         96            0             0
Verfolgt man nun (auf UNIX bspw. mit einem top) den jeweiligen Datenbankprozeß, so stellt man fest, dass dieser mehr und mehr Speicherplatz verbraucht - das liegt einfach daran, dass der temporäre LOB nicht mehr freigegeben wird; nach außen hin ist ein Memory Leak entstanden.
Monitoring the memory usage of the database process (e.g. with unix top) shows that the memory consumption is also increasing - by definition we have implemented a memory leak. The reason is that we have not freed the resources allocated with each temporary lob.
Wie löst man das Problem? Ganz einfach: Den temporären LOB explizit freigeben (in PL/SQL mit DBMS_LOB.FREETEMPORARY, auf Java-Seite mit oracle.sql.CLOB.freeTemporary()). Das Java-Programm sähe dann so aus ...
How to solve the problem? This is easy: Just free the temporary lob! In PL/SQL one can use DBMS_LOB.FREETEMPORARY, java programmers use the method freeTemporary of the oracle.sql.CLOB object. The changed java program looks then like this ...
import java.sql.*;
import oracle.jdbc.*;
import oracle.sql.*;

public class TempLobLeak {
  static Connection        con = null;
  static PreparedStatement pstmt = null;
  static ResultSet         rs = null;
  static CLOB              lob = null;

  static int               iCounter = 0;

  public static void main(String args[]) throws Exception {
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.2.140:1521:orcl", "scott", "tiger");
    con.setAutoCommit(false);
    pstmt = con.prepareStatement(
      "select xmlelement(\"custs\", xmlagg(xmlelement(\"name\", cust_last_name))).getclobval() " +
      "from sh.customers"
    );
    // Erstes Kommandozeilen-Argument legt die Anzahl der Durchläufe fest
    while (iCounter < Integer.parseInt(args[0])) {
      // Query ausführen
      rs = pstmt.executeQuery();
      rs.next();
      // Lob nach Java ziehen
      lob = ((OracleResultSet)rs).getCLOB(1);
      iCounter++;
      if (iCounter % 10 == 0) {
        System.out.print(".");
      }
      rs.close();
      lob.freeTemporary();
    }
    pstmt.close();
    con.close();
  }
}
Startet man nun das Programm nochmals, so stellt man mit der View V$TEMPORARY_LOBS fest, dass stets nur ein bis drei temporäre LOBs aktiv sind - auch steigt der Speicherplatzbedarf der Datenbankprozesse nicht mehr an. Das Memory-Leak ist behoben.
Running the program again the V$TEMPORARY_LOBS shows a not increasing number of active temporary lobs (it should be one to three). The memory consumption does also not increase - there's no memory leak any more.
Bleibt also zusammenzufassen: Wenn mit temporären LOBs gearbeitet wird (und das ist immer der Fall, wenn größere Text- oder Binärobjekte lediglich "transient" ohne Tabelle genutzt werden) dann sollten diese stets explizit freigegeben werden. Ob temporäre LOBs für einen Memory Leak verantwortlich sind, lässt sich am besten mit V$TEMPORARY_LOBS überprüfen.
To summarize this: When working with temporary lobs (and this is always the case when "bigger" text or binary objects are used without storing them into a table) then these should always be freed explicitly. Querying the view V$TEMPORARY_LOBS while the programs / processes are running shows whether temporary lobs are involved.
Im reinen PL/SQL-Kontext kommt die Datenbank zwar ganz gut klar; aus Gründen der "sauberen Programmierung" sollte jedoch auch hier stets das explizite Freigeben der LOBs erfolgen.
This is also recommended within a "pure" PL/SQL context although the database - in this case - cleans up independently. Just in the sense of "good" programming style: every resource allocated should be freed.
Wo muss man noch aufpassen?
Which further areas do need the developers' attention?
Es gibt einige PL/SQL-Pakete, in welchen der Entwickler explizit Handles bzw. Speicherbereiche allokiert - diese müssen dann auch explizit wieder freigegeben werden. Beispiele dafür sind DBMS_XMLPARSER, DBMS_XMLDOM oder DBMS_XSLPROCESSOR. Hier ist es wichtig, die jeweiligen FREE... Prozeduren stets korrekt aufzurufen.
There are some PL/SQL packages in which the developer explicitly allocates memory - this memory must then also be freed explicitly. Examples for are DBMS_XMLPARSER, DBMS_XMLDOM or DBMS_XSLPROCESSOR. When using these packages it is important to call their FREE... procedures appropriately.

1 Kommentar:

Anonym hat gesagt…


Sie haben einen netten Blog hier Herr Czarski.

Das schönste memory leak hat deref bei mir produziert. Das kommt davon wenn man diese merkwürdigen objekt-relationalen Features nutzt.

Ist übrigens in 11.2.0.4 immer drinnen. Kaum macht man einen normalen join wird es schneller und das leak ist weg.

Aber man muss es ja erst mal finden, wenn sich das frühzeitig in den Code eingeschlichen hat.

Beliebte Postings