"Oracle SQL und PL/SQL" von Carsten Czarski: Februar 2008

19. Februar 2008

Bind Variablen: Wirklich immer benutzen? (Was ist "Bind Variable Peeking")?

Heute geht mit dem Thema effizientes SQL - aus der Sicht des Entwicklers - weiter:

Im letzten Post habe ich den Performance-Effekt der Verwendung von Bindevariablen vorgestellt. Wie das Testprogramm zeigte, ist dieser Effekt ganz erheblich - wenn eine Applikation viele "gleichartige" SQL-Statements absetzt, hat die Verwendung (oder Nicht-Verwendung) von Bindevariablen signifikante Auswirkungen auf die Performamce.

Heute möchte ich auf die Frage eingehen, ob man wirklich immer Bindevariablen nutzen sollte (Ihr könnt es euch denken: Natürlich nicht). Das Thema Bind Variable Peeking wird dabei ein wichtiger Schwerpunkt sein. Zunächst jedoch nochmal ein paar Worte zur Abarbeitung von SQL-Kommandos in der Datenbank:

Parse: Das Statement wird syntaktisch und semantisch geprüft und es wird ein Ausführungsplan erstellt
Bind: Die Platzhalter der Bindevariablen werden durch die konkreten Werte ersetzt
Execute: Das Statement wird ausgeführt.

Der Ausführungsplan wird also während des "Parse" im ersten Schritt erstellt. Der Optimizer schaut dazu nach ...

... ob Indizes da sind ...
... wieviele Zeilen da sind ...
... ob die Query selektiv ist oder nicht ...

... und anderes mehr. Insbesondere die Selektivität ist ganz entscheidend für die Frage, ob und wie Indizes genutzt werden - je weniger selektiv eine Query (oder Subquery) ist, desto wahrscheinlicher ist ein Full Table Scan.

Doch wie kann der Optimizer die "Selektivität" bestimmen, wenn er die Werte der Bindevariablen gar nicht kennt ... ?

Naja, zunächst ist diese Frage nur dann relevant, wenn es ein extremes Ungleichgewicht in der Verteilung der Daten der jeweiligen Spalte gibt (wie im folgenden Beispiel). Bei Gleichverteilung ist die optimale Strategie vom konkreten Wert der Bindevariable unabhängig. Zunächst erstellen wir mit folgendem Skript eine Tabelle basierend auf der Tabelle CUSTOMERS im Schema SH. Dieser Tabelle wird eine neue Spalte STATUS hinzugefügt. 1% der Kunden werden den Status "A" und alle anderen die Status "B" oder "C" erhalten.

drop table customers
/

create table customers as select * from sh.customers
where rownum < 5000
/

alter table customers add (status char(1))
/

declare
  type number_array is table of number index by binary_integer;
  type char_array is table of varchar2(1) index by binary_integer;
  v_id_array  number_array;
  v_status_array char_array;

  v_random_val number;
begin
  select cust_id bulk collect into v_id_array from customers;
  for i in v_id_array.first..v_id_array.last loop
    v_random_val := dbms_random.value(0,100);
    if v_random_val < 1 then 
      v_status_array(i) := 'A';
    elsif v_random_val > 1 and v_random_val < 10 then
      v_status_array(i) := 'B';
    else 
      v_status_array(i) := 'C';
    end if;
  end loop;
  forall i in v_id_array.first..v_id_array.last 
    update customers set status = v_status_array(i)
    where cust_id = v_id_array(i);
end;
/
sho err

Nun wird die neue Spalte STATUS indiziert (IDX_STATUS). Schließlich werden die Statistiken erneuert (DBMS_STATS.GATHER_SCHEMA_STATS). Dabei stellt der Parameter method_opt sicher, dass ein Histogramm für die Spalte STATUS gebildet wird - mit einem solchen Histogramm "weiss" der Optimizer um die (ungleiche) Verteilung der Daten.

create index idx_status on customers (status)
/

exec dbms_stats.gather_schema_stats(ownname => user, method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');

Wie geht die Datenbank nun vor, wenn eine SQL Abfrage mit der Spalte STATUS in der WHERE-Klausel hereinkommt...?

select * from customers where status = 'A'

Wenn keine Bindevariablen verwendet werden, "sieht" die Datenbank den Wert "A" sofort und sie kann anhand der Histogramme nachschauen, wieviele Zeilen die Abfrage zurückliefern wird. Die Entscheidung, ob Index oder Full Table Scan nun besser sind, ist dann ein Leichtes. Im letzten Beispiel haben wir jedoch gesehen, dass man besser Bindevariablen verwenden sollte - was passiert dann ...?

select * from customers where status = :1

Die Datenbank "sieht" den Wert nun zur Parse-Zeit noch nicht. Allerdings muss der Optimizer einen Ausführungsplan erstellen - zur Lösung des Problems zieht er das Binding gewissermaßen vor (Bind Variable Peeking). Dieses Bind Variable Peeking wird pro SQL-Kommando aber nur einmal durchgeführt - wenn das gleiche SQL-Kommando erneut hereinkommt, wird der beim ersten Mal erstellte Ausführungsplan wiederverwendet. Und dies kann zu "seltsamen" Ergebnissen führen ... dazu zurück zum Beispiel:

Also: Die Tabelle wurde wie oben beschrieben erstellt und die Applikation nutzt Bindevariablen. Nach dem Hochfahren der Datenbank kommt also das SQL herein ...

select * from customers where status = :1

Wie gerade beschrieben, führt die Datenbank das Bind Variable Peeking durch; wir gehen davon aus, dass in diesem Fall nach dem Status "A" gefragt wird. Anhand des Histogramms stellt der Optimizer fest, dass nur 1% der Zeilen selektiert werden und er entscheidet sich für ...

SQL_ID  cfrfax3f40swx, child number 0
-------------------------------------
select * from customers where status = :1

Plan hash value: 2427356980

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |       |       |    40 (100)|          |
|   1 |  SORT AGGREGATE              |            |     1 |     6 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMERS  |    38 |   228 |    40   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_STATUS |    38 |       |    10   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   3 - access("STATUS"=:1)

... den Indexzugriff - und das ist gut so. Nun geht es weiter. Über die Applikation sucht der nächste Endanwender nach allen Kunden mit Status "C". Als SQL kommt wieder herein ...

select * from customers where status = :1

Nun kennt die Datenbank das SQL-Statement aber bereits - es wurde ja schonmal ausgeführt. Also findet kein Bind Variable Peeking statt - der beim erstem Mal erstellte Ausführungsplan wird wiederverwendet - die Datenbank macht für dieses SQL-Statement nun immer den Indexzugriff. Aber eigentlich wäre der Full Table Scan besser ...

Anderes Szenario: Die Datenbank wird durchgestartet und der erste Endanwender, der über die Anwendung auf die Datenbank kommt, sucht diesmal nach Kunden mit Status "C". Der Optimizer kennt das Statement noch nicht, also macht er ein Bind Variable Peeking - und kommt zu folgendem Ausführungsplan:

SQL_ID  bz2rfvv5145f5, child number 0
-------------------------------------
select * from customers where status = :1

Plan hash value: 296924608

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

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

   2 - filter("STATUS"=:1)

"Full Table Scan" (logisch - schließlich haben 90% der Kunden den Status "C"). Nun kommt der nächste Anwender und sucht nach den Kunden mit Status "A" ... und Ihr könnt euch den Rest denken: Das SQL ist ja nun bekannt, die Datenbank verwendet den bereits erstellten Ausführungsplan wieder und wird nun alle diese SQL-Abfragen mit Full Table Scans ausführen ... nicht so gut ...

Das Problem ist hier, dass wir eigentlich gerne je nach abgefragtem Status unterschiedliche Ausführungspläne hätten. Durch die Verwendung der Bindevariable ist das SQL-Statement textlich jedoch stets dasselbe. Mit Bind Variable Peeking wird für die erste Abfrage ein Ausführungsplan berechnet und dann beibehalten. Für ein SQL-Statement (der SQL-Text ist entscheidend) gibt es im SQL-Cache der Datenbank nur einen aktiven Ausführungsplan.

Dies ist also ein Fall, in dem es besser ist, das SQL per Stringverkettung zusammenzusetzen und keine(!) Bindevariablen zu verwenden. Denn dann liegen in der Datenbank für die verschiedenen Status textlich verschiedene SQL-Abfragen (mit unterschiedlichen Ausführungsplänen) vor. Bindevariablen sind zwar in den meisten Fällen, aber eben nicht "per-se" die beste Lösung:

Bindevariablen sollten nicht genutzt werden, wenn je nach Inhalt der Bindevariablen unterschiedliche Ausführungspläne erwartet werden
Bindevariablen sollten genutzt werden, wenn der Ausführungsplan von den Inhalten der Bindevariablen unabhängig ist und ohnehin stets gleich berechnet würde.

Oder anders gesagt: Als Entwickler sollte man sicherstellen, dass die Datenbank zur Parse-Zeit alle Informationen hat, die sie zur Erstellung eines guten Ausführungsplans braucht. Wenn Bindevariablen solche Informationen verstecken würden, sollte man sie weglassen.

Und wer Oracle11g nutzt, liest am besten gleich im nächsten Posting weiter, denn 11g bringt Lösungen für genau dieses Problem mit.

11. Februar 2008

Geht es auch schneller? Gedanken und Tipps zum SQL Tuning - für Entwickler

Heute und in den folgenden Ausgaben möchte ich mich dem Thema effizientes SQL widmen - und zwar aus der Sicht desjenigen, der das SQL in die Anwendungen einbaut: des Entwicklers!

Zu Beginn also etwas zum Thema Bindevariablen: Interessant ist ja, dass eigentlich nur die "Datenbankleute" einen Unterschied zwischen statischem und dynamischem SQL machen. Für den JDBC-Entwickler gibt es diesen Unterschied gar nicht; dort wird das SQL immer per Stringverkettung zusammengesetzt und dann an die Datenbank übergeben (executeQuery(), prepareStatement()). Eine Ausnahme ist SQLJ, aber das wird (soweit ich es beobachten kann) kaum genutzt.

Es kommt also darauf an, wie man das SQL zusammensetzt und vor allem, wie man dabei mit den Literalen umgeht:

Entweder verkettet man sie "in das SQL" hinein ... dann wird das SQL vom Entwickler fertig zusammengesetzt und an die Datenbank übergeben - die führt es sofort aus.
```
select * from sh.sales where cust_id = 4711
```
Oder man verwendet Bind-Variablen (in der Java-Welt auch PreparedStatements genannt). In diesem Fall verwendet man zunächst Platzhalter für die Literale ...
```
select * from sh.sales where cust_id = ?
```
... oder ...
```
select * from sh.sales where cust_id = :1
```
... und übergibt das SQL ohne Parameter an die Datenbank. Im nächsten Schritt werden die Platzhalter dann mit Werten belegt, dann führt die Datenbank das Statement aus.

Der Unterschied zwischen den beiden Varianten ist erheblich, wie das folgende kleine Java-Programm zeigt ...

Let's start with bind variables: It's interesting that only database guys make a difference between static and dynamic SQL. The Java/JDBC developer does not care about this - every SQL is concatenated as a string and then passed to the database via executeQuery() or prepareStatement. An exception is SQLJ but as of my experience this is used rarely.

import java.sql.*;

public class Bindtest {
  public static void main(String args[]) throws Exception {
    Connection        con = null;
    Statement         stmt = null;
    PreparedStatement pstmt = null;
    ResultSet         rs = null;

    long lStartTime;
    int  iCount = (Integer.parseInt(args[0]));

    String sSql = "";

    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.2.140:1521:orcl", "sh", "sh");
    con.setAutoCommit(false);

    System.out.println("1. Test: Stringverkettung");
    System.out.println("**************************");
   
    lStartTime = System.currentTimeMillis();
    stmt = con.createStatement();
    for (int i=0;i<iCount;i++) {
      sSql = "select * from sh.sales where cust_id = " + ((int)(Math.random() * iCount));
      rs = stmt.executeQuery(sSql);
      while (rs.next()) {
        // do nothing
      }
      if (i % ((int)(iCount / 80)) == 0) {
        System.out.print(".");
      }
      rs.close();
    }
    stmt.close();
    System.out.println();
    System.out.println("Zeit: " + (System.currentTimeMillis() - lStartTime) + "ms.");

    System.out.println("2. Test: Bindevariablen");
    System.out.println("************************");

    lStartTime = System.currentTimeMillis();
    pstmt = con.prepareStatement("select * from sh.sales where cust_id = ?");
    for (int i=0;i<iCount;i++) {
      pstmt.setInt(1, ((int)(Math.random() * iCount)));
      rs = pstmt.executeQuery();
      while (rs.next()) {
        // do nothing
      }
      if (i % ((int)(iCount / 80)) == 0) {
        System.out.print(".");
      }
      rs.close();
    }
    pstmt.close();
    System.out.println();
    System.out.println("Zeit: " + (System.currentTimeMillis() - lStartTime) + "ms.");
    con.close();
  }
}

Das Programm zeigt den Performance-Unterschied zwischen SQL-Abfragen mit Bindevariablen und einfacher Stringverkettung, indem einzele Zeilen aus der Tabelle SH.SALES wiederholt abgerufen werden. Dabei wird massiv SQL an die Datenbank abgesetzt. Ihr müsst den Connection String an eure Datenbank anpassen, um das Programm nutzen zu können; außerdem muss das SH-Schema vorhanden sein. Das Programm muss mit einem Parameter aufgerufen werden; dieser gibt an, wie oft die Tabelle SH.SALES abgefragt werden soll.

$ java Bindtest 5000

1. Test: Stringverkettung / Simple String concatenation
********************************************************
.................................................................................
Zeit: 38326ms.

2. Test: Bindevariablen / Bind variables
*****************************************
.................................................................................
Zeit: 18186ms.

Die Ergebnisse zeigen sehr deutlich, was die Bindevariablen bringen; in diesem Beispiel ist es Faktor 3. Das liegt einfach daran, dass im Falle der einfachen Stringverkettung jedes SQL-Kommando für die Datenbank neu ist - und daher führt die Datenbank für jedes SQL ein Parsing durch; sie prüft also, ob die Tabellen und Spalten vorhanden sind, ob die Zugriffsrechte OK sind und schließlich erstellt sie einen Ausführungsplan (welcher immer gleich aussehen dürfte). Das kostet für das einzelne Kommando nicht viel, aber es kostet etwas - und bei Last im Mehrbenutzerumfeld macht sich dieser Unterschied auch bemerkbar.

Man sieht hieran sehr schön, dass Bindevariablen (oder PreparedStatements) nicht zum Thema "schönerer Code", sondern zur Pflicht beim Umgang mit der Datenbank gehören (und das besonders, wenn die Maschine unter Last steht).

Und das gilt für alle Programmiersprachen; Java ist hier lediglich beispielhaft aufgeführt. Ihr erreicht die gleichen Ergebnisse mit C/C++, PHP, Perl, sogar mit PL/SQL und EXECUTE IMMEDIATE.

6. Februar 2008

MD5-Prüfsummen aus BLOBs oder CLOBs berechnen

Es gibt auch in der Datenbank viele Anwendungsfälle, in denen man Prüfsummen berechnen muss. Meist werden MD5-Prüfsummen verwendet. So kann man damit Duplikate erkennen oder die Integrität einer heruntergeladenen Datei überprüfen. Und wenn die Datei aus der Datenbank kommen soll, muss diese Prüfsumme auch in der Datenbank berechnet werden. Doch wie kann man eine MD5-Checksumme in der Datenbank berechnen?

Eine erste Idee ist es, nach vorhandenen PL/SQL-Prozeduren oder Funktionen zu suchen, man findet auch recht schnell DBMS_UTILITY.GET_HASH_VALUE. Allerdings funktioniert diese nur mit VARCHAR2-Variablen, womit wir auf 32Kb beschränkt wären.

Die Lösung lautet Java in der Datenbank. Java bietet im Package java.security standardmäßig Methoden zum Berechnen von MD5-Prüfsummen an - und die können wir auch in der Datenbank nutzen. Der folgende Code erstellt eine entsprechende Java-Klasse und die passenden PL/SQL-Wrapper zur Übergabe von BLOB oder CLOB-Datentypen.

create or replace java source named "MD5" as 
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Clob;
import java.sql.Blob;
 
public class MD5 {
  public static String getMD5HashFromClob(Clob inhalt) throws Exception{
    String sChecksum = null;

    if (inhalt != null) {
      try {
        String data = inhalt.getSubString(1L, (int)inhalt.length());
        MessageDigest md5 = MessageDigest.getInstance("MD5");
        md5.update(data.getBytes());
        byte result[] = md5.digest();
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < result.length; i++) {
            String s = Integer.toHexString(result[i]);
            int length = s.length();
            if (length >= 2) {
                sb.append(s.substring(length - 2, length));
            } else {
                sb.append("0");
                sb.append(s);
            }
        }
        sChecksum = sb.toString();
      } catch (NoSuchAlgorithmException e) {
        // do nothing
      }
    }
    return sChecksum;
  } 

  public static String getMD5HashFromBlob(Blob inhalt) throws Exception{
    String sChecksum = null;

    if (inhalt != null) {
      try {
        MessageDigest md5 = MessageDigest.getInstance("MD5");
        md5.update(inhalt.getBytes(1L, (int)inhalt.length()));
        byte result[] = md5.digest();
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < result.length; i++) {
            String s = Integer.toHexString(result[i]);
            int length = s.length();
            if (length >= 2) {
                sb.append(s.substring(length - 2, length));
            } else {
                sb.append("0");
                sb.append(s);
            }
        }
        sChecksum = sb.toString();
      } catch (NoSuchAlgorithmException e) {
        // do nothing
      }
    }
    return sChecksum;
  }
}
/
sho err

alter java source "MD5" compile
/
sho err

CREATE OR REPLACE FUNCTION get_md5_CLOB(inhalt CLOB) RETURN VARCHAR2 DETERMINISTIC
AS LANGUAGE JAVA
name 'MD5.getMD5HashFromClob(java.sql.Clob) return java.lang.String';
/

CREATE OR REPLACE FUNCTION get_md5_BLOB(inhalt BLOB) RETURN VARCHAR2 DETERMINISTIC
AS LANGUAGE JAVA
name 'MD5.getMD5HashFromBlob(java.sql.Blob) return java.lang.String';
/

Vielen Dank übrigens an Michael Seiwert aus Hamburg für die Idee und auch Hilfe bei der Umsetzung