Oracle 11g feature van de maand: Result Cache

November 2007 - Begin november werd versie 11g van de Oracle database officieel in Nederland gelanceerd. Op de Oracle site is de lijst met nieuwe features te vinden. Een van de meest interessante features voor ontwikkelaars is de result cache. De result cache slaat de resultaten van zowel query's als PL/SQL functies op. Als deze query's en functies opnieuw uitgevoerd worden, dan zijn de resultaten direct uit het geheugen beschikbaar. De database hoeft dan niet meer benaderd te worden. Dit resulteert in snellere response tijden.

In dit Whitebook bekijken we wat de result cache precies inhoudt en hoe we dit in zowel SQL als PL/SQL kunnen gebruiken. Tenslotte wordt ingegaan op het beheer van de result cache.

Wat is de result cache?

De result cache slaat resultaten van query's en PL/SQL functies op in een apart gedeelte van de shared pool, namelijk het Result Cache Memory. Omdat de result cache onderdeel is van de shared pool, is de inhoud van de cache beschikbaar voor alle sessies binnen de database. De resultaten worden uit de cache verwijderd, zodra de data uit afhankelijke tabellen of views wijzigt. Voor de resultaten in de cache geldt het FIFO (first in - first out) principe; de oudste resultaten worden het eerst uit de cache verwijderd. Indien het resultaat te groot is voor de cache, wordt deze niet opgeslagen.

SQL Result Cache

SQL query resultaten kunnen in 11g worden gecached. Hoe dit gebeurt, hangt af van de database parameter RESULT_CACHE_MODE. Deze parameter heeft drie mogelijke waarden, namelijk MANUAL, FORCE en AUTO. Bij de waarde MANUAL zullen query resultaten alleen in de cache terechtkomen als de hint result_cache in een query wordt gebruikt.

SELECT /*+ result_cache */ deptno, count(*)
FROM   emp 
GROUP BY deptno;

De waarde FORCE doet precies het tegenovergestelde. Alle query resultaten worden in de cache opgeslagen tenzij de query de hint no_result_cache bevat. De derde mogelijke waarde AUTO staat niet beschreven in de Oracle documentatie, maar deze waarde betekent dat de optimizer zelf bepaalt wat er wordt gecached.
Query's kunnen ook bind variabelen bevatten. In dat geval worden de cache resultaten geparameteriseerd. Indien de query met dezelfde parameters wordt aangeroepen, kan het resultaat weer direct uit de cache worden gehaald.

PL/SQL Function Result Cache

Ook resultaten van PL/SQL functies kunnen worden opslagen in de result cache. Result cache voor PL/SQL functies kan eenvoudig worden ingeschakeld door de RESULT_CACHE clausule aan een functie toe te voegen. Zodra een result cache functie wordt aangeroepen, wordt eerst de cache geraadpleegd. Indien de functie al eerder is aangeroepen met dezelfde parameters, kan het resultaat direct worden teruggeven zonder de functie opnieuw uit te voeren.

Optioneel kan ook de RELIES_ON clausule worden gebruikt. D.m.v. deze clausule kan worden gespecificeerd van welke tabellen of views de functie afhankelijk is. Zodra data in deze tabellen wordt gewijzigd, zullen de resultaten voor alle afhankelijke functies uit de cache verwijderd worden. Bij de volgende aanroep wordt de functie weer opnieuw uitgevoerd en het resultaat wederom opgeslagen. Bij hercompilatie van de functie worden alle cache resultaten van de betreffende functie verwijderd.

Hieronder volgt een eenvoudig voorbeeld van een result cache functie. Deze functie haalt voor een afdeling het aantal medewerkers op en is afhankelijk van de tabel EMP. Om de werking van de result cache goed duidelijk te maken, is er een pauze van 5 seconden in de functie toegevoegd.

CREATE OR REPLACE FUNCTION aantal_mdw_per_afd ( 
  p_deptno IN dept.deptno%TYPE 
)  
RETURN NUMBER 
RESULT_CACHE RELIES_ON (emp)
AS 
   l_aantal PLS_INTEGER; 
BEGIN 
   SELECT COUNT(*) 
   INTO   l_aantal 
   FROM   emp 
   WHERE  deptno = p_deptno; 
   -- Wacht 5 seconden 
   dbms_lock.sleep(5); 
   RETURN l_aantal; 
END aantal_mdw_per_afd; 
/  

Vervolgens voeren we bovenstaande functie twee maal achter elkaar uit:

SQL> select aantal_mdw_per_afd(30) from dual;

AANTAL_MDW_PER_AFD(30)
----------------------
                     6

Elapsed: 00:00:05.01
SQL> select aantal_mdw_per_afd(30) from dual;

AANTAL_MDW_PER_AFD(30)
----------------------
                     6

Elapsed: 00:00:00.00
SQL> 
  

De functie met parameter '30' is na de eerste aanroep opgeslagen in de result cache. Als vervolgens de functie met dezelfde parameter nogmaals wordt uitgevoerd, kan het resultaat vrijwel direct worden teruggegeven.

Het volgende voorbeeld toont aan dat er relatief veel winst valt te halen door het gebruik van de result cache. Het betreft een functie die voor een gebruikersgroep een standaard printer ophaalt. De functie bevat een query over twee tabellen: een tabel met printers (10 records) en een tabel met gebruikersgroepen (40 records). Het test script ziet er als volgt uit:

DECLARE
  l_group_id  user_groups.id;
  l_printer   printers.name%TYPE;
BEGIN
  FOR i IN 1 .. 20000 LOOP
    l_group_id := ROUND(dbms_random.value(1,40));
    l_printer  := bepaal_default_printer(l_groep);
  END LOOP;
END;
/

Dit test script zal 20.000 keer de functie aanroepen met een random waarde voor de gebruikersgroep. We voeren dit script eerst uit met de voorbeeld functie zonder result cache.

SQL> @test_functie

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.22
 SQL> 

Nu maken we de functie aan met result cache en voeren we nogmaals het test script uit.

SQL> @test_functie

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.19
SQL> 

Ondanks de beperkte hoeveelheid data bij dit voorbeeld, is de functie met result cache meer dan 11 keer sneller dan de functie zonder result cache. Op het moment dat het ophalen van één resultaat langer duurt, zal het verschil relatief gezien alleen maar groter worden.

PL/SQL function result cache, de naam zegt het al, is alleen bruikbaar voor functies. Procedures cachen is niet mogelijk. Daarnaast zijn er nog een aantal restricties, zo is het niet toegestaan OUT of IN OUT parameters te gebruiken en parameters van de functie mogen niet van types BLOB, CLOB, Object, Record en REF CURSOR zijn.

De function result cache heeft op het eerste gezicht iets weg van een deterministic functie. Deze functie geeft bij herhaalde aanroep met dezelfde parameters ook het resulaat terug uit de cache. Er zijn echter twee grote verschillen. Ten eerste kan een deterministic functie geen gebruik maken van gegevens uit de database, omdat het resultaat niet opnieuw wordt bepaald bij wijziging van deze gegevens. Ten tweede zal de functie de cache resultaten alleen gebruiken in de context van een function based index of materialized view. Deze verschillen maken duidelijk dat de PL/SQL function result cache veel meer mogelijkheden biedt t.o.v. de deterministic functie.

Beheer van de result cache

Voor het beheer van de result cache zijn database parameters beschikbaar. De hoeveelheid geheugen beschikbaar voor de result cache wordt geregeld d.m.v. de database parameter RESULT_CACHE_MAX_SIZE. Door deze parameter op nul te zetten, wordt de result cache uitgeschakeld. De parameter RESULT_CACHE_MAX_RESULT geeft aan welk percentage van de RESULT_CACHE_MAX_SIZE een enkel resultaat mag zijn. De eerder genoemde parameter RESULT_CACHE_MODE geeft aan of SQL resultaten automatisch aan de result cache worden toegevoegd. Deze parameters zijn aanpasbaar zonder herstart van de database.

De DBMS_RESULT_CACHE package biedt een interface voor de result cache. Hieronder een aantal van de functies en procedures uit deze package:

• De procedure MEMORY_REPORT geeft een overzicht van het geheugengebruik van de result cache.
  

  SQL> exec 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  = 1984K bytes (1984 blocks)
  Maximum Result Size = 99K bytes (99 blocks)
  [Memory]
  Total Memory = 103528 bytes [0.095% of the Shared Pool]
  ... Fixed Memory = 5132 bytes [0.005% of the Shared Pool]
  ... Dynamic Memory = 98396 bytes [0.090% of the Shared Pool]
  ....... Overhead = 65628 bytes
  ....... Cache Memory = 32K bytes (32 blocks)
  ........... Unused Memory = 25 blocks
  ........... Used Memory = 7 blocks
  ............... Dependencies = 3 blocks (3 count)
  ............... Results = 4 blocks
  ................... SQL     = 1 blocks (1 count)
  ................... PLSQL   = 2 blocks (2 count)
  ................... Invalid = 1 blocks (1 count)
  
  PL/SQL procedure successfully completed.
  
  SQL> 
    

• De INVALIDATE procedure biedt de mogelijkheid om resultaten in de result cache ongeldig te maken. Door het opgeven van een object aan deze procedure, zullen alle cache resultaten die afhankelijk zijn van dit object ongeldig worden.
  

  SQL> EXEC DBMS_RESULT_CACHE.invalidate('SCOTT', 'EMP');
  
  PL/SQL procedure successfully completed.
  
  SQL>
  

• De FLUSH procedure verwijderd alle objecten uit de result cache.

Een uitgebreide beschrijving van alle procedures en functies van de DBMS_RESULT_CACHE package is te vinden in de Oracle documentatie.

Conclusie

Result caching is een krachtige nieuwe feature in 11g, die zeker kan bijdragen aan het verhelpen van performance bottlenecks in applicaties. Uiteraard is het van belang goed na te denken over de toepasbaarheid van de result cache. Query's en functies die vaak worden aangeroepen en gebruik maken van data die niet vaak wijzigt, komen in aanmerking voor caching. Denk hierbij bijvoorbeeld aan parameter tabellen. Ook functies die complexe berekeningen uitvoeren op (relatief) statische data, kunnen d.m.v. result caching versneld worden.
Nu wordt nog vaak dit soort caching handmatig gerealiseerd door gebruik te maken van package variabelen. Nadeel hiervan is dat dit altijd per sessie wordt opgeslagen. Met result caching zijn de resultaten beschikbaar voor alle sessies. Omdat dit alles ook relatief eenvoudig te implementeren is, is result caching een heel interessante en nuttige nieuwe feature in 11g.

Over de auteur

Peter van der Neut is Oracle consultant bij Whitehorses en heeft ruim 7 jaar ervaring in de IT. Hij houdt zich voornamelijk bezig met applicatieontwikkeling met Oracle technologie en heeft veel ervaring met PL/SQL, Forms en Designer. Daarnaast houdt hij zich bezig met nieuwe ontwikkelingen als Application Express.

Referenties

• Oracle Database 11g: The Top New Features for DBAs and Developers:
  http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/index.html
• Using the Cross-Session PL/SQL Function Result Cache:
  http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/subprograms.htm#LNPLS00817
• DBMS_RESULT_CACHE package:
  http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_result_cache.htm#ARPLS202