Logo Oracle Deutschland   Application Express Community
Schnellere Abfragen - schnellere APEX-Seiten: mit Oracle11g und dem Result Cache

Der heutige Tipp beschäftigt sich mit dem ab Datenbankversion Oracle11g enthaltenen SQL und PL/SQL Result Cache. Dies ist ein eigener, 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 und an den Statistiken, die Sie sich in SQL*Plus recht einfach mit set autotrace on anzeigen lassen können.

:

Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       4027  bytes sent via SQL*Net to client
        497  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         71  rows processed

Die Tatsache, dass keine Datenbankblöcke verarbeitet wurden (0 consistent gets) und keine internen System-Calls nötig waren (0 recursive calls) lässt deutlich erkennen, dass das Ergebnis hier aus dem Result Cache kam.

Nutzungsvarianten

Die vorgestellte Variante mit dem Hint ist sicherlich die einfachste und flexibelste Möglichkeit, den Result Cache zu nutzen. Aber die Datenbank stellt noch weitere Möglichkeiten bereit.

  • Der Session-Parameter RESULT_CACHE_MODE kann von MANUAL auf FORCE umgestellt werden. Das bedeutet, dass der Hint automatisch zu jeder SELECT-Anfrage hinzugefügt wird. Bei Abfragen mit Unterabfragen (Sub-Selects) wird der Hint nur der äußeren Abfrage hinzugefügt. Das hat zwar den Vorteil, dass man keine Hints mehr selbst setzen muss - allerdings wird der Result Cache dann immer verwendet - auch für solche Abfragen verwendet, für die es (wegen sich häufig ändernder Tabelleninhalte) keinen Sinn macht. Das kann dazu führen, dass wichtige Inhalte durch unwichtige aus dem Cache verdrängt werden - auch der Cache hat ein Größenlimit. Insofern sollte diese Variante mit Vorsicht eingesetzt werden.
  • Seit der Version 11.2 kann ein Result Cache-Attribut auf Tabellenebene gesetzt werden. Setzt man das Attribut auf FORCE, so wird für alle Abfragen, bei denen besagte Tabelle verwendet wird, der Result Cache genutzt - es sei denn, man setzt in der Abfrage den Hint NO_RESULT_CACHE. Nun werden in SQL-Abfragen aber oft mehrere Tabellen verwendet: Damit die FORCE-Klausel wirksam wird, muss sie für alle beteiligten Tabellen gesetzt sein. Das Tabellenattribut ist sinnvoll vor allem für Lookup- oder andere Tabellen, deren Inhalte eher statischer Natur sind.
    alter table {tablename} result_cache (mode force);
    
    alter table {tablename} result_cache (mode default);
    
    Die Klausel kann sowohl beim CREATE TABLE als auch beim ALTER TABLE verwendet werden.

Der Result Cache kann übrigens sehr hilfreich bei der Verwendung von entfernten Tabellen via Database Links sein. Gerade hier kann das Einsparen des Roundtrips zum entfernten Server einen großen Performanceeffekt bringen.

Für eine entfernte Tabelle kann die Datenbank natürlich nicht mehr automatisch feststellen, ob die Inhalte sich mittlerweile geändert haben und die Cache-Inhalte noch gültig sind. Daher ist der Datenbankparameter RESULT_CACHE_REMOTE_EXPIRATION wichtig, der in Minuten gesetzt wird. Wird der Parameter auf 0 (Null) gesetzt, dann werden Abfragen nicht gecacht, wenn eine entfernte Tabelle beteiligt ist. Ansonsten legt der Parameter die Anzahl Minuten fest, die ein solches Ergebnis gültig bleibt. Setzt man den Parameter, besteht also durchaus die Möglichkeit, dass veraltete Ergebnisse zurückgeliefert werden - aber in manchen Fällen kann das vielleicht zugunsten besserer Performance in Kauf genommen werden.

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 wurde oder alle beteiligten Tabellen das Attribut RESULT_CACHE FORCE tragen.
  • 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 scope = both | spfile

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

alter system set result_cache_max_size = 20M scope = both | spfile

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

alter system set result_cache_max_result = 50 scope = both | spfile

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. Die Dictionary View V$RESULT_CACHE_OBJECTS zeigt die Inhalte des Result Cache an. Hier kann man prüfen, ob eine bestimmte SQL-Abfrage überhaupt gecacht ist - und ob das Ergebnis noch gültig ist.

SQL> select id, type, status, name from v$result_cache_objects 
  2  where lower(name) like '%prod_name%';

        ID TYPE       STATUS    NAME
---------- ---------- --------- --------------------------------------------------
      1537 Result     Published select /*+ result_cache */ * from (
                                select
                                  p.prod_name,
                                  count(sqrt(s.cust_id) * sqrt(cust_id)) anzahl,
                                  sum(s.amount_sol

Die View V$RESULT_CACHE_DEPENDENCY hilft dabei, die Tabellen herauszufinden, von denen der Cache-Eintrag abhängig ist - wichtig ist nun der Inhalt der Spalte ID von V$RESULT_CACHE_OBJECTS.

SQL> select * from V$result_cache_dependency where result_id = 1537;

 RESULT_ID  DEPEND_ID  OBJECT_NO
---------- ---------- ----------
      1537       1344      68146
      1537        713      68087

2 Zeilen ausgewählt.

Mit den gefundenen DEPEND_IDs kann man nun (wieder aus V$RESULT_CACHE_OBJECTS) die Namen der Tabellen bzw. Datenbankobjekte herausfinden.

SQL> select id, type, status, name from v$result_cache_objects where id in (1344,713);

        ID TYPE       STATUS    NAME
---------- ---------- --------- --------------------------------------------------
      1344 Dependency Published SH.PRODUCTS
       713 Dependency Published SH.SALES

2 Zeilen ausgewählt.

Die View V$RESULT_CACHE_STATISTICS enthält weitere Informationen über das Verhalten des Cache.

SQL> select * from v$result_cache_statistics;

        ID NAME                                    VALUE
---------- --------------------------------------- -----------
         1 Block Size (Bytes)                      1024
         2 Block Count Maximum                     10240
         3 Block Count Current                     1984
         4 Result Size Maximum (Blocks)            512
         5 Create Count Success                    36242
         6 Create Count Failure                    0
         7 Find Count                              239023
         8 Invalidation Count                      30401
         9 Delete Count Invalid                    30592
        10 Delete Count Valid                      3805
        11 Hash Chain Length                       0-1
        12 Find Copy Count                         234506

12 Zeilen ausgewählt.

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 allerdings ohne Wirkung. Das gleiche gilt für die Standard Edition und OracleXE. Sobald die Anwendung auf einer Oracle11g-Datenbank und der Enterprise Edition läuft, wird der Result Cache wirksam.

PL/SQL-Result Cache

Für den PL/SQL-Kontext gibt es einen eigenen Result Cache, der auch anders verwendet wird. 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
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. Der PL/SQL Result Cache ist nun nicht für die Verwendung der Funktion in SQL-Abfragen wirksam, sondern für das Aufrufen aus einem anderen PL/SQL-Objekt (Prozedur, Package) heraus, also für den PL/SQL-Kontext.

Der PL/SQL-Result Cache sollte nur für PL/SQL-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 die 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.

Mehr Informationen zum SQL und PL/SQL Result Cache finden Sie in der Oracle-Dokumentation - dort im Performance Tuning Guide und in der PL/SQL Language Reference.

Zurück zur Community-Seite