Der Result Cache in 11g: Nutzung, Administration und Monitoring
von Ulrike Schwinn, ORACLE Deutschland GmbH

Seit Oracle Database 11g gibt es einen neuen Cache-Bereich im Shared Pool, der speziell für Ergebnismengen reserviert ist, den sogenannten Result Cache. Dieser Cache steht automatisch mit jeder 11g Enterprise Edition zur Verfügung und kann ohne zusätzliche Installation eingesetzt werden.
Ergebnisse von Statements, die den Result Cache nutzen, werden bei der Ausführung im Result Cache abgelegt und bei den Folgeausführungen wiederverwendet. Die Ausführungszeit reduziert sich dabei drastisch. Um konsistente Abfragen zu garantieren, wird bei Änderungen an den Tabellenwerten der Cache automatisch invalidiert. Statements, die sich häufig wiederholen und deren Ergebnisse nur mit aufwändigen Mitteln d.h. mit hohem Ressourcenaufwand und Ausführungszeiten zu realisieren sind, können nun einfach vom Result Cache profitieren.

Der Einsatz ist besonders in folgenden Fällen von Vorteil:

  • Langlaufenden und rechenintensive SQL-Abfragen
  • Rechenintensive PL/SQL-Funktionen
  • Vorhersehbare SQL-Abfragen
  • Gleichbleibende deterministische Ergebnismengen
  • Kleine Ergebnismengen bzw. ausreichendes Memory
  • Geringe DML-Aktivität auf den zugrundeliegenden Tabellen

    • Die einfache Nutzung und das Monitoring werden in folgenden Abschnitten an Beispielen illustriert.

    Wie kann nun dieser Cache eingeschaltet bzw. aktiviert werden?
    Folgende vier Initialisierungsparameter in 11g sind speziell zur Administration und Nutzung des Caches neu eingeführt worden. Dabei sind die Parameter gleich nach der Installation mit Defaultwerten belegt. Somit kann der Cache sofort verwendet werden. Die Defaultwerte lassen sich dabei je nach Parameter mit dem ALTER SESSION- oder dem ALTER SYSTEM- Befehl verändern.

    Folgende Abfrage gibt Auskunft über die Parameter und die mögliche Wertebelegung: 
    SQL> SELECT name, value, description FROM v$parameter WHERE name LIKE 'result_cache%';

NAME                           VALUE
------------------------------ --------------------
DESCRIPTION
------------------------------------------------------------
result_cache_mode              MANUAL
result cache operator usage mode

result_cache_max_size          104857600
maximum amount of memory to be used by the cache

result_cache_max_result        5
maximum result size as percent of cache size

result_cache_remote_expiration 0
maximum life time (min) for any result using a remote object
    Mit dem Parameter RESULT_CACHE_MAX_SIZE wird die Gesamtgrösse des reservierten Bereichs für den Result Cache im Shared Pool festgelegt. Dabei wird der Speicher für Ergebnisse von SQL-Abfragen UND auch für Ergebnisse von PL/SQL-Funktionen reserviert. Wird dieser Parameter auf den Wert 0 gesetzt, ist der Result Cache ausgeschaltet. Der Defaultwert ist in der Regel allerdings ungleich 0.

    RESULT_CACHE_MAX_RESULT legt den prozentualen Anteil am gesamten Result Cache für die einzelnen Ergebnisse fest. Beide Parameter benötigen das ALTER SYSTEM- Privileg.

    Wird auf Remote Objekte zugegriffen, kann mit dem Parameter RESULT_CACHE_REMOTE_EXPIRATION festgelegt werden, wie lange das Resultat in Minuten im Cache verbleibt. Dieser Parameter ist mit ALTER SESSION oder ALTER SYSTEM einstellbar.

    Der Initialisierungsparameter RESULT_CACHE_MODE ist für die Nutzung des Caches reserviert und mit dem Privileg ALTER SESSION verändert werden. Dieser kann die Werte MANUAL (Default) oder FORCE besitzen und bestimmt die Art der Nutzung dieses Cachebereichs für SQL Abfragen. MANUAL bedeutet dabei, dass der Hint /*+ RESULT_CACHE */ in der SQL-Abfrage eingetragen werden muss, damit der Result Cache genutzt wird. FORCE hingegen erzwingt automatisch die Verwendung in jedem SELECT- Statement auf der Root-SELECT-Ebene.

    Folgendes Beispiel zeigt die Anwendung unter Verwendung des Wertes MANUAL. 
    SQL> SELECT /*+ result_cache */ COUNT(*) total_count, SUM(salary) total_sal
  2  FROM hr.bigemp group by department_id ORDER BY department_id;
...
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    11 |    55 |  2229   (2)| 00:00:34 |
|   1 |  RESULT CACHE       | 91myw5c1bud0mcn64g3d0ykdhm |       |       |            |          |
|   2 |   SORT GROUP BY     |                            |    11 |    55 |  2229   (2)| 00:00:34 |
|   3 |    TABLE ACCESS FULL| BIGEMP                     |   876K|  4280K|  2201   (1)| 00:00:34 |
--------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=2; dependencies=(HR.BIGEMP); name="SELECT /*+ result_cache */
    COUNT(*)    total_count,
    SUM(salary) total_sal
  FROM bigemp
group by department_id
ORDER "

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       9081  consistent gets
       9077  physical reads
          0  redo size
        686  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    Die neue Operation "RESULT CACHE" im Ausführungsplan zeigt an, dass der Result Cache erzeugt bzw. bei wiederholter Ausführung genutzt wird.

    Wiederholen wir die Ausführung und stellen zudem sicher, dass die darunterliegende Tabelle nicht geändert wird, werden wir festellen, dass sich die Statistiken im folgenden Beispiel geändert haben. Der Wert von "consistent gets" hat sich zum Beispiel auf 0 reduziert. Der Cache ist nun im Einsatz, und die Ausführung der Abfrage ist schneller als beim ersten Mal. 
    SQL> SELECT /*+ result_cache */ COUNT(*) total_count, SUM(salary) total_sal
     FROM hr.bigemp group by department_id ORDER BY department_id;
...
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        686  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed
    Wird die Tabelle BIGEMP allerdings mit DML-Operationen verändert, wird der Cache automatisch invalidiert und beim nächsten Mal erneut geladen.

    Im nächsten Beispiel wird der Parameter RESULT_CACHE_MODE auf FORCE gesetzt. Wie vorher schon angedeutet, wird nun automatisch der Result Cache für das Gesamtergebnis der jeweiligen Abfragen zur Verfügung gestellt. Allerdings gilt dies nicht für Abfragen auf Objekte im Schema SYS und SYSTEM. 
    SQL> ALTER SESSION SET RESULT_CACHE_MODE=force;
Session altered.

SQL> SELECT a.department_id "Department",
2           a.num_emp/b.total_count "%_Employees",
3           a.sal_sum/b.total_sal   "%_Salary"
4    FROM (SELECT department_id,
5                 COUNT(*) num_emp,
6                 SUM(salary) sal_sum
7    FROM bigemp GROUP BY department_id) a, 
8         (SELECT COUNT(*) total_count,
9                 SUM(salary) total_sal
10   FROM bigemp ) b
11   ORDER BY a.department_id;
...
-----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                            |    11 |   715 |  4428   (2)| 00:01:07 |
|   1 |  RESULT CACHE          | 5d9jwxyxqwzbabj272uvuy2n61 |       |       |            |          |
|   2 |   SORT ORDER BY        |                            |    11 |   715 |  4428   (2)| 00:01:07 |
|   3 |    NESTED LOOPS        |                            |    11 |   715 |  4428   (2)| 00:01:07 |
|   4 |     VIEW               |                            |     1 |    26 |  2199   (1)| 00:00:34 |
|   5 |      SORT AGGREGATE    |                            |     1 |     3 |            |          |
|   6 |       TABLE ACCESS FULL| BIGEMP                     |   876K|  2568K|  2199   (1)| 00:00:34 |
|   7 |     VIEW               |                            |    11 |   429 |  2229   (2)| 00:00:34 |
|   8 |      SORT GROUP BY     |                            |    11 |    55 |  2229   (2)| 00:00:34 |
|   9 |       TABLE ACCESS FULL| BIGEMP                     |   876K|  4280K|  2201   (1)| 00:00:34 |
-----------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=3; dependencies=(HR.BIGEMP); name="SELECT
   a.department_id         "Department",
   a.num_emp/b.total_count "%_Employees",
   a.sal_sum/b.total_sal   "%_Salary"
"
    Soll dieses Result Cache-Verhalten allerdings für einzelne SELECT-Statements ausgeschaltet werden, kann der Hint /*+ NO_RESULT_CACHE */ verwendet werden.

    Weitere Anwendungsbeispiele auch zur Verwendung mit PL/SQL Funktionen finden sich auf der Oracle Application Express Community Seite unter
    http://www.oracle.com/global/de/community/tipps/resultcache/index.html

    Um einen Überblick über die gesamte Result Cache Nutzung zu erhalten und genaues Monitoring zu gewährleisten, sind zusätzlich weitere neue V$-Tabellen und ein neues Package eingeführt worden. Das neue Package DBMS_RESULT_CACHE hilft z.B. dabei einfache administrative Aufgaben rund um den Result Cache durchzuführen. So kann z.B mit DBMS_RESULT_CACHE.FLUSH() der Cache bereinigt (flush) werden. DBMS_RESULT_CACHE.MEMORY_REPORT() gibt einen Gesamtüberblick über die Verwendung der Blöcke.

    Folgendes Beispiel zeigt die Anwendung von DBMS_RESULT_CACHE.MEMORY_REPORT: 
    SQL> set serveroutput on
SQL> execute dbms_result_cache.memory_report()
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 100M bytes (100K blocks)
Maximum Result Size = 5M bytes (5K blocks)
[Memory]
Total Memory = 103528 bytes [0.027% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.026% of the Shared Pool]
....... Overhead = 65628 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 27 blocks
........... Used Memory = 5 blocks
............... Dependencies = 3 blocks (3 count)
............... Results = 2 blocks
................... SQL     = 1 blocks (1 count)
................... PLSQL   = 1 blocks (1 count)

PL/SQL procedure successfully completed.
    Offensichtlich ist ein (1) Block für ein SQL Ergebnis-Resultat und ein (1) Block für ein PL/SQL-Funktionsergebnis reserviert. Allokiert sind im Moment nur 0.027% des Shared Pools, da wir gerade mit dem Cachen von Ergebnissen begonnen haben. Im weiteren Verlauf wird sich natürlich dieser Wert entsprechend erhöhen.

    V$RESULT_CACHE_OBJECTS gibt darüberhinaus Aufschluss über die Nutzung der einzelnen Ergebnisse im Cache und ihre Abhängigkeiten. So kann man genau feststellen, wie häufig z.B. ein "Cache-Objekt" vom Typ "Result" oder "Dependency" verwendet worden ist, oder ob und wie häufig dieses invalidiert worden ist.
    Möchte man nun genau prüfen, welche Ergebnisse im Result Cache verwaltet werden, sollte man folgende Abfrage durchführen: 
    NAME                 TYPE        ROW_COUNT BLOCK_COUNT INVALIDATIONS SCAN_COUNT
-------------------- ---------- ---------- ----------- ------------- ----------
HR.GET_DATUM         Dependency          0           1             0          0
SCOTT.EMP            Dependency          0           1             0          0
HR.BIGEMP            Dependency          0           1             0          0
"HR"."GET_DATUM"::8. Result              1           1             0          1
"GET_DATUM"#27dda668
fe0cf492 #1

SELECT /*+ result_ca Result             12           1             0          4
che */
    COUNT(*)    tota
l_count,
    SUM(salary) tota
l_sal
  FROM bigemp
group by department_
id
ORDER BY
    Die beiden Ergebnisblöcke setzen sich offensichtlich aus der Abfrage 'SELECT /*+ result_cache */ COUNT(*) ...' und der ausgeführten PL/SQL Funktion HR.GET_DATUM zusammen. Dabei ist das Ergebnis der Abfrage schon 4 mal genutzt worden (siehe Spalte SCAN_COUNT). Invalidierungen, d.h. Änderungen an den abhängigen Objekten (z.B. an der Tabelle BIGEMP) sind nicht erfolgt, wie am Spaltenwert 0 der Spalte INVALIDATIONS zu erkennen ist.

    Einige abschliessende Beispiele illustrieren im Folgenden die weitere Verwendung.
    1) Im ersten Beispiel sollen Veränderungen an einer darunterliegenden Tabelle durchgeführt werden. Aus diesem Grund verändern wir die Tabelle BIGEMP mit folgendem Statement: 
    INSERT INTO bigemp SELECT * FROM bigemp;
COMMIT;
    Danach wird die erste Abfrage von oben mehrfach ausgeführt. Das Ergebnis in V$RESULT_CACHE_OBJECTS zeigt die Invalidierung in der Spalte "INVALIDATIONS" der Tabelle HR.BIGEMP an. Der Status des "alten" Resultats ändert sich auf "Invalid", parallel dazu wird eine neue Zeile mit den Informationen des neuen Result Cache erzeugt. Diese besitzt den Status "Published". Mittlerweile wurde das Resultat schon 5 Mal genutzt, wie in der Spalte SCAN_COUNT zu sehen ist. 
    SQL> SELECT name, type, row_count,  status, invalidations, scan_count
     FROM v$result_cache_objects;

NAME                 TYPE        ROW_COUNT STATUS    INVALIDATIONS SCAN_COUNT
-------------------- ---------- ---------- --------- ------------- ----------
HR.GET_DATUM         Dependency          0 Published             0          0
SCOTT.EMP            Dependency          0 Published             0          0
HR.BIGEMP            Dependency          0 Published             1          0
SELECT /*+ result_ca Result             12 Published             0          5
che */ COUNT(*) tota
l_count, SUM(salary)
 total_sal
FROM hr.bigemp group
 by department_id OR
DER BY department

"HR"."GET_DATUM"::8. Result              1 Published             0          1
"GET_DATUM"#27dda668
fe0cf492 #1

SELECT /*+ result_ca Result             12 Invalid               0          4
che */ COUNT(*) tota
l_count, SUM(salary)
 total_sal
FROM bigemp group by
 department_id ORDER
 BY department_id

6 rows selected.

    2.) In den nächsten Beispielen sollen unterschiedliche Abfragen durchgeführt werden. Dabei soll überprüft werden, ob die existierenden Result Caches genutzt oder ob neue Result Cache Bereiche erzeugt werden.
    Folgende Queries, die diegleiche Ergebnismenge besitzen, werden nun ausgeführt: 
    SELECT /*+ result_cache */ COUNT(*), SUM(salary) total_sal
FROM bigemp group by department_id ORDER BY department_id;

    SELECT /*+ result_cache */ COUNT(*), SUM(salary) total_sal
FROM HR.bigemp group by department_id ORDER BY department_id;

    SELECT /*+ result_cache */ COUNT(*), SUM(salary) total_sal
FROM bigemp WHERE salary>0 group by department_id ORDER BY department_id;
    Im nächsten Beispiel werden Bindvariablen in SQL*Plus verwendet, und folgende Abfragen ausgeführt: 
    variable var1 number
execute :var1:=5000

SELECT /*+ result_cache */ COUNT(*) total_count, SUM(salary) total_sal
FROM hr.bigemp where salary>:var1 group by department_id ORDER BY department_id;

execute :var1:=100

SELECT /*+ result_cache */ COUNT(*) total_count, SUM(salary) total_sal
FROM hr.bigemp where salary>:var1 group by department_id ORDER BY department_id;
    Das Ergebnis in V$RESULT_CACHE_OBJECTS sieht dann folgendermassen aus: 
    SQL> SELECT name, row_count, scan_count
     FROM v$result_cache_objects WHERE type='Result' ORDER BY creation_timestamp DESC;

NAME                                                     ROW_COUNT SCAN_COUNT
------------------------------------------------------- ---------- ----------
SELECT /*+ result_cache */                                      12          0
    COUNT(*)    total_count,
    SUM(salary) total_sal
  FROM hr.bigemp where salary>:var1
group by d

SELECT /*+ result_cache */                                      11          0
    COUNT(*)    total_count,
    SUM(salary) total_sal
  FROM hr.bigemp where salary>:var1
group by d

SELECT /*+ result_cache */ COUNT(*), SUM(salary) total_         12          0
sal
FROM bigemp WHERE salary>0 group by department_id ORDER
 BY department

SELECT /*+ result_cache */ COUNT(*), SUM(salary) total_         12          0
sal
FROM HR.bigemp group by department_id ORDER BY departme
nt_id

SELECT /*+ result_cache */ COUNT(*), SUM(salary) total_         12          0
sal
FROM bigemp group by department_id ORDER BY department_
id
...
    Für die verwendeten Abfragen wurden neue Result Cache Bereiche erzeugt. Änderungen an den Statements durch Hinzufügen der Schemabezeichnung oder einer WHERE-Klausel oder die Anwendung von verschiedenen Werten bei Bindvariablen führen offensichtlich in unserem Test zur Erweiterung des Result Caches.

    Mehr zu diesem Thema bzw. zu weiteren Themen rund um die Datenbank lesen Sie in den nächsten Ausgaben ...

    Zurück zur Community-Seite