Im Rahmen unserer Artikelreihe über die Neuheiten in Oracle Database 11g greifen wir in dieser Ausgabe mit dem Thema "Result Caching" eine weitere Hauptfunktion dieser neuen Version auf. In bestimmten Anwendungsbereichen wird der Result Cache in der Lage sein, die Antwortzeiten deutlich zu verbessern und Ressourcen einzusparen.

select /*+ result_cache */ sum(GEHALT) from MITARBEITER; --------------------------------------------------------------------- |Id |Operation |Name |Rows |Bytes |Cost --------------------------------------------------------------------- | 0 |SELECT STATEMENT | | 1 | 4 | 3 | 1 |RESULT CACHE |25wurjfyk3m7ya94nj5j9bdqrc | | | | 2 |SORT AGGREGATE | | 1 | 4 | | 3 |TABLE ACCESS FULL |MITARBEITER | 13 | 52 | 3 --------------------------------------------------------------------- Result Cache Information (identified by operation id): --------------------------------------------------------------------- 1 - column-count=1; dependencies=(ORA01.MITARBEITER); attributes=(single-row); name="select /*+ result_cache */ sum(GEHALT) from MITARBEITER" Statistics --------------------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads ... Abb. 1: Ausführungsplan und Statistiken zur Nutzung des Result Cache. Abfrageergebnis Materialized View (MV) SQL Query Result Cache Ablageort Eigenes Objekt im physikalischen Speicher der Datenbank Im Speicher (SGA) Aufbewahrungsdauer Manuelles Entfernen / Refresh Instance Shutdown / automatische Invalidierung / LRU-Mechanismus Bei Änderungen an Basistabellen MV ist statisch, d. h. sie weiß zunächst nichts von den Änderungen => Refresh der MV ist anzustoßen Cache wird automatisch invalidiert und bei Folgeausführung aktualisiert. Zugriff auf veraltete Abfragedaten möglich? Ja, Risiko besteht bei Einstellung stale_tolerated Nein (Ausnahme: Caching von Remote-Resultaten) Rewrite-Funktion vorhanden? Ja, umfangreich Nein, nur geringe Änderungen in der Schreibweise der Abfrage bei den Folgeausführungen werden toleriert (wie z. B. geänderte Groß-/Kleinschreibung, Whitespaces). Abb. 2: Funktionalität und Einsatzgebiete der beiden Konzepte Materialized View (MV) und SQL Query Result Cache. CREATE FUNCTION maAnzahl_Cache ( p_abteilungsnr NUMBER ) RETURN NUMBER RESULT_CACHE RELIES_ON (mitarbeiter) IS v_count NUMBER; BEGIN SELECT count(*) INTO v_count FROM mitarbeiter WHERE abteilungsnr = p_abteilungsnr; RETURN v_count; END; / Abb. 3: PL/SQL-Funktion mit Result Cache Klausel. Abb. 4: PL/SQL Function Result Cache. Vergrößern

Das bisherige Caching von Datenbankblöcken in den verschiedenen SGA-Bereichen, wie Default Pool, Keep oder Recycle Buffer Pool, bestimmt unter Oracle Database 11g längst nicht mehr das Maß aller Dinge, sprich: Daten, die im Cache der Datenbank gehalten werden können.

Mit Oracle 11g ist es nun auch möglich, Abfrageergebnisse und PL/SQL-Funktionsresultate in einem reservierten Bereich des Shared Pool, der als Result Cache bezeichnet wird, zwischenzuspeichern.

Konzept des Result Cache in Oracle 11g

Die Nutzung des Result Cache setzt den Einsatz der Enterprise Edition voraus und wird in erster Linie über den Initialisierungsparameter RESULT_CACHE_MODE mit seinen möglichen Werten FORCE oder MANUAL geregelt. Standardmäßig steht dieser auf MANUAL. Das bedeutet, dass SQL-Abfragen explizit den Optimizer Hint /*+ RESULT_CACHE */ einsetzen müssen, um sowohl bei ihrer erstmaligen Ausführung das Abfrageergebnis im Result Cache ablegen zu können als auch bei ihren Folgeausführungen das Abfrageergebnis aus dem Result Cache wieder verwenden zu können. Da der Inhalt des Result Cache über alle Sessions hinweg nutzbar ist, funktioniert das natürlich auch mit Folgeausführungen in einer anderen Session. Sobald eine der Tabellen, auf denen die zwischengespeicherten Resultate basieren, eine Daten- oder Strukturänderung erfährt, wird das zugehörige Result Set - ohne ein manuelles Eingreifen des DBAs - invalidiert und muss bei der nächsten Ausführung der Abfrage neu aktualisiert werden.

Formen des Result Cache in Oracle 11g

Folgende Formen des Result Cache werden in Oracle Database 11g unterschieden:

• Server Result Cache
  • SQL Query Result Cache
  • PL/SQL Function Result Cache
• OCI Result Cache auf Client Seite

Nutzung des Result Cache

Angenommen, es wird eine Abfrage unter Verwendung des Hint /*+ RESULT_CACHE */ an die Datenbank abgegeben, so prüft der Optimizer zunächst, ob diese Abfrage bereits von einer anderen Session oder einem anderen Benutzer ausgeführt und in den Result Cache abgelegt wurde. Ist dies der Fall, wird die Abfrage direkt aus dem Result Cache beantwortet, was zu erheblich kürzeren Antwortzeiten führen kann, da keine Datenbankblöcke - weder physikalisch noch logisch - mehr gelesen und keine Berechnungen zur Ermittlung des Resultats mehr durchgeführt werden müssen.

Die Nutzung des Result Cache ist im Ausführungsplan in Abbildung 1 zu erkennen.

Die Zeile mit dem Operator RESULT CACHE ist ein eindeutiges Indiz dafür, dass der SQL Query Result Cache genutzt wurde. Für den Result Cache Eintrag wird eine interne Cache Id (z. B.: 25wurjfyk3m7ya94nj5j9bdqrc) vergeben, über die der aktuelle Status des Result Cache Eintrages in der neuen View V$RESULT_CACHE_OBJECTS abgefragt werden kann. Leider ist anhand des Ausführungsplanes nicht zu erkennen, ob es sich hier um die Ablage des Resultats (= erste Ausführung) oder um die Wiederverwendung des bereits abgelegten Resultats (= Folgeausführung) handelt. Bei zusätzlicher Betrachtung der Statistiken wird eine erheblich reduzierte Anzahl an Consistent Reads (im Beispiel auf 0!) letztendlich den entscheidenden Hinweis auf eine Folgeausführung liefern können.

Vorteile und Anwendungsbereiche des Result Cache

Durch die Nutzung des Result Cache bei vorhersehbaren, sich immer wiederholenden SELECT-Abfragen und PL/SQL-Funktionen können Laufzeit, CPU-Ressourcen sowie physikalische (Disk) und logische Zugriffe (Memory) eingespart werden. Voraussetzung ist aber, dass sich die zu Grunde liegenden Datenbestände nur selten, oder noch besser, überhaupt nicht ändern.

Gute Kandidaten für den Result Cache sind sowohl Abfragen, die viele Zeilen selektieren, aber nur wenige Zeilen im Ergebnis als auch langlaufende, komplexe und mit aufwändigen Berechnungen versehene Abfragen und Unterabfragen.

Server Result Cache in der Datenbank-Setup

Der Server Result Cache, den sich die beiden Cache Bereiche "SQL Query Result Cache" und "PL/SQL Function Result Cache" untereinander teilen, kann mit Hilfe der neuen Initialisierungsparameter RESULT_* dynamisch konfiguriert werden.

Die maximale Größe des Server Result Cache im Shared Pool wird durch den Parameter RESULT_CACHE_MAX_SIZE bestimmt. Setzt der DBA diesen Parameter nicht explizit, belegt Oracle standardmäßig einen Bereich von 0,25% des neuen Parameters MEMORY_TARGET, 0,5% von SGA_TARGET oder 1% der SHARED_POOL_SIZE, je nachdem, welcher von diesen Parametern definiert ist und in Abhängigkeit von der soeben genannten Reihenfolge dieser Parameter. Ein explizit gesetzter Wert von 0 schaltet den Result Cache aus.

RESULT_CACHE_MODE regelt die Art, wie der Result Cache genutzt wird. Standardmäßig steht dieser Parameter auf MANUAL und bedingt die Verwendung des Optimizer Hint /*+ RESULT_CACHE */, um den Result Cache ansprechen zu dürfen. Die Einstellung FORCE hingegen versucht, alle Resultate im Result Cache abzulegen und von dort wiederzuverwenden. Mit Hilfe des Optimizer Hint /*+ NO_RESULT_CACHE */ kann der Result Cache hier gezielt umgangen werden.

Unabhängig davon, in welchem Modus der Result Cache geführt wird, darf ein einzelnes Abfrageresultat einen bestimmten Prozentsatz von RESULT_CACHE_MAX_SIZE nicht überschreiten. Dieser Prozentsatz (0% - 100%) wird über den Parameter RESULT_CACHE_MAX_RESULT gesteuert und beträgt standardmäßig 5%.

Sollen auch Abfrageergebnisse, die unter Beteiligung von Remote-Tabellen zustande kamen, im Result Cache aufgenommen werden, ist der Parameter RESULT_CACHE_REMOTE_EXPIRATION auf einen Wert größer 0 zu setzen. Dieser bestimmt (in Minuten) die Gültigkeitsdauer der Resultate, bei denen entfernte Objekte beteiligt waren.

Zu beachten ist, dass diese Einstellung zu veralteten Abfrageresultaten führen kann, da die lokale Datenbank keine Kenntnis von inzwischen veränderten Daten auf der entfernten Seite bekommt. Aus diesem Grund kann es ratsam sein, die Voreinstellung von 0 Minuten, was ein Caching von Remote-Resultaten verhindert, zu übernehmen.

Monitoring des Server Result Cache

Für das Monitoring des Result Cache wurden in Oracle 11g neue Dynamic Performance Views eingeführt:

• V$RESULT_CACHE_OBJECTS
Gibt einen Überblick über alle Abfrage- und Funktionsresultate im Result Cache sowie deren Abhängigkeiten.
• V$RESULT_CACHE_DEPENDENCY
Diese View zeigt die Verbindungen zwischen den im Result Cache befindlichen Abfrageergebnissen und die davon abhängigen Objekte.
• V$RESULT_CACHE_STATISTICS
Über diese View erhält man mehr Informationen über die aktuellen Einstellungen und die Nutzung des Result Cache.
• V$RESULT_CACHE_MEMORY
Alle momentan im Cache befindlichen Blöcke inklusive deren Status (Spalte FREE: YES|NO) werden hier aufgelistet.

Neben diesen Views bietet die PL/SQL-Prozedur DBMS_RESULT_CACHE.MEMORY_REPORT zusätzlich die Möglichkeit, einen Report über die Nutzung des Result Cache Memory zu generieren.

Abgrenzung des SQL Query Result Cache zur Materialized View

Diejenigen, die bereits mit Materialized Views (MV) gearbeitet haben, können sicherlich Parallelen zwischen den beiden Konzepten MV und Result Cache ziehen. Zwar erinnert das Konzept des Result Cache an eine im Speicher (SGA) liegende "Just-in-time" Materialized View, genauer betrachtet unterscheiden sich die beiden Konzepte allerdings doch wesentlich in ihrer Funktionalität und dem Einsatzgebiet voneinander (siehe Abbildung 2).

PL/SQL Function Result Cache

Wie bereits angedeutet, setzt der PL/SQL Function Result Cache ebenfalls auf der bis dato vorgestellten Result Caching Funktionalität auf und erlaubt das Caching von Funktionsresultaten in Abhängigkeit ihrer Parameterwerte. Hierfür muss die PL/SQL-Funktion einfach mit der Compiler-Direktive RESULT_CACHE ausgestattet werden (siehe Abbildung 3).

Mit der "optionalen" Klausel RELIES_ON können die abhängigen Tabellen angegeben werden, bei denen Änderungen der Daten zur Invalidierung der entsprechenden Function Cache Einträge führen. Unterlässt man allerdings diese "freiwillige" Angabe, besteht die Gefahr des Zugriffs auf veraltete Daten, da Oracle bei Datenänderungen die hiervon abhängigen Funktionsresultate im Cache nicht mehr automatisch invalidiert!

Wenn angewendet, prüft Oracle den Result Cache, um zu sehen, ob ein Aufruf der Funktion - mit den gleichen Übergabeparametern - bereits existiert und liefert das im Cache befindliche Ergebnis zurück, statt die Funktion nochmals auszuführen (siehe Abbildung 4). Funktionen, die häufig ausgeführt werden und deren abhängige Daten nur selten oder nie eine Datenänderung erfahren, erweisen sich als optimale Kandidaten für diese Art des Result Caching.

OCI Client Result Cache

Auf Client- oder Application-Server-Seite können Ergebnisse von Abfragen und Funktionen nun ebenfalls in einem separaten Speicherbereich zwischengespeichert werden. Dieser wird als OCI Client Result Cache bezeichnet. Wie der Name bereits impliziert, ist das Cachen auf Client-Seite nur möglich, wenn es sich beim Client bzw. Application Server um eine Applikation handelt, die über das Oracle Call Interface (OCI) mit der Datenbank kommuniziert. Ein Treffer im Client Result Cache bewirkt, dass die beiden OCI Calls OCIStmtExecute() und OCIStmtFetch() zukünftig lokal bearbeitet werden können. Sessions auf dem Client oder Application Server können sich somit bei wiederholten Abfragen den "Gang" an die Datenbank sparen.

OCI Client Result Cache aktivieren

Um den Client Result Cache nutzen zu können, muss zunächst auf Server-Seite dessen maximale Größe in Bytes über den statischen Initialisierungsparameter CLIENT_RESULT_CACHE_SIZE (Default: 0) gesetzt werden. Die Aktivierung auf dem Server vorausgesetzt, kann dieser Server-Parameter über die sqlnet.ora Einstellungen OCI_RESULT_CACHE_MAX_SIZE am Client überschrieben und pro Abfrageresultat mit den Parametern OCI_RESULT_CACHE_MAX_RSET_SIZE und OCI_RESULT_CACHE_MAX_RSET_ROWS genauer spezifiziert werden.

Datenkonsistenz im OCI Client Result Cache

Es stellt sich natürlich die Frage, wie Oracle es schafft, den Cache-Inhalt am Client mit den Änderungen, die in der Datenbank ausgeführt werden, konsistent zu halten. Zur Gewährleistung der Konsistenz des Client Cache nutzt Oracle folgendes Verfahren:

Bei allen Anfragen des OCI-Client an die Datenbank bekommt dieser Informationen über Invalidierungen aufgrund geänderter (Meta-)Daten mit auf den Weg zurück geschickt. Sollte die OCI-Applikation allerdings für eine bestimmte Zeit keine Calls an die Datenbank absetzen, tritt die statische Server-Einstellung CLIENT_RESULT_CACHE_LAG in Kraft, die standardmäßig nach 3000 ms den nächsten Aufruf OCIStmtExecute() erzwingt. Dieser erzwungene Aufruf an die Datenbank erkundigt sich nach solchen Invalidierungen, die am Client letztlich zur Invalidierung der entsprechenden Resultate im Client Cache führen.

Fazit

Für Datenbankadministratoren, SQL- und PL/SQL-Entwickler wird der Einsatz dieser neuen Funktion als sehr einfaches Mittel zum Tuning von SQL und PL/SQL vorkommen. Die sehr großen Performance-Verbesserungen werden sich jedoch nur bei richtiger Anwendung und Konfiguration dieser Funktion einstellen können. Diese sind bei Anwendungslogiken zu erwarten, die häufig die gleichen Abfragen absetzen und deren Daten - in Relation dazu gesehen - geringen Änderungen unterliegen.

In einer der nächsten Ausgaben der ORDIX News werden wir Neuerungen beim Data Pump in Oracle 11g näher beleuchten.

Kathleen Hock (info@ordix.de).