Schnellere Abfragen - schnellere Reports mit Oracle11g und dem Result Cache

Der heutige Tipp beschäftigt sich mit der jüngsten Datenbankversion Oracle11g und dem darin enthaltenen SQL und PL/SQL Result Cache. Dies ist ein neuer, speziell für Ergebnisse aus SQL-Abfragen oder PL/SQL-Funktionen vorgesehener Cache. Die Datenbank verwaltet den Cache völlig selbstständig - und stellt dabei sicher, dass niemals veraltete Ergebnisse ausgegeben werden. Als Entwickler muss man nur entscheiden, ob man ihn nutzen möchte oder nicht. Der Result Cache ist Bestandteil der Enterprise Edition der Oracle-Datenbank.

Result Cache: Erste Schritte

Ausprobieren ist ganz einfach. Als Beispiel nehmen wir eine etwas längerlaufende Abfrage (damit man den Cache auch bemerkt), basierend auf dem in den meisten Datenbanken vorhandenen Beispielschema SH: 

select /*+ result_cache*/
  p.prod_name, 
  count(sqrt(s.cust_id) * sqrt(cust_id)) anzahl, 
  sum(s.amount_sold) summe ,
  max(s.amount_sold) max_sale
from sh.sales s join sh.products p using (prod_id)
group by prod_name

Damit die Abfrage funktioniert, benötigen Sie natürlich SELECT-Privilegien auf den Tabellen SH.SALES und SH.PRODUCTS. Wenn das Schema SH bei Ihnen nicht vorhanden ist, können Sie eine beliebige andere SQL-Abfrage nutzen; achten Sie für diesen Test lediglich darauf, dass es zur Ausführung etwas mehr Zeit benötigt.

Der Result Cache wird durch den Hint /*+ RESULT_CACHE*/ aktiviert. Achten Sie darauf, den Hint in der SQL-Abfrage richtig zu schreiben, bei Fehlern wird er schlicht ignoriert. Um den Result Cache zu testen, erzeugen Sie also eine Anwendungsseite und darauf eine Berichtsregion. Damit Sie den Effekt des Cache sehen können, setzen Sie den Substitution String #TIMING# entweder in den Regions-Header oder Regions-Footer.

Schauen Sie, nachdem Sie den Bericht erstellt haben, nochmals in die SQL-Abfrage in der Regionsquelle hinein. Speziell wenn Sie den Bericht im Rahmen einer neuen Anwendung erstellen, verändert APEX die Berichtsabfrage nochmals. Stellen Sie sicher, dass der Hint /*+RESULT_CACHE*/ vorhanden ist und im äußeren SELECT verwendet wird (Abbildung 1).

Hint "RESULT_CACHE" in Berichtsabfrage nutzen

Abbildung 1: Hint "RESULT_CACHE" in Berichtsabfrage nutzen

Nun können Sie die Seite starten. Bei erstmaliger Ausführung sollte die Darstellung der Seite ein wenig Zeit benötigen und in etwa wie in Abbildung 2 aussehen.

Erstmalige Ausführung der Abfrage mit Hint "RESULT_CACHE"

Abbildung 2: Erstmalige Ausführung der Abfrage mit Hint "RESULT_CACHE"

Führen Sie die Seite (bspw. mit [F5]) direkt nochmals aus. Beim zweiten Ausführen der Abfrage geht alles viel schneller (Abbildung 3).

Wiederholte Ausführung der Abfrage mit Hint "RESULT_CACHE"

Abbildung 3: Wiederholte Ausführung der Abfrage mit Hint "RESULT_CACHE"

Im letzteren Fall wird die SQL-Abfrage nicht mehr ausgeführt; vielmehr kommt das Ergebnis direkt aus dem Result Cache. Der Cache wird solange verwendet, bis die der Anfrage zugrundeliegenden Datenbankobjekte (Tabellen, Views, Funktionen, PL/SQL), sich ändern. Das können Sie auch einfach ausprobieren, in dem Sie (bspw. mit dem SQL Workshop) einmal folgendes SQL absetzen: 

update sh.products set prod_id = prod_id where rownum<2

Wenn Sie die Anwendungsseite nun nochmals starten, benötigt der Bericht wieder die ursprüngliche Zeit, da das Ergebnis der SQL-Abfrage nicht aus dem Cache geholt werden kann - der ist ungültig geworden.

Beim Tuning erkennen Sie die Nutzung des Result Cache übrigens auch im Ausführungsplan; Abbildung 4 zeigt die Darstellung des Ausführungsplans bei Nutzung des Result Cache; die Spalte LAST_CR_BUFFER_GETS zeigt deutlich, dass keine Datenbankblöcke gelesen wurden.

Ausführungsplan für eine SQL-Abfrage mit Result Cache

Abbildung 4: Ausführungsplan für eine SQL-Abfrage mit Result Cache

Administration des Result Cache

Technisch ist der Result Cache ein reservierter Bereich der Datenbank-SGA (der Hauptspeicher, welchen die Oracle-Datenbank auf dem Server verwendet). Der Datenbankparameter RESULT_CACHE_MAX_SIZE gibt an, wieviel Hauptspeicher die Datenbank aktuell für den Result Cache verwendet. Das folgende Codebeispiel zeigt an, wie sich der DBA über den Result Cache informieren kann. 

SQL> select name, value from v$parameter where name like 'result_cache%';

NAME                           VALUE
------------------------------ ------------------------------
result_cache_mode              MANUAL
result_cache_max_size          1572864
result_cache_max_result        5
result_cache_remote_expiration 0

4 Zeilen ausgewählt.

Dies bedeutet ...

  • Der Result Cache wird nur genutzt, wenn der Optimizer-Hint /*+ RESULT_CACHE*/ in der SQL-Abfrage verwendet wird. Die Alternative zu MANUAL wäre FORCE, was bedeuten würde, dass Oracle den Hint quasi automatisch in jede SQL-Abfrage einbaut. Dies ist normalerweise (und ganz speziell für APEX) nicht zu empfehlen.
  • Es werden 1,5 MB für den Result Cache verwendet
  • Ein Ergebnis (Result) einer SQL-Abfrage darf maximal fünf Prozent des Cache belegen.
  • Wenn gecachte Ergebnisse auf entfernten Tabellen oder Views beruhen, gibt der Parameter RESULT_CACHE_REMOTE_EXPIRATION an, nach wievielen Minuten das entfernte Objekt als ungültig angesehen wird.

Um das Verhalten des Result Cache zu ändern, muss der DBA diese Parameter entsprechend ändern. So schaltet das folgende Kommando den Result Cache ab. Die Hints in den SQL-Abfragen sind danach ohne Wirkung. 

alter system set result_cache_max_size = 0

Das nun folgende Kommando reserviert 20MB für den Result Cache 

alter system set result_cache_max_size = 20M

Mit diesem Kommando kann eine einzelne SQL-Abfrage bis zu 50% des Result Cache belegen: 

alter system set result_cache_max_result = 50

Darüber hinaus steht das PL/SQL Paket DBMS_RESULT_CACHE zur weiteren Verwaltung des Result Cache zur Verfügung. Unter anderem enthält des die Prozedur MEMORY_REPORT , welche genaue Information über die Nutzung des Result Cache gibt.

Wann sollte man den Result Cache nutzen ... und wann nicht?

Bevor man nun beginnt, den Hint /*+ RESULT_CACHE*/ in seine Abfragen einzubauen, sollte man einige Dinge überlegen ...

  • Die Datenbank invalidiert den Result Cache automatisch, wenn sich am zugrundeliegenden Datenbankobjekt (also der Tabelle) etwas ändert. Daraus leitet sich bereits die erste Regel ab: Wenn die der Abfrage zugrundeliegenden Daten sich ständig ändern, die SQL-Abfrage also stets ein anderes Ergebnis zurückliefert, bringt der Result Cache keine Verbesserung.

    Umgekehrt ist der Result Cache um so besser geeignet, je seltener sich die zugrundeliegenden Daten ändern.

  • Speziell für APEX gilt: Wenn sessionbezogene Variablen (speziell die Session-ID) in die Abfrage aufgenommen werden, ändert sich das Ergebnis mit jeder APEX-Session. Hier muss man nachdenken: Nur wenn die Abfrage innerhalb der gleichen Session mit den gleichen Parametern mehrfach ausgeführt, die Berichtsseite also häufig abgerufen wird, ist es sinnvoll, den Cache zu aktivieren.
  • Die Abfrage sollte schließlich zeitintensiv sein; wenn sie ohnehin nur sehr kurze Ausführungszeiten hat, ist der Result Cache gar nicht nötig.

Die Hints können natürlich auch in Oracle9i oder Oracle10g gesetzt werden, sie bleiben nur ohne Wirkung. Sobald die Anwendung auf einer Oracle11g-Datenbank läuft, werden die entsprechenden APEX-Seitenabrufe eben schneller.

PL/SQL-Result Cache für eigene Funktionen

Sie können den Result Cache auch im PL/SQL-Kontext nutzen und für selbstgeschriebene Funktionen aktivieren. Ein Beispiel finden Sie in diesem Code: 

create or replace function get_emp_salary (
  p_empno in EMP.EMPNO%TYPE
) return EMP.SAL%TYPE 
RESULT_CACHE RELIES_ON (emp)
is
  v_sal EMP.SAL%TYPE;
begin
  select sal into v_sal from emp where empno=p_empno;
  return v_sal;
exception
  when NO_DATA_FOUND then return null;
  when TOO_MANY_ROWS then return null;
end;

Durch die RESULT_CACHE-Klausel wird das Caching für diese Funktion generell aktiviert. Für PL/SQL-Funktionen wird der Cache also bei Deklaration und nicht bei Ausführung eingeschaltet. Da die Datenbank die Abhängigkeiten der Funktion von Tabellen oder anderen Datenbankobjekten nicht automatisch erkennen kann, müssen diese mit der RELIES_ON-Klausel deklariert werden. Das obige Beispiel hängt also von der Tabelle EMP ab.

Die RELIES_ON-Klausel ist extrem wichtig - lässt man weg, wird der Cache für diese Funktion überhaupt nicht invalidiert; die Funktion würde also also stets das gecachte Ergebnis ausliefern.

Der PL/SQL-Result Cache sollte nur bei Funktionen eingesetzt werden, deren gleiche Eingabeparameter auch zum gleichen Ergebnis führen. Sobald das Funktionsergebnis vom Datum (SYSDATE, SYSTIMESTAMP) oder zufällig generierten Werten (DBMS_RANDOM) abhängt, sollten Sie den Result Cache für diese Funktion nicht verwenden.

Schließlich darf Ihre Funktion keine PL/SQL OUT-Parameter verwenden; für solche Funktionen ist die RESULT_CACHE-Klausel ebenso nicht erlaubt wie für Prozeduren - Sie erhalten eine Fehlermeldung.

PL/SQL Result Cache und ältere Datenbankversionen

Speziell beim PL/SQL-Result Cache stellt sich nun die Frage, wie man die Funktion so entwickelt, dass der gleiche Code sowohl in Oracle11g als auch in älteren Versionen lauffähig ist. Bein SQL-Result Cache war das kein Problem: Wenn die Datenbank einen Hint in einer SQL-Abfrage nicht kennt, ignoriert sie ihn.

Der PL/SQL-Compiler ist nicht so nachsichtig; wenn obiger Code in eine 10g-Datenbank eingespielt wird, erhalten Sie eine Fehlermeldung: 

LINE/COL ERROR
-------- -------------------------------------------------------------------------------------
4/1      PLS-00103: Encountered the symbol "RESULT_CACHE" when expecting one of the following:
         ; is authid as cluster order using external character

Hier hilft das bedingte Kompilieren, welches ursprünglich mit Oracle10g eingeführt und dann auf Oracle9i "backgeported" wurde, also ab Version 9.2.0.8 verfügbar ist. Der folgende Code erzeugt eine Funktion, die in Oracle11g den PL/SQL-Result Cache nutzt und in älteren Versionen nicht. Der gleiche Code kann jedoch in alle Datenbankversionen eingespielt werden. 

create or replace function get_emp_salary (
  p_empno in EMP.EMPNO%TYPE
) return EMP.SAL%TYPE 
  $IF DBMS_DB_VERSION.VER_LE_10_2 $THEN  
  $ELSE                                  
    RESULT_CACHE RELIES_ON (emp)         
  $END                                   
is
  v_sal EMP.SAL%TYPE;
begin
  select sal into v_sal from emp where empno=p_empno;
  return v_sal;
exception
  when NO_DATA_FOUND then return null;
  when TOO_MANY_ROWS then return null;
end;

Mehr Informationen zum SQL und PL/SQL Result Cache finden Sie in der Oracle-Dokumentation.

Zurück zur Community-Seite