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.

27. März 2008

"Label Cloud" auf Blogger ...? Geht!

English title: Label Cloud on Blogger: Works!

Eine "Label Cloud" wollte ich schon immer auf dem Blog haben - gerade wenn man schon länger postet, ist es gut, mal zu sehen, womit man sich so beschäftigt hat - welche "Schwerpunkte" sich ergeben haben. "Blogger" bietet out-of-the-box nur nix an ... ich habe einige andere Möglichkeiten gefunden (technorati), war aber alles entweder recht kompliziert oder im Ergebnis nicht besonders schön. Anders war es mit diesem Tipp hier. Ein erstes Ergebnis war in 10 Minuten da - ein wenig verfeinern: 20 Minuten ... kann ich nur empfehlen!
I always wanted to have a "Label Cloud" on my blog: I'm posting for more than a year now - and its interesting to see on which topics the blog focus is - not everything was planned from the beginning on. Now, "Blogger" does not offer a tag cloud out-of-the-box, so I looked around for other ways: Technorati offers a widget, but I did'nt like the layout; others were very complex. Finally I found this posting. I had the first result in 10 minutes - then I spent another 10 minutes for adjusting the layout and ... finished. A very recommendation ...

26. März 2008

Dokumentation für "PL/SQL und Betriebssystemkommandos" verfügbar: Danke an "pldoc"

Documentation for file system interaction available: Thanks to pldoc!

Nun steht endlich auch eine Dokumentation für die PL/SQL Packages und Types zum Aufruf von Betriebssystem-Kommandos bereit. Ich hatte befürchtet, dass dies länger dauern wird, denn eine vernünftige, bedienbare Dokumentation für drei Packages und einen Objekttypen zu erstellem ist ja schon ein wenig Aufwans. Der Patrick Wolf hat mir aber in Bensheim den Tipp gegeben, mir pldoc, einen Open Source-Dokumentationsgenerator für PL/SQL anzusehen. pldoc funktioniert für PL/SQL wie javadoc für Java. Man kommentiert direkt im Code, verwendet spezielle Tags wie @param oder @return und der Parser erstellt dann automatisch eine HTML-Dokumentation, wie man sie von Java gewohnt ist.
Einziger Wermutstropfen: PL/DOC kann keine Objekttypen; und gerade die habe ich für den PL/SQL File Handle verwendet. Also habe ich für den pldoc Parser eine Package Spec geschrieben, welche dem Objekttypen entspricht ... nicht schön, aber machbar.
Schaut einfach mal rein - die neue Dokumentation befindet sich nur einen Link entfernt.
Nebenbei habe ich denn auch eine neue Version 0.5.1 eingestellt, denn die 0.5er hatte ein paar Fehler, so dass der Code auf einer 10er-Datenbank nicht kompilierte; das kommt davon, wenn man nicht richtig testet.
The documentation to my packages and types for operating and file system interaction is available. I was afraid that this would take a lot of time since creating a comprehensive and easy-to-use documentation for all the packages is some effort. But Patrick Wolf and I talked in Bensheim he pointed me to PLDOC, an open source documentation generator for PL/SQL. pldoc works for PL/SQL like javadoc for Java. It parses the comments, special tags like @param or @return as well as the PL/SQL code and generated a pretty HTML documentation.
There was just one point: pldoc cannot handle object types - and my implmentation for the file handle is an object type - so I created a package spec which describes the object type - not very nice but OK.
Just give it a try - the new API documentation is just a link away.
BTW: I added version 0.5.1 - the previous one had some bugs which prevented them from compiling in an Oracle10 database ... testing is everything I'm afraid ...

11. März 2008

Neue Version verfügbar: Betriebssystem-Kommandos mit SQL ausführen

New version available: Executing OS commands from the database

Seit der initialen Version ist nun einige Zeit vergangen - inzwischen ist die neue Version des PL/SQL-Packages zum Aufruf von Betriebssystem-Kommandos verfügbar. Ich habe die Anregung von Dietmar Aust aufgegriffen und Code zum Umgang mit Dateien und Verzeichnissen hinzugefügt. Außerdem habe ich gründlich reorganisiert, so dass nun mehrere PL/SQL-Objekte zur Verfügung stehen:
  • OS_COMMAND: zum Aufrufen von Betriebssystem-Kommandos; STDIN oder STDOUT können in die SQL-Ebene zurückübertragen und dort weiterverarbeitet werden
  • FILE_TYPE: (Objekttyp!) Repräsentiert einen File-Handle; damit können Dateien erstellt, umbenannt, kopiert ausgelesen oder geschrieben werden
  • FILE_PKG: Stellt die File-Handles (FILE_TYPE) bereit. File-Handles können einzeln, als einfaches oder rekursives Directory-Listing (virtuelle Tabelle!) abgerufen werden.
  • LOB_WRITER_PLSQL: das ist "nebenbei" entstanden; mit einen PL/SQL-Mitteln (ohne Java) können LOBs ins Dateisystem geschrieben werden
Was man mit den Packages machen kann (und wie einfach das dann ist), illustriert das folgende Beispielskript - es packt ein ZIP-Archiv in ein temporäres Verzeichnis aus und lädt alle entstandenen Dateien mit einem einzigen INSERT-Kommando in eine Tabelle:
Some time passed by since the initial version of the PL/SQL package OS_COMMAND - now the new version (0.5) is available. Based on the Dietmar Aust's comment I added some code to deal with files and folders (based on java.io.File). Beyond this the code is reorganized and now distributed over 4 PL/SQL objects:
  • OS_COMMAND: to execute shell commands from the SQL layer. STDIN or STDOUT contents can be passed between Java and the SQL layer
  • FILE_TYPE: (object type!) represents a file handle; allows to create, move, copy, write to and read from files
  • FILE_PKG: package to get a file handle - this might be a single file handle or multiple ones from a simple or recursive directory listing (the latter two are returned as a virtual table)
  • LOB_WRITER_PLSQL: this was created by the way; it allows to write a LOB to a file with pure PL/SQL - no java involved.
The following SQL script demonstrates the possibilities (and the simplicity) - it extracts a zip file into a temporary folder, loads all files into a database table (with one single INSERT command) and finally deletes the temporary directory.
set verify off

drop table document_table
/

drop sequence seq_documents
/

create table document_table(
  id number(10),
  file_path varchar2(4000),
  file_name varchar2(4000),
  document clob
)
/

create sequence seq_documents 
/


accept ZIPFILE default '/home/oracle/files.zip' prompt '>> Contents of which ZIP file to be loaded [/home/oracle/files.zip] '

declare
  f  file_type;
  fz file_type;

  r  number;
begin
  -- get a handle for the "tmp" directory
  f:=file_pkg.get_file('/tmp');

  -- create a new temporary directory where the zip archive is being
  -- extracted into ... make the filename unique using TIMESTAMP
  fz := f.create_dir(
    'zipdir_temp_'||user||'_'||to_char(systimestamp, 'YYYYMMDD_HH24MISS.SSSS')
  );

  -- DOIT: 
  -- extract the zipfile; the -qq switch is very important here - otherwise
  -- the OS process will not come back
  r := os_command.exec('unzip -o -qq &ZIPFILE. -d '||fz.file_path);

  -- if the result is 0 (=success) load the contents of the temporary directory
  -- (recursively) with ONE (!) SQL INSERT command
  if r = 0 then 
    insert into document_table (
      select 
        seq_documents.nextval id,
        e.file_path,
        e.file_name,
        file_pkg.get_file(e.file_path).get_content_as_clob('iso-8859-1') content 
      from table(file_pkg.get_recursive_file_list(fz)) e
    ); 
  end if;
  
  -- finally delete the temporary directory and its contents
  fz := fz.delete_recursive();
end;
/
sho err
Es fehlt noch die ausführliche Dokumentation der Packages - diese folgt nach; den Code wollte ich möglichst frühzeitig bereitstellen. Probiert es mal aus - An Feedback bin ich natürlich stets interessiert ...
A comprehensive documentation is missing at the moment - I wanted to release the code early. Just give it a try - Feedback is very appreciated.

9. März 2008

Oracle11g und Bind Variablen: Intelligent Cursor Sharing

English title: Oracle11g and bind variables: intelligent cursor sharing)

So, nach der Pause von zwei Wochen geht es nun wieder weiter mit dem Thema Datenbank-Performance aus Sicht des Anwendungsentwicklers. In den letzten zwei Wochen hielten meine Kollegen Bernhard, Haitham, Rainer und ich den Workshop Geodaten LIVE erleben ab. Der war wirklich klasse - die Hands-On mit den Teilnehmen haben richtig Spaß gemacht - ich denke, ich muss doch mal was zum Thema Geodaten schreiben. Aber dies am Rande ...
Here is (after a two weeks' "outage") the next post about database performance from the developers' point of view. During the last two weeks my collegues Bernhard, Haitham, Rainer and I held a workshop Experience spatial data LIVE which was really great. The hands-on to get a map based on spatial data in the web were fun - so I think I'll post something about spatial data in the Oracle database here - but these are some thoughts beside - now we'll come back to the topic:
Nach meinen letzten Post zum Thema Bind Variable Peeking hat der Patrick Wolf mich freundlicherweise darauf hingewiesen, dass es in Oracle11g hier einige Neuerungen gibt. In der Tat: Das im Post beschriebene Verhalten ist so nur bis Oracle10g (einschließlich) gültig. Oracle11g hat das sog. Intelligent Cursor Sharing eingeführt und arbeitet ein wenig geschickter - Dazu nun mehr ...
After my last post about "Bind Variable Peeking I got a comment from Patrick Wolf who told me that there's something new in Oracle11g. And really: The behaviour described in my post is valid for Oracle9i and Oracle10g but not for Oracle11g. Now the database has intelligent cursor sharing and the optimizer deals in a more "smart" manner with bind variables. Instead of translating this whole post to english I'd like to refer to the blog of the Oracle optimizer group. The post Why are there more cursors in 11g for my query containing bind variables? contains the background information about intelligent cursor sharing.
From my point of view there is a clear trend towards the general usage of bind variables for every kind of SQL statement. The Oracle11g optimizer takes the first step to always choose a good execution plan even when bind variables are used and there is skew in the data. So the recommendation to avoid bind variables in those cases does not apply in Oracle11g. As the Q & A section in the blog post of the optimizer group states it is even planned to enable this together with CURSOR_SHARING=FORCE - with this setting the optimizer does not see literals any more.
So if an application is being developed to run on Oracle11g (or higher) developers should basically prefer bind variables. Let the optimizer (based on histograms) decide whether to use static or "dymamic" execution plans. (The database behaviour has to be tested for the particular queries of the developers' application - of course!). If the application is being developed for Oracle9i (development towards Oracle9i? hopefully not!) or Oracle10g you have still to think about using literals for columns with data skews.
Wir nehmen wieder den Setup vom letzten Mal mit der Tabelle CUSTOMERS. Übrigens kann man auch in SQL*Plus ganz einfach mit Bindevariablen arbeiten. Und zwar so:
SQL> var v varchar2(10);
SQL> exec :v := 'A';
SQL> select /*test0001*/ sum(cust_id) from customers where status = :v;

SUM(CUST_ID)
------------
     1091134
Der SQL-Kommentar dient dazu, die Abfrage später in den Views V$SQL bzw. V$SQL_PLAN wiederzufinden. Schauen wir uns die Abfrage anschließend mal in der View V$SQL an.
select /*sqlareaselect*/ 
  sql_id, is_bind_sensitive, is_bind_aware, child_number, loaded_versions, last_load_time 
from  v$sql 
where 
  lower(sql_text) like '%test0001%' and not lower(sql_text) like '%sqlareaselect%'
order by last_load_time desc

SQL_ID        I I CHILD_NUMBER LOADED_VERSIONS LAST_LOAD_TIME
------------- - - ------------ --------------- ------------------------------------
0bqb5a0dq4qt4 Y N            0               1 2008-03-09/09:25:05 
Wir sehen die SQL ID und die child number des Cursors. Interessant sind die Spalten IS_BIND_SENSITIVE und IS_BIND_AWARE. Während die erste angibt, ob dieser Cursor für das neue intelligende Cursor Sharing in Frage kommt (hier: Ja), besagt die zweite, ob es für diese Abfrage bereits passiert ist. Mit DBMS_XPLAN.DISPLAY_CURSOR können wir uns auch den tatsächlichen Ausführungsplan anschauen ...
select * 
from table(
  dbms_xplan.display_cursor(
    sql_id          => '0bqb5a0dq4qt4',
    cursor_child_no => 0
  )
) 

SQL_ID  0bqb5a0dq4qt4, child number 0
-------------------------------------
select /*test0001*/ sum(cust_id) from customers where status=:v

Plan hash value: 2427356980

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE              |            |     1 |     7 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMERS  |    45 |   315 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_STATUS |    45 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("STATUS"=:V)
Gibt man bei cursor_child_no etwas anderes als 0 ein, so kommt nur die Meldung, dass es dieses Child nicht gibt - ist ja auch logisch. So weit so gut. Nun setzen wir die Bindevariable auf den Wert "C":
SQL> exec :v := 'C';
SQL> select /*test0001*/ sum(cust_id) from customers where status = :v;

SUM(CUST_ID)
------------
   119625495
Nun machen wir (wieder als SYS die gleichen Abfragen auf V$SQL und DBMS_XPLAN.DISPLAY_CURSOR nochmal - mit dem gleichen Ergebnissen wie vorhin. Also doch nix besonderes in Oracle11g? Schließlich hätte er für den Wert C einen full table scan machen sollen; das sind ja über 90% der Zeilen.
Aber nicht so voreilig. Setzen wir die gleiche SQL-Abfrage nochmals ab:
SQL> select /*test0001*/ sum(cust_id) from customers where status = :v;

SUM(CUST_ID)
------------
   119625495
Und nun noch einmal die Abfragen auf V$SQL und DBMS_XPLAN.DISPLAY_CURSOR ausführen ... und jetzt kommt's:
select /*sqlareaselect*/ 
  sql_id, is_bind_sensitive, is_bind_aware, child_number, loaded_versions, last_load_time 
from  v$sql 
where 
  lower(sql_text) like '%test0001%' and not lower(sql_text) like '%sqlareaselect%'
order by last_load_time desc

SQL_ID        I I CHILD_NUMBER LOADED_VERSIONS LAST_LOAD_TIME
------------- --- ------------ --------------- ------------------------------------
0bqb5a0dq4qt4 Y Y            1               1 2008-03-09/09:34:59
0bqb5a0dq4qt4 Y N            0               1 2008-03-09/09:25:05 
Für dieses SQL gibt es einen neuen Child Cursor - und dieser ist bind aware - es hat also kein "blindes" Cursor-Sharing wie in früheren Oracle-Versionen stattgefunden. Und wenn wir uns via DBMS_XPLAN mal den Ausführungsplan anschauen, dass kommt der Vorteil von Oracle11g heraus:
select * 
from table(
  dbms_xplan.display_cursor(
    sql_id          => '0bqb5a0dq4qt4',
    cursor_child_no => 1
  )
) 

SQL_ID  0bqb5a0dq4qt4, child number 1
-------------------------------------
select /*test0001*/ sum(cust_id) from customers where status=:i

Plan hash value: 296924608

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |    40 (100)|          |
|   1 |  SORT AGGREGATE    |           |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |  4528 | 31696 |    40   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STATUS"=:I)
Die Datenbank hat erkannt, dass der vorhandene Ausführungsplan - wegen der ungleichen Datenverteilung in der Spalte STATUS - suboptimal ist. Also wurde für das SQL ein neuer Ausführungsplan erstellt (ein erneuter hard parse hat stattgefunden). Das Ergebnis ist, dass für dieses SQL im Library Cache nun zwei Ausführungspläne vorhanden sind - ausgewählt wird je nach Inhalt der Bindevariable. Auf diesem Weg können auch noch mehr Ausführungspläne für dieses SQL entstehen.
Zusammengefasst: Der Oracle11g-Optimizer kann wesentlich besser mit Bindevariablen umgehen als die Versionen davor. Die Frage ist nun, ob sich die im letzten Post gemachte Aussage, wann man Bindevariablen einsetzen sollte und wann nicht, ändert ...
Zunächst: Der Trend bzw. die Absicht der Entwickler des Optimizers ist klar erkennbar - Bind Variablen sollen die erste Wahl sein! Der Optimizer in Oracle11g macht den ersten Schritt dazu, auch ungleich verteilten Daten intelligent mit Bind Variablen umzugehen und den jeweils passenden Ausführungsplan zu wählen. Damit kann man Bindevariablen nun auch dort verwenden, wo bislang (siehe letzter Post) davon abzuraten war. Wie im Blog der Oracle Optimizer Gruppe unter Q & A nachzulesen ist, ist auch geplant, dieses Feature im Zusammenhang mit CURSOR_SHARING=FORCE bereitzustellen (in Oracle 11.1 allerdings noch nicht). Mit dieser Einstellung sieht der Optimizer überhaupt keine Literale mehr. Man kann also deutlich sehen: Die Entwickler des Optimizer wollen Bindevariablen im SQL sehen.
So möchte ich folgendes Fazit ziehen: Wenn Anwendungen für 11g und höher entwickelt werden, sollte man grundsätzlich eher mit Bindevariablen als mit Stringverkettung arbeiten - überlassen wir dem Optimizer die Entscheidung, ob wechselnde oder gleiche Ausführungspläne besser sind (Natürlich muss jeder das Verhalten der Datenbank für seine Queries testen - Fehler gibt es immer!). Wenn für 9i (hoffentlich nicht) oder für 10g entwickelt wird, muss der Entwickler ein wenig nachdenken: bei ungleichen Datenverteilungen ist Stringverkettung vorzuziehen.

Beliebte Postings