Die dunkle Seite des Parsens

Performance Unterschiede bei der Verwendung von Nativen Queries vs. Prepared Statements mit Oracle

Native Datenbankabfragen in einer Anwendung erschweren im Allgemeinen deren Wartbarkeit bzw. die Möglichkeit, unabhängig von einer spezifischen Datenbank zu entwickeln. Im Rahmen der Performance Analyse einer Java-Altanwendung mit Oracle Datenbank konnten wir noch einen weiteren Aspekt entdecken, der gegen die Verwendung solcher Abfragen spricht: die Performance gegenüber Prepared Statements im Kontext einer Oracle Datenbank.

Ausgangspunkt war eine Analyse durch den Automatic Database Diagnostic Monitor (ADDM) von Oracle [1]. Dieser analysiert unter anderem SQL Abfragen und gibt Empfehlungen. Im vorliegenden Fall lieferte er für eine ganze Reihe von Abfragen folgende Meldung:

Hard Parse aufgrund von literaler Auslastung: SQL Anweisungen wurden wegen der Verwendung von Literalen nicht gemeinsam benutzt. Dies hat zu zusätzlichen Hard Parses geführt, die wesentliche Datenbankzeit belegt haben.

Überprüfen Sie die Anwendungslogik auf die mögliche Benutzung von Bindevariablen anstelle von Literalen.

Verwendung von Literalen bedeutet dabei das direkte Einfügen der Werte in Bedingungen, beispielsweise:

    SELECT * FROM meine_tabelle WHERE mein_feld = 123

Demgegenüber steht bei der Verwendung von Bindevariablen nur ein Platzhalter anstelle eines konkreten Wertes und wird erst im folgenden Schritt eingefügt.

Performance-Analyse Literal vs. Bindevariable

Welche Auswirkung auf die Performance der Datenbankabfragen haben nun aber diese beiden Varianten und welche Ursachen gibt es hierfür? Hierfür ist ein Blick in die Query Verarbeitung von Oracle notwendig. Vor der eigentlichen Ausführung muss der Query geparsed werden, um so mögliche Abarbeitungspläne zu ermitteln. Hier wird beispielsweise ermittelt, ob und wie ein Index genutzt werden kann. Dieses “Hard Parse” ist notwendig, um den optimalen Weg der Abarbeitung zu ermitteln und kann mehr Zeit in Anspruch nehmen, als die eigentliche Ausführung. Allerdings speichert sich Oracle einen solchen Abarbeitungsplan zu einem Query – soll dieser erneut ausgeführt werden, entfällt der Overhead des Parsens. Werden Literale benutzt, so funktioniert dies nur, wenn auch deren Werte gleich sind.

Bei der Nutzung von Bindevariablen hingegen, erstellt Oracle den Abarbeitungsplan für die Abfrage mit den Platzhaltern und speichert dies auch so. Es ist daher egal, ob dieser Query später mit anderen Werten ausgeführt wird – als Vergleich dient immer die Abfrage mit den Bindevariablen. Ein vorher ermittelter Abarbeitungsplan kann somit erneut genutzt werden – das Parsen entfällt.

Doch wie viel macht dies tatsächlich aus? Da die von uns untersuchte Java-Altanwendung noch rein mit JDBC Mitteln arbeitet, wurde eine kleine Beispielanwendung geschrieben, welche ebenfalls nur JDBC nutzt und identische Abfragen sowohl als nativen Query als auch mittels Prepared Statement absetzt. Beim Nutzen von Prepared Statements kümmert sich JDBC darum, die Bindevariablen an Oracle zu übergeben. Die konkreten Abfragen waren dabei an die angelehnt, welche der ADDM als problematisch gemeldet hatte. Für die Tests führten wir jeweils mehrere hundert Abfragen mit unterschiedlichen Werten und auch gegen verschiedene Oracle Instanzen aus – im Ergebnis war die Ausführung der Prepared Statements stets um den Faktor 10 bis teilweise 100 schneller! So einen dramatischen Unterschied hatten wir nicht erwartet – jedoch deckt es sich auch mit den Messungen unter [2]. Die Tests haben wir dann nochmals wiederholt unter Nutzung von JPA und EclipseLink, das Zeitverhalten war vergleichbar gut dem der Prepared Statements.

Hieraus ergab sich nun die ganz klare Empfehlung, die Java-Anwendung – zumindest in den relevanten Komponenten – auf die Nutzung von Prepared Statements umzustellen. Die notwendigen Änderungen am Code sind dabei überschaubar, wie das folgende Beispiel zeigt:

    import java.sql.Connection;
    import java.sql.Statement;
    import java.sql.PreparedStatement;
    ...
    // 1. Query mit Literal als nativer Query
    Statement stmt = connection.createStatement();
    stmt.executeQuery("select * from meine_tabelle where mein_feld = 123");
    // 2. Query mit Bindevariable unter Nutzung von PreparedStatement
    String query = "select * from meine_tabelle where mein_feld = ?";
    PreparedStatement stmt2 = connection.prepareStatement(query);
    stmt2.setInt(1, 123);
    stmt2.executeQuery();

Cursor Sharing – Tuning auf der Datenbank

Zum Abschluss soll noch eine Möglichkeit erwähnt werden, welche sich direkt auf der Oracle Datenbank selbst bietet – beispielsweise, wenn der entsprechende Code nicht umgebaut werden kann. Im Analyse Ergebnis des ADDM wurde noch folgender Hinweis gegeben:

Alternativ können Sie den Parameter “cursor_sharing” auf “force” festlegen.“

Normalerweise werden nur identische Abfragen auch gleich behandelt, bzw. nicht neu geparsed – daher der Vorteil der Nutzung von Bindevariablen bzw. Prepared Statements. Wird dieser Parameter jedoch auf “force” gesetzt, so werden auch Queries als gleich behandelt, wenn diese sich nur in den genutzten Literalen unterscheiden. Klingt also erst einmal wie ein Quick Win – die Codeänderungen wären also überflüssig. Jedoch rät Oracle selbst schon davon ab (ohne jedoch ins Detail zu gehen) dies einfach so zu tun. Eine umfangreichere Beschreibung des Parameters inklusive seiner Vor- und Nachteile gibt es für Interessierte hier.

Fazit

Mit kleinen Codeänderungen lässt sich auch bei älteren Java Anwendungen, welche viele native Queries einsetzen, einiges an Performance herausholen – zumindest wenn die Datenbank Oracle heißt. Ein Umbau auf Prepared Statements lohnt – vor allem dann, wenn die Anwendung ähnliche Abfragen oft durchführen muss. Ob auch andere DBMS solche Unterschiede in der Performance zwischen Literalen und Bindevariablen aufweisen, konnten wir nicht prüfen, ist aber denkbar.

---

Siehe auch:

[1] Weitere Infos zum ADDM: Oracle 12c: Datenbank Tuning und Monitoring
[2] Details zum Hard Parse und dessen Performance: Bind variables – The key to application performance

Dieser Beitrag wurde verfasst von: