Über unsMediaKontaktImpressum
Jürgen Sieben 23. Februar 2016

Oracle Database: Deterministische Funktionen in PL/SQL

Dieser Artikel ist ein weiterer einer kleinen Reihe von Fundstellen, die mich bei der Lektüre von Fachliteratur zu Widerspruch gereizt haben. Mir geht es natürlich nicht darum, mit dem Finger auf andere Autoren zu zeigen. Vielmehr finden sich hier Anker, an denen ich ein Thema erläutern möchte, von dem ich glaube, dass es für viele Leser interessant sein könnte. Hier nun einige Überlegungen zur Deterministic-Klausel in Oracle PL/SQL-Funktionen.

Die Fundstelle

Diesmal war es keine Veröffentlichung, sondern eine Liste von Themen, die ich von einem Kunden bekam, um eine Inhouse-Schulung vorzubereiten, die ich halten sollte. Es ist nur ein Spiegelstrich in einer längeren Liste, aber so ist das halt manchmal: Daran entzündete sich die Idee für diesen Artikel.

How can I make things go faster? – Don't!

Der Punkt lautet:

  • deterministic function
    Soweit ich weiß, macht dies keinen Sinn bei Nutzung in Oracle PL/SQL, nur beim Aufruf von SQL.

Das stimmt fast, oder sagen wir besser, es hat einmal gestimmt. Doch das Thema ist vielschichtig. Was denn nun: Deterministic, Result Cache, relies on-Klausel...? Sehen wir uns einmal den aktuellen Stand und die Optionen an:

Das Problem: Performance in SQL und PL/SQL

Dem alten Performance-Tuner-Spruch "How can I make things go faster? – Don't!" folgend, bemühen wir uns bei der Programmierung natürlich, unnötige Berechnungen auch nicht durchführen zu lassen. Das ist auch in Oracle PL/SQL nicht anders. Wenn eine Funktion aufgerufen wird, die für einen gegebenen Parameter stets das gleiche Ergebnis liefern wird, wie das etwa für die Funktionen lower, upper etc. gilt, sollten wir doch nicht gezwungen sein, die immer gleichen Berechnungsschritte auszuführen. Insbesondere gilt dies im Umfeld von SQL, weil hier die Funktion millionenfach ausgeführt werden kann, je nachdem, wie die Funktion aufgerufen wird und wie viele Zeilen die Tabelle enthält. Aber auch in reinem PL/SQL, in einer Schleife etwa, stellt sich das Problem. Hier vielleicht nicht durch die reine Anzahl der Aufrufe, aber zum Beispiel auf Grund der komplexen, zeitaufwändigen Berechnungen, die in der Funktion ausgeführt werden müssen.

Allerdings sollten wir uns vorab klarmachen, wie groß das Problem eigentlich ist. Durch alle Optionen, die wir im folgenden besprechen, beschleunigen wir die PL/SQL-Funktion, indem wir unnötige Berechnungen unterbinden. Damit wir aber auch einen Performanzeffekt spüren, muss in PL/SQL auch signifikant viel Arbeit erbracht werden. Die Funktion lower() ist insofern kein wirklich gutes Beispiel, es sei denn, sie rufen sie viele, viele, viele (!) Male und dann auch noch mit den gleichen Parametern auf.

Lösungsansätze: Deterministische Funktionen und Result Cache

Sie haben also eine Funktion, für die gilt: Sie macht erheblich Arbeit, liefert für eine gegebene Kombination von Parametern aber immer ein identisches Ergebnis. Zudem wird diese Funktion potenziell sehr häufig benötigt und zwar nicht aus SQL heraus (dann müssen wir das Problem der Umgebungswechsel lösen), sondern aus PL/SQL heraus. Viele Wenn und Aber, aber so sind nun einmal die Regeln. Erfüllt Ihre Funktion diese Regeln nicht, ist das Folgende vielleicht eher von allgemeinem Interesse, wirklich schneller wird Ihre Anwendung durch diese Möglichkeiten wohl eher nicht.

Erste Variante: Die Klausel DETERMINISTIC für Oracle PL/SQL-Funktionen

Zunächst können Sie die Funktion (und nur eine Funktion, für Prozeduren gilt dies naturgemäß nicht) als deterministisch kennzeichnen, indem Sie die Klausel deterministic hinter der Parameterdeklaration einfügen:

create or replace function deterministic_test(
  p_in number)
  return number
  deterministic
as
  ...
end deterministic_test;

Sie teilen damit der Datenbank mit, dass diese Funktion für die gleichen Parameter stets die gleichen Resultate liefern wird. Das ist insofern wichtig, als die Datenbank dies nicht weiter prüft, sondern sich auf Ihr Wort verlässt. Leider, das soll bereits hier gesagt werden, sind gar nicht so viele Funktionen deterministisch, wie Sie sich vielleicht wünschen: Aufrufe der Funktionen user, dbms_random oder sysdate innerhalb der Funktion verbieten sich ebenso wie Resultate, die auf der Abfrage von Tabellendaten beruhen, selbst wenn dies Stammdaten sind, die sich nicht häufig ändern. Auch die – in APEX-Kreisen allgegenwärtige – Funktion v, die Daten aus dem Session State liest, ist nicht deterministisch.

Nebenbei bemerkt muss eine Funktion deterministisch sein, wenn Sie auf dieser Funktion einen Index oder eine virtuelle Spalte aufsetzen möchten. Achten Sie in diesem Zusammenhang auch darauf, dass sich die Implementierung der Funktion über die Zeit ändern und damit die Ergebnisse ebenfalls andere sein könnten. Das bekommt weder die virtuelle Spalte, noch der funktionsbasierte Index mit!

Was aber tut die Datenbank, wenn die Funktion als deterministisch deklariert wurde? Zunächst einmal recht wenig. Bis Version 9 sogar offensichtlich gar nichts, dann jedoch wurde ein Cache eingeführt, der beim Aufruf einer PL/SQL-Funktion aus SQL Ergebnisse zwischenspeichern und somit wiederverwenden konnte. Erst ab Version 11.2 wurde ein solcher Cache auch für die Arbeit in PL/SQL selbst eingeführt, so dass diese Klausel sich nun zunehmend als sinnvoll herausstellt.

Wird also innerhalb von PL/SQL dieser Cache eingerichtet, hat dies zur Folge, dass die Datenbank Ergebnisse bereits berechneter Prozeduraufrufe im Rahmen des jeweiligen Server Calls speichert (das wird später noch bedeutsam). Weitere Aufrufe der Funktion mit gleichen Parametern werden also nicht mehr neu berechnet, sondern aus dem Cache geliefert. In SQL gilt der Fokus analog: Die Ergebnisse werden innerhalb einer Abfrage im Cache vorgehalten, nicht darüber hinaus.

Vielleicht ein Beispiel. Ich verwende hier die einfachste Implementierung, die mir einfiel, um den Effekt zu zeigen. Das erste Listing zeigt zunächst ein kleines Hilfspackage, um die Zeit zu stoppen:

Listing 1

SQL> create or replace package utl_timer
  2  is
  3    procedure start_timer;
  4    function get_duration
  5      return varchar2;
  6  end utl_timer;
  7  /
Package wurde erstellt.
SQL> create or replace package body utl_timer
  2  is
  3
  4    l_point_in_time number := null;
  5
  6    procedure start_timer
  7    is
  8    begin
  9      l_point_in_time := dbms_utility.get_time;
 10    end;
 11
 12    function get_duration
 13      return varchar2
 14    is
 15      l_duration number;
 16    begin
 17      l_duration := (dbms_utility.get_time - l_point_in_time) / 100;
 18      return l_duration || ' Sek.';
 19    end;
 20
 21  end utl_timer;
 22  /
Package Body wurde erstellt.

Nun folgt der eigentliche Test. Die Funktion nimmt einen Parameter entgegen, wartet die angegebene Zeit und meldet sich zurück. Die Funktion wird einmal mit, einmal ohne den Hint deterministic implementiert, wie in Listing 2 gezeigt:

Listing 2:

SQL> create or replace function test_deterministic(
  2    p_n number)
  3    return number
  4    deterministic
  5  is
  6  begin
  7    dbms_lock.sleep(p_n);
  8    return p_n;
  9  end test_deterministic;
 10  /
Funktion wurde erstellt.
SQL> create or replace function test_non_deterministic(
  2    p_n number)
  3    return number
  4  is
  5  begin
  6    dbms_lock.sleep(p_n);
  7    return p_n;
  8  end test_non_deterministic;
  9  /
Funktion wurde erstellt.

Der Test zeigt, dass die deterministische Funktion nur einmal, die nicht deterministische Funktion hingegen mehrfach aufgerufen wird:

Listing 3:

SQL> set serveroutput on
SQL> declare
  2    l_n number;
  3  begin
  4    utl_timer.start_timer;
  5    for indx in 1 .. 10
  6    loop
  7      l_n := test_deterministic (0.5);
  8    end loop;
  9    dbms_output.put_line('Dauer deterministisch: '
 10      || utl_timer.get_duration);
 11
 12    utl_timer.start_timer;
 13    for indx in 1 .. 10
 14    loop
 15      l_n := test_non_deterministic (0.5);
 16    end loop;
 17    dbms_output.put_line('Dauer nicht deterministisch: '
 18      || utl_timer.get_duration);
 19  end;
 20  /
Dauer deterministisch: ,5 Sek.
Dauer nicht deterministisch: 5,02 Sek.

PL/SQL-Prozedur erfolgreich abgeschlossen.

Diese Effekte gelten nur im Umfeld eines Server-Calls, wie Sie erkennen können, wenn wir in der gleichen Session die Funktion erneut aufrufen:

SQL> r
  ...
Dauer deterministisch: ,5 Sek.
Dauer nicht deterministisch: 5,02 Sek.

Wäre ein Session-Cache erstellt worden, hätte die Ausführung der deterministischen Funktionsvariante nicht 0,5 Sekunden gedauert.

Diese Optimierung ist, wie gesagt, mit Version 11.2 neu in die Datenbank gekommen. Vorher wurde diese Optimierung nur dann durchgeführt, wenn die PL/SQL-Funktion aus SQL aufgerufen wurde, und dies auch erst ab Version 10.

Zweite Variante: Oracle Function Result Cache

Der Function Result Cache bezeichnet eine Funktionalität, die es der Datenbank ermöglicht, Ergebnisse von PL/SQL-Prozeduren in einen Cache zu speichern, der nicht nur Session-bezogen, sondern Instanz-bezogen in der SGA vorgehalten wird. Eine aufwändige PL/SQL-Funktion kann so einmal gerechnet werden, die Daten werden zentral zur Verfügung gestellt und können direkt aus anderen Sessions genutzt werden. Analog zum deterministic-Hint wird einfach die Klausel result_cache an gleicher Stelle der Funktionsdeklaration eingesetzt, um der Datenbank mitzuteilen, dass diese Funktion für ein Caching in Frage kommt (und um Oracle mitzuteilen, dass Sie gern die Enterprise Edition-Lizenzkosten zahlen möchten). Diese Funktionalität bezieht sich nicht nur auf deterministische Funktionen, sondern auch auf nicht-deterministische Funktionen. Oracle prüft selbstständig die Abhängigkeiten, denen die Funktion unterliegt (insbesondere Tabellendaten, auf die die Funktion zugreift). Ändern sich diese, wird das Ergebnis der Berechnung invalidiert, die Funktion rechnet erneut.

In diesem Zusammenhang war bis Version 11.2 die Klausel relies on wichtig, denn mit dieser Klausel konnte definiert werden, von welchen Tabellen das Ergebnis der Funktion abhängig ist. Ändern sich diese Tabellen, werden also auch die Funktionsergebnisse im Cache invalide. Mit Version 11.2 ist diese Klausel nicht mehr erforderlich und tut einfach gar nichts mehr, die Datenbank evaluiert die Abhängigkeiten selbst.

Für diese Funktionalität gelten folgende Warnhinweise:

  • Sie ist derzeit an die Enterprise Edition gebunden.
  • Sie vermeidet keine Umgebungswechsel zwischen SQL und PL/SQL, im Zweifel ist also eine skalare Unterabfrage billiger und schneller.
  • Da Umgebungswechsel dennoch erforderlich sind, bietet sich die Kombination mit einer skalaren Unterabfrage an. Hier wird der Cache auf der PL/SQL-Seite genutzt und die Umgebungswechsel durch die skalare Unterabfrage genutzt.
  • Es scheint, als habe der Result Cache einigen Overhead bezüglich der Cacheverwaltung, was auffällt, wenn Sie eine Funktion sehr oft aufrufen, die Rechenzeit in der Funktion aber klein ist. Daher lohnt diese Technologie vor allem dann, wenn die Rechenzeit der Funktion groß gegen diesen Overhead ist.

Man kann diese Funktionalität empfehlen, wenn komplexe, lang laufende Berechnungen in PL/SQL dadurch eingespart werden können, nicht jedoch als Standardmittel. Anderen wird die Beschränkung auf die Enterprise Edition dieses Feature verleiden. Aber vielleicht wird diese Technik ja – wie ehedem die funktionsbasierten Indizes – aus der Enterprise Edition in die Standard Edition aufgenommen.

Zusammenfassung

Was lernen wir daraus? Die eigentliche Nachricht ist, dass wir – wieder einmal – der Datenbank so genau wie möglich sagen sollten, was wir über den Code wissen. Es ist nicht realistisch, dass Ihre Beschäftigung mit einer neuen Version der Datenbank stets so intensiv ist, dass Sie Änderungen wie das geänderte Verhalten des deterministic-Hints mitbekommen und danach sofort all Ihren Code refaktorisieren. Aber diesen Hint gibt es schon lange. Und wenn man auch in vielen Blogs lesen kann, er solle nicht genutzt werden, da er nichts bringe und daher nur verwirre: Ich bin anderer Meinung. Wenn eine Funktion deterministisch ist, sagen Sie das. Ob die Datenbank eine Optimierung daraus ableiten kann oder nicht, entscheidet die Datenbank. Aber selbst, wenn die aktuelle Version keine Optimierung anbieten kann, eine zukünftige wird es können, ansonsten wäre die Einführung eines entsprechenden Hints nicht plausibel. Ähnlich können Sie es übrigens mit dem – seit Version 12c verfügbaren – Pragma UDF (User Defined Function) halten, mit dem Sie kennzeichnen, dass eine Funktion hauptsächlich aus SQL heraus aufgerufen wird. Auch dieses Pragma bringt im Moment noch nicht viel, aber das muss nicht so bleiben.

Der Trick mit der skalaren Unterabfrage ist und bleibt cool.

Im Gegensatz hierzu ist die Klausel result_cache ein echtes Tuning, das einerseits die Enterprise Edition voraussetzt und andererseits genau daraufhin durchgesehen werden muss, ob der Einsatz wirklich etwas bringt. Ob tatsächlich eine Beschleunigung eintritt oder diese durch zusätzlichen Verwaltungsaufwand der Datenbank wieder einkassiert wird, müssen Sie testen. Im Gegensatz zu den anderen Lösungen übersteht diese Option aber sogar ein Ab- und Anmelden, da der Cache in der SGA gelagert und daher allen angemeldeten Benutzern zur Verfügung gestellt wird. Auch das kann eine interessante Beschleunigung Ihrer Anwendung sein.

Ein interessantes Feature also, aber sicher keine Killerfunktionalität. Es bleibt dabei: Diese Optimierungen können im ein oder anderen Fall wirklich drastische Wirkungen haben, im Regelfall stellen sie aber wohl eher eine Evolution und keine Revolution dar. Cool ist und bleibt der Trick mit der skalaren Unterabfrage, er kostet nichts und bringt viel – aber eben auch nur im Zusammenspiel zwischen SQL und PL/SQL, nicht in reinem PL/SQL.

Autor

Jürgen Sieben

Jürgen Sieben ist Autor des Buchs "Oracle PL/SQL: Das umfassende Handbuch", das bei Galileo erschienen ist und Geschäftsführer der ConDeS GmbH
>> Weiterlesen
Bücher des Autors:

botMessage_toctoc_comments_9210