Über unsMediaKontaktImpressum
Jürgen Sieben 24. Februar 2015

Oracle-Optimierung: Umgebungswechel bei PL/SQL vermeiden

Performance-Tuning ist ein weites Feld mit vielen Facetten. Doch es gibt Dinge, die generell als Best Practice gelten und die Grundlage für weitere Tuningmaßnahmen bilden. Aus meinen Kursen zum Thema weiß ich, dass oftmals diese Grundlagen nicht ausreichend berücksichtigt werden und die Hoffnung eher auf exotische Optimierungen gelegt wird, anstatt kritisch zu hinterfragen, ob es nicht die Basics sind, die zunächst einmal bereinigt werden müssen.

Eine besondere Rolle fällt in diesem Zusammenhang den Umgebungswechseln zwischen SQL und PL/SQL zu. Sie sind – meist unbemerkt – für erhebliche Einbußen der Performance verantwortlich. Dieser Artikel zeigt die grundlegenden Mechanismen auf und erläutert, wie ungewollte Umgebungswechsel erkannt und vermieden werden können.

Was sind Umgebungswechsel?

Seit vielen Versionen der Datenbank enthält die Programmiersprache PL/SQL keine eigene SQL-Implementierung mehr, sondern ruft die SQL-Implementierung der Datenbank auf, wenn entsprechende Anweisungen im Code auftreten. Wechselt die Kontrolle von PL/SQL zu SQL oder umgekehrt, entsteht ein Umgebungswechsel, der auf Grund der Einrichtung der Umgebungsvariablen etc. einen erheblichen Aufwand für die Datenbank darstellt.

Solche Umgebungswechsel haben enormen Einfluss auf die Gesamtleistung der Anwendung und sollten daher so selten wie möglich auftreten. Vor der Vermeidung steht jedoch das Erkennen von Umgebungswechseln und das kann sehr einfach, aber auch sehr vertrackt sein. Beginnen wir mit den einfachen Beispielen.

Umgebungswechsel von SQL nach PL/SQL

Hier sind es vor allem zwei Szenarien, die immer wieder anzutreffen sind:

  • Aufruf von PL/SQL-Funktionen aus SQL
  • Zeilentrigger auf Tabellen

Funktionsaufrufe aus SQL

Sehen wir uns ein einfaches Beispiel für einen Funktionsaufruf aus SQL an:

select *
  from emp
where deptno = v('P10_DEPTNO');

Die Funktion v ist im Schema APEX_nnnnn definiert und bietet dem Entwickler Zugriff auf Informationen, die sich im Session State von APEX befinden. Eigentlich ist dies aber für das Beispiel unerheblich, denn jede andere Funktion können Sie sich hier ebenfalls denken. Der Parameter P10_DEPTNO bezeichnet ein Element auf einer APEX-Anwendungsseite, er ist eine Konstante und die Funktion mit Sicherheit für die Dauer der Abfrage deterministisch, das heißt, sie wird für den gleichen Eingangsparameter im Kontext dieser select-Abfrage das gleiche Ergebnis liefern. Sollte SQL nicht so clever sein, die Funktion nur einmal aufzurufen anstatt für jede Zeile?

Die Antwort lautet: Nein. Die Funktion selbst ist nicht deterministisch: Ein anderer Benutzer mit anderem Session-Identifier wird einen anderen Wert für das Element P10_DEPTNO erhalten können, und auch zwischen zwei Abfragen innerhalb der gleichen Benutzersession können die Ergebnisse für den Funktionsaufruf mit diesem Parameter differieren. Die Datenbank weiß also nicht, welches Ergebnis die Funktion liefert, sondern muss es aktuell erfragen (Die Daten stehen in einer Tabelle des Benutzers APEX_nnnnn, was noch einmal einen Umgebungswechsel von PL/SQL zurück zu SQL zur Folge hat!). Daher hätte noch nicht einmal PL/SQL die Möglichkeit, das Ergebnis zu cachen. Viel wichtiger ist jedoch, dass SQL nicht weiß, welche Werte die Funktion zurückliefern wird. Daher muss für jede Zeile die Funktion erneut aufgerufen und von PL/SQL neu berechnet werden.

Dies ist besonders fatal, weil die Funktion in der where-Klausel der Abfrage verwendet wird, denn sie wird in jedem Fall für jede Zeile der Tabelle aufgerufen: Beinhaltet die Tabelle 1 Million Zeilen, von denen nach der Filterung nur noch 100 übrigbleiben, hat die Anfrage dennoch 1 Million Umgebungswechsel durchführen müssen. Das kann gern mehr als 30 Sekunden dauern, je nach Maschine, auf der Sie arbeiten.

Stellen wir uns vor, wir könnten der Datenbank mitteilen, dass die Funktion v deterministisch sei. Was erspart uns das? Der Aufruf dieser Funktion wird nun zur Folge haben, dass die Werte durch die PL/SQL-Funktion nicht neu berechnet werden müssen, aber nicht, dass die Umgebungswechsel zwischen SQL und PL/SQL nicht mehr stattfinden. Denn die PL/SQL-Seite weiß, dass die Funktion deterministisch ist, SQL jedoch noch lange nicht. Die einzige Ersparnis liegt darin, dass nach dem Umgebungswechsel die PL/SQL-Funktion nicht berechnet wird, sondern das Ergebnis direkt liefern kann. Was also tun?

Die Lösung liegt darin, den Aufruf der Funktion in einer skalaren Unterabfrage zu schachteln, wie im folgenden Beispiel:

select *
  from emp
where deptno = (select v('P10_DEPTNO') from dual);

Das sieht auf den ersten Blick zwar komisch aus, hat aber eine Reihe von Vorteilen:

  • Weil eine skalare Unterabfrage von SQL wie eine Konstante behandelt wird, wird sie nur einmal für jeden unterschiedlichen Parameter der Funktion berechnet, in unserem Fall also genau einmal.
  • Es treten nach der ersten Berechnung keine weiteren Umgebungswechsel mehr auf.
  • Diese Optimierung funktioniert mit deterministischen Funktionen ebenso wie mit nicht deterministischen Funktionen

Gerade der letzte Punkt ist interessant: Die Denkweise ist, dass SQL eine Unterabfrage lesekonsistent zum Zeitpunkt der Abfrage einmal pro unterschiedlichem Parameter beantwortet und das Ergebnis der Abfrage nachfolgend als Konstante betrachtet. Daher ist es nicht erforderlich, dass die Funktion v deterministisch ist, wir verwenden das Ergebnis, das zum Zeitpunkt der Abfrage geliefert wurde. Da wir mit diesem Aufruf nun 1 Million Umgebungswechsel einsparen, sinkt die Ausführungszeit der Abfrage (nahezu) auf die gleichen Werte wie bei Verwendung einer Konstanten in der Abfrage, sie wird förmlich pulverisiert und sinkt unter den sinnvoll messbaren Bereich.
Ein erweiterter Lösungsansatz besteht darin, mehrere Funktionsaufrufe in einer faktorisierten Unterabfrage der eigentlichen Abfrage voranzustellen, wie im folgenden Beispiel:

  with params as(
      select v('P10_DEPTNO') deptno,
             v(('P10_JOB') job
        from dual)
select e.*
  from emp e natural join params p;

Das ist für mich auch einmal eine sinnvolle Anwendung des Natural Joins, der ja Spalten gleichen Namens zweier Tabellen in eine automatische Inner Join-Beziehung einbezieht. Da ich in der PARAMS-Abfrage über Aliase im Griff habe, wie ich die Parameter benennen möchte, kann ich mir eine explizite Join-Klausel ersparen.

Diese Lösung funktioniert immer und überall. Die faktorisierte Unterabfrage hat manchmal Probleme im Umfeld von APEX zur Folge, weil je nach Zusammenhang durch APEX dynamisches SQL konstruiert wird, in dem eine with-Klausel nicht erlaubt ist. Ist dies der Fall, können Sie faktorisierte Unterabfragen aber immer auch als Inner View in die from-Klausel schreiben.
Doch, ist dieser Aufwand nötig? Haben wir nicht von RESULT_CACHE, QUERY_RESULT_CACHE (für SQL-Abfragen) und in Version 12c von den Pragma UDF (User Defined Function) gelesen, das speziell für Funktionen da ist, die in SQL verwendet werden sollen? Was ist mit dem Pragma DETERMINISTIC? All diese Lösungen beziehen sich auf die jeweilige Umgebung, also auf PL/SQL oder auf SQL und können dort unnötige Neuberechnungen ersparen. Viele dieser Optionen sind zudem an die Enterprise Edition gebunden und haben keine Auswirkung auf SE oder XE. Zudem kann keine dieser Optimierungen unnötige Umgebungswechsel minimieren. Das kann nur die Kapselung in einer Unterabfrage, die zudem auch noch in jeder Datenbankversion und -edition funktioniert.

Grund genug für die erste Best Practice:

Funktionsaufrufe in SQL gehören in eine skalare Unterabfrage. Jedenfalls dann, wenn Sie nicht für jede Zeile ein unterschiedliches Ergebnis benötigen (dbms_random zum Beispiel): Diese Funktionsaufrufe müssen pro Zeile gerechnet werden und dürfen daher nicht in eine skalare Unterabfrage.

Zeilentrigger

Ein beliebtes Thema und für viele die «Einstiegsdroge» sind Trigger auf Tabellen, um zum Beispiel Primärschlüsselspalten zu berechnen, Großschreibung von Namen zu garantieren oder sonstige Datenprüfungen vorzunehmen. Hier ein einfacher Zeilentrigger auf die Tabelle EMP:

create or replace trigger trg_emp_briu
before insert or update on emp
for each row
referencing new as neu and old as alt
declare
  l_now date := sysdate;
begin
  :NEU.empno := coalesce(:NEW.empno, emp_seq.nextval);
  :NEU.ename := upper(:NEU.ename);
  if updating then
    :NEU.empno := :ALT.empno;
  end if;
end;
/

Zeilentrigger, die also für jede betroffene Zeile einer DML-Anweisung einmal ausgeführt werden, haben allerdings zwei Nachteile:

  • Sie haben für jede Zeile einen Umgebungswechsel zwischen SQL und PL/SQL zur Folge.
  • Sie verteilen die Geschäftslogik auf viele kleine Einzelelemente und erschweren in komplexen Anwendungen den Überblick über die Logik ungemein.

Warum ist ein Umgebungswechsel erforderlich? Der Triggerkörper ist ein anonymer PL/SQL-Block. Wann der Trigger ausgelöst wird und auf welche Tabelle er sich bezieht, wird in SQL definiert. Die Logik wird aber in PL/SQL implementiert und die Schnittstelle zwischen beiden ist der Aufruf eines anonymen PL/SQL-Blocks für jede Zeile, für die der Trigger ausgelöst wird. Sie können sich diese Schnittstelle auch aus anderem Blickwinkel klarmachen: In einer update-Anweisung haben Sie mittels der Pseudovariablen NEW Zugriff auf den neuen und mittels OLD Zugriff auf den alten Zustand der Zeile. Im Beispiel sehen Sie das, denn ich habe den Variablen beim Aufruf ein Alias NEU und ALT zugewiesen, um den Unterschied zu zeigen. Außerhalb des anonymen Blocks werden diese Pseudovariablen ohne, innerhalb des anonymen Blocks mit vorangestelltem Doppelpunkt notiert. Warum? Weil in SQL die Spaltenwerte in eine Speicherstruktur geladen und anschließend «von außen» an den PL/SQL-Block weitergegeben werden. Dies wird nicht als Parameter gemacht (ein anonymer Block kennt keine Parameter), sondern mittels einer Bindevariable, die also außerhalb des PL/SQL-Blocks definiert und innerhalb über die Doppelpunktnotation referenziert wird. Lassen Sie den Doppelpunkt weg, sähe PL/SQL nach, ob im anonymen Block eine Variable NEW definiert wurde, und das ist nicht der Fall. Dass es sich um einen anonymen Block handelt, erkennen Sie daran, dass eine lokale Variable nicht nach der Klausel AS/IS deklariert werden muss, sondern nach DECLARE.

Gut, aber was tun wir dagegen? Die Antwort ist einfach, die Umsetzung nicht: Lassen Sie die Zeilentrigger weg. Ab Version 12c haben wir mit der column identity-Klausel (endlich) einen Weg, Trigger zur Generierung neuer Primärschlüssel zu ersetzen. Das muss dann auch konsequent gemacht werden, wenn Sie auf Version 12c umsteigen und keine Rückwärtskompatibilität benötigen. Aber der Rest? Wie ersetzen Sie das? Wenn Sie die Zeilentrigger weglassen, verlagern Sie die Logik in ein PL/SQL-Package. Toll, werden Sie sagen, dann haben wir halt die Umgebungswechsel umgekehrt. Richtig und auch wieder nicht, denn wie ich im entsprechenden Abschnitt zeigen werde, bietet ein Package immer die Möglichkeit, solche Arbeiten in einer Mengenverarbeitungsroutine in PL/SQL zunächst aufzubereiten und dann mit wenigen Umgebungswechseln als Menge an SQL zu übergeben.

Doch ein Vorteil des Triggers ist, dass an ihm keiner vorbei kommt. Wissen Sie schon, welche Teile des Anwendungscodes auf die Tabelle schreiben? Und hier beginnt das Problem. Denn um zu verhindern, dass jeder wie er will in die Tabellen schreibt, müssen Sie das Schreibrecht auf die Tabellen entziehen und lediglich ein Ausführungsrecht an den PL/SQL-Prozeduren erlauben, die dann in die Tabellen schreiben. Wenn Sie nun sagen, „Danke, das war’s dann mit diesem Konzept, das kriegen wir nie durch“, kann ich das zwar gut nachvollziehen, es ist aber auch ein Indiz für eine zu starke Koppelung zwischen Datenbank und Anwendungscode. Sagen wir so: Es ist eine architektonische Entscheidung, wenn Sie die nicht treffen möchten, sind diese Umgebungswechsel eine unausweichliche Folge, die damit verbundenen Performanzprobleme nur äußerst schwer in den Griff zu bekommen.

Fassen wir also zusammen:

Zeilentrigger sind mit einer performanten Anwendung nicht in Einklang zu bringen, wenn sie in Bewegungstabellen verwendet werden. Je größer die Transaktionslast auf eine Tabelle, umso stärker die Begründung, auf Zeilentrigger zu verzichten und alternative Programmiermodelle zu etablieren.

Umgebungswechsel von PL/SQL nach SQL

Auch umgekehrt sind Umgebungswechsel von PL/SQL nach SQL möglich.

Einfaches Beispiel: Aufruf von DML-Anweisung in einem Loop

Einfach zu sehen ist dies in der folgenden Prozedur, die 10.000 Zeilen in eine Tabelle schreibt:

create or replace procedure sql_performance_test_4
as
begin
  for i in 1..10000 loop
    insert into test_table values(i);
  end loop;
  commit;
end sql_performance_test_4;
/

Diese Prozedur wird für jeden Schleifendurchlauf einen Umgebungswechsel machen, der auf die Performanzbremse tritt. Ärgerlich daran ist, dass die einfache Programmierung eigentlich suggeriert, dass man alles richtig gemacht hat, denn die Forderung „if it’s not a simple solution, it might be the wrong solution“ ist hier erfüllt. Als PL/SQL noch über eine eigene SQL-Maschine verfügte, war das nicht verkehrt, doch heute ist das keine gute Idee mehr. Um dieses Problem zu lösen, wird die mengenorientierte Programmierung eingesetzt, wie im folgenden Beispiel:

create or replace procedure sql_performance_test_5
as
  type value_table_type is table of pls_integer index by binary_integer;
  l_value_table value_table_type;
  l_iterations integer := 10000;
begin
  -- first, load values into value_table
  for i in 1..l_iterations loop
    l_value_table(i) := i;
  end loop; 
  -- now, bulk insert values into database
  forall idx in 1 .. l_iterations
    insert into test_table values(l_value_table(idx));    
  commit;
end sql_performance_test_5;
/

Zunächst wird eine lokale Datenstruktur mit Daten vollgepumpt (was einen erhöhten Speicherverbrauch, nicht aber Umgebungswechsel zur Folge hat) und erst, wenn dies geschehen ist, wird die PL/SQL-Tabelle value_table in einem Rutsch an SQL übergeben (das ist keine Schleife, sieht nur so ähnlich aus). Man kann, mit etwas mehr Aufwand, die Anzahl der Zeilen, die in einem Rutsch an SQL übergeben werden, steuern. Macht man das, wird die Speicherbelastung reduziert, allerdings werden mehr Umgebungswechsel durchgeführt. Wo ist hier also die richtige Balance?

Ich habe die beiden Prozeduren oben einmal gegeneinander ins Rennen geschickt. Gemessen habe ich dabei nicht nur die Zeit der Ausführung, sondern auch die Locks und Latches, die beide Prozeduren innerhalb der Datenbank allokieren. Gemessen wurde mit Tom Kytes RUN_STATS-Skript. Hier ist das beeindruckende Ergebnis:

scott@condes> begin
  2    Compare_Implementation('4', '5', 1000);
  3  end;
  4  /

Run1 ran in 307 hsecs
Run2 ran in 4 hsecs
run 1 ran in 7675% of the time        

Name                                  Run1        Run2        Diff
STAT...redo entries                 10,577         595      -9,982
LATCH.redo allocation               10,602         597     -10,005
STAT...execute count                10,084          42     -10,042
STAT...calls to get snapshot s      10,096          44     -10,052
STAT...db block gets                10,781         629     -10,152
STAT...session logical reads        11,213         849     -10,364
LATCH.shared pool                   11,055         499     -10,556
STAT...recursive calls              11,301         412     -10,889
STAT...db block changes             21,191       1,180     -20,011
LATCH.library cache pin             20,763         366     -20,397
LATCH.library cache                 21,296         658     -20,638
LATCH.cache buffers chains          54,218       3,433     -50,785
STAT...redo size                 2,313,324     206,372  -2,106,952        
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
120,409       6,072    -114,337  1,983.02%

PL/SQL procedure successfully completed.

Der Vergleich zeigt, dass die mengenorientierte Arbeitsweise um den Faktor 75 höher ist, die Anzahl der Locks und Latches sinkt um den Faktor 20 (und damit steigt die Skalierbarkeit der Anwendung um diesen Faktor).

Doch zurück zur Frage der richtigen Balance zwischen den beiden Antipoden Speicherverbrauch und Geschwindigkeit: Hier gibt es gute Nachrichten, denn bereits kleine Zeilenmengen haben erheblichen Einfluss auf die Geschwindigkeit. Die folgende Grafik zeigt den obigen Vergleich für die Bulkgrößen von 1 bis 128:

Wie Sie sehen sinkt die Ausführungszeit rapide und optimiert sich ab etwa 100 Zeilen nicht mehr relevant weiter. Daher ist diese Zeilenzahl ein guter Start für eigene Versuche. Wie wird das gemacht? Das folgende Listing zeigt es:

create or replace procedure sql_bulk_test(
  p_bulk_size in integer)
as
  type value_table_type is table of pls_integer index by binary_integer;
  l_value_table value_table_type;
  c_iterations constant integer := 100000;
  l_idx integer := 1;
  l_value integer;
  l_time integer;
begin
  l_time := dbms_utility.get_time;
  while l_idx <= ceil(c_iterations / p_bulk_size) + 1 loop
    -- first, load values into value_table
    l_value_table.delete;
    for i in 1..p_bulk_size loop
      l_value := ((l_idx - 1) * p_bulk_size) + i;
      if l_value <= c_iterations then
        l_value_table(i) := l_value;
      else
        exit;
      end if;
    end loop;
    -- now, bulk insert values into database
    forall idx in 1 .. l_value_table.count
      insert /*+ append */ into test_table values(l_value_table(idx));
    l_idx := l_idx + 1;
  end loop;
  l_time := dbms_utility.get_time - l_time;
  dbms_output.put_line('Bulk size: ' || p_bulk_size || ', Time: ' || l_time || 'hsec');
  execute immediate 'truncate table test_table';
end sql_bulk_test;
/

Wenn Sie den Test mit verschiedenen Größen selbst einmal nachvollziehen möchten, rufen Sie die Prozedur sql_bulk_test mit folgendem Code auf (denken Sie an serveroutput=on):

create or replace procedure show_bulk_size_efficiency
as
  l_bulk_size integer;
begin
  for i in 0..16 loop
    sql_bulk_test(power(2, i));
  end loop;
end show_bulk_size_efficiency;
/

begin
  show_bulk_size_efficiency;
end;
/

Auch hier die Zusammenfassung als Best Practice:
Datenbanken werden grundsätzlich mengenorientiert programmiert und nicht zeilenorientiert. Bei einer Bearbeitung von Daten Zeile für Zeile (Tom Kyte: slow by slow) führen die häufigen Umgebungswechsel zu schlechter Performance. Selbst höherer Programmieraufwand ist gerechtfertigt, wenn dadurch mengenorientiert gearbeitet werden kann.

Öffnen eines Cursors

Ebenso offensichtlich ist, dass von PL/SQL nach SQL gewechselt werden muss, wenn ein Cursor geöffnet wird. Auch hier gilt die Empfehlung, mit Mengenverarbeitung, hier allerdings mit der Anweisung bulk collect into, zu arbeiten. Für einfache Cursor existiert die automatische Optimierung durch den PL/SQL-Compiler, wenn der Parameter PLSQL_OPTIMIZE_LEVEL mindestens den Wert 2 hat. In diesem Fall werden einfache Cursor-For-Schleifen automatisch zu bulk collect into-Schleifen umgebaut. Die eingestellte Bulkgröße ist übrigens 100 Zeilen, wie unser Test von oben ja auch bereits suggeriert hat. Ein Beispiel hierfür möchte ich mir gern ersparen, es ist das Standardverfahren zum Arbeiten mit Datenmengen in PL/SQL. Interessant wird es allerdings, wenn unser Cursor nicht so ganz einfach ist. Stellen wir uns folgenden Klassiker vor: Wir benötigen einen Cursor, der über alle Abteilungen iteriert, um für eine Abteilung in einem zweiten Cursor alle Mitarbeiter zu bearbeiten. Der Code sieht also in etwa so aus:

declare
  cursor dept_cur is
    select deptno, dname, loc
      from dept;
  cursor emp_cur(p_deptno in number) is
    select empno, ename, job, sal
      from emp
     where deptno = p_deptno
     order by ename;
begin
  for dept in dept_cur loop
    dbms_output.put_line('Abteilung ' || dept.dname);
    for emp in emp_cur(dept.deptno) loop
      dbms_output.put_line(
        '. ' || emp.ename || ', ' || emp.job);
    end loop;
  end loop;
end;
/

Dieser Code hat gleich zwei massive Probleme: Zum einen, das ist aus dem bisher Gesagten klar, werden nun pro Schleifendurchlauf der äußeren Schleife Umgebungswechsel für das Öffnen jedes inneren Cursors notwendig. Das allein wäre nur lästig beziehungsweise langsam. Schlimmer ist jedoch, dass der innere Cursor nicht lesekonsistent zum äußeren Cursor ist: Ändern sich die Zeilen der Tabelle EMP während des Laufs des PL/SQL-Codes, sehen nachfolgende Durchläufe der inneren Schleife die geänderten Daten. Dies liegt daran, dass select-Anweisungen keine Sperren in der Datenbank hinterlassen und daher nicht den Regeln der Lesekonsistenz unterliegen. Ein Ausweg wäre ein rabiater: Vor der Ausführung des Codes müsste für die Session der Serialization Level auf SERIALIZABLE gestellt, anschließend explizit eine Transaktion angefordert werden. Wenn Sie die Anweisungen hierfür nicht kennen, dann wohl auch deshalb, weil dies bei Oracle so gut wie nie unbedingt nötig ist (Ich habe es in den vielen Jahren meiner Beschäftigung mit PL/SQL jedenfalls noch nie benötigt). Was aber ist der Ausweg aus dieser Situation? Wieder wird die Programmierung etwas umfänglicher, aber eben auch sowohl lesekonsistent als auch schnell. Wir benötigen einen Cursor-Ausdruck in der select-Anweisung:

declare
  cursor dept_cur is
    select dname,
           cursor(
             select ename, job
               from emp e
              where e.deptno = d.deptno
              order by e.ename) emp_cur
      from dept d;
  l_dname dept.dname%type;
  l_emp_cur sys_refcursor;
  l_ename emp.ename%type;
  l_job emp.job%type;
begin
  open dept_cur;
  loop
    fetch dept_cur into l_dname, l_emp_cur;
    exit when dept_cur%notfound;
    dbms_output.put_line('Abteilung ' || l_dname);
    loop
      fetch l_emp_cur into l_ename, l_job;
      exit when l_emp_cur%notfound;
      dbms_output.put_line(
        '. ' || l_ename || ', ' || l_job);
    end loop;
  end loop;
end;
/

Wir übernehmen explizit die Kontrolle über den Cursor und deklarieren im Cursor mit einem Cursorausdruck die n-Seite der Abfrage. Auf diese Weise wird die gesamte Ergebnismenge in einem Roundtrip zur SQL-Seite ermittelt und lesekonsistent zur Verfügung gestellt. Die Ergebnisse der Unterabfrage werden durch den Cursorausdruck in der Schleife in einen lokalen Cursor vom Typ SYS_REFCURSOR übernommen (andere Varianten wären möglich, aber komplexer) und in der äußeren Schleife in eine Variable dieses Typs übernommen. Über diesen Cursor L_EMP_CUR kann nun eine zweite Schleife iterieren und die Werte ausgeben.

Die Lehre aus der Geschichte:

Ein Cursor ist ein unvermeidbarer Umgebungswechsel zwischen SQL und PL/SQL und daher nicht schlimm. Wenn aber geschachtelte Cursoren verwendet werden, ist es erforderlich, die Daten in einem Roundtrip zu generieren und mit Cursorausdrücken auszuwerten, um nicht in Lesekonsistenz-Probleme und schlechte Performance zu schlittern.

Verdeckte Umgebungswechsel

Etwas weniger offensichtlich wird es, wenn Umgebungswechsel nicht offensichtlich sind. Wie auch schon in den gezeigten Beispielen, liegt die Gefahr darin, dass die Programmierung verdeckter Umgebungswechsel mit elegant erscheinendem Code möglich ist und daher für richtig erachtet wird.

Verdeckte Umgebungswechsel in SQL

Mir fällt hier vor allem die Instanzierung von Objekttypen beziehungsweise die Verwendung von member-Funktionen dieser Typen ein. Haben Sie eigene Objekttypen erstellt und hierfür Konstruktor- oder member-Funktionen angelegt, wird der Gebrauch dieser Funktionen zu Umgebungswechseln führen. Das ist schwer zu verhindern, am ehesten dadurch, dass Sie die Objekte in PL/SQL erstellen und mit bulk-Operationen an SQL übergeben. Ob der Aufwand hierfür gerechtfertigt ist, ist allerdings von Fall zu Fall zu entscheiden.

Verdeckte Umgebungswechsel in PL/SQL

Etwas hinterhältiger verhält sich PL/SQL. Zum einen gibt es Funktionen, die eine Fallback-Lösung auf eine SQL-Abfrage besitzen und daher potenziell für Umgebungswechsel sorgen können. Sehen Sie sich als Beispiel einmal die Implementierung der Funktion sysdate im Package STANDARD an:

function pessdt return DATE;
    pragma interface (c,pessdt);

  -- Bug 1287775: back to calling ICD.
  -- Special: if the ICD raises ICD_UNABLE_TO_COMPUTE, that means we should do
  -- the old 'SELECT SYSDATE FROM DUAL;' thing.  This allows us to do the
  -- SELECT from PL/SQL rather than having to do it from C (within the ICD.)
  function sysdate return date is
    d date;
  begin
    d := pessdt;
    return d;
  exception
    when ICD_UNABLE_TO_COMPUTE then
      select sysdate into d from sys.dual;
      return d;
  end;

Die Funktion ist als externe Funktion PESSDT in C implementiert. Sollte dies aber aus irgendeinem Grund nicht gehen (exception ICD_UNABLE_TO_COMPUTE), wird «the old 'SELECT SYSDATE FROM DUAL'-thing» getan, d. h. ein Umgebungswechsel zu SQL durchgeführt. Nun gut, das ist vielleicht etwas exotisch, aber es spricht dafür, auch sysdate nicht in einer Schleife zu verwenden, sondern lieber eine lokale Variable

l_now date := sysdate; 

zu vereinbaren und in der Methode zu benutzen. Auf diese Weise ist auch sichergestellt, dass alle Iterationen einer Schleife zur gleichen Zeit ausgeführt werden (so dies denn nicht ausdrücklich vermieden werden soll).

Wichtiger ist sicher noch das Beispiel der Funktion USER, die viele für eine Pseudospalte halten. Das ist nicht so, sondern eine select-Abfrage gegen die Datenbank. Daher verbietet sich der Einsatz von USER in Schleifen, zumal es doch eher unwahrscheinlich ist, dass sich der angemeldete Datenbankbenutzer während eines laufenden PL/SQL-Blocks ändert. Sehen Sie sich einmal folgenden Ausriss aus einem TKPROF-Bericht an:

********************************************************************************
SQL ID: 1v717nvrhgbn9 Plan Hash: 1388734953

SELECT USER
FROM
SYS.DUAL

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute  18110      0.18       0.31          0          0          0           0
Fetch    18110      0.04       0.13          0          0          0       18110
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    36222      0.23       0.44          0          0          0       18110

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 58     (recursive depth: 2)
Number of plan statistics captured: 2

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  FAST DUAL  (cr=0 pr=0 pw=0 time=4 us cost=2 size=0 card=1)
********************************************************************************

Das deutet doch sehr darauf hin, dass in einer View oder einer Abfrage oder Schleife, die auf USER referenziert, eine Optimierung möglich sein müsste...

Zusammenfassung

Umgebungswechsel sind ein ganz wesentliches Problem der Programmierung in PL/SQL und der Abfragegestaltung in SQL. Daher sollten Sie einen Blick für mögliche Wechselszenarien entwickeln und sich fragen, ob eine eingeschlagene Programmierstrategie nicht eventuell viele Umgebungswechsel zur Folge hat. Helfen können beim Detektieren solcher Umgebungswechsel natürlich auch die bekannten Hilfsmittel TKPROF oder der hierarchische Profiler, weil diese Werkzeuge relativ genau anzeigen, was im Code passiert.

Die in diesem Artikel zusammengetragenen Best Practices sind natürlich nicht umfassend, nicht einmal bezüglich der Umgebungswechsel, aber stellen doch einen Teil der Strategien dar, die ein PL/SQL-Entwickler im Hinterkopf haben sollte, um grundsätzlich performanten und skalierbaren Code zu schreiben. Performance-Tuning beginnt ja nicht erst, wenn ein Problem existiert, sondern manifestiert sich zunächst einmal darin, dass die groben und bekannten Fehler von vornherein nicht gemacht werden. Dies gilt insbesondere für Code, der «nicht performanzkritisch» ist und von daher ja auch nachlässiger programmiert werden könnte. Erfahrungsgemäß verbleibt dieser Code lange im Code und zwar genau dort, wo er eben doch erheblich im Wege steht.

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
botMessage_toctoc_comments_9210