Tips & Trucs

Database Buffer Cache Size - Stephen Rea

De Database Buffer Cache is een onderdeel van de Shared Global Area (SGA) welke in het geheugen staat voor een enkele database instance (SID). Het bevat de blokken data en indexes die op dat moment worden gebruikt. De Database Buffer Cache kan zelfs meerdere kopieën bevatten van eenzelfde data?block. Dat is het geval als bijvoorbeeld één proces er wijzigingen op uitvoert - maar het nog niet heeft ge-commit -, terwijl een ander proces het origineel op hetzelfde moment raadpleegt. De parameters db_block_size en db_block_buffers (gezet in de init.ora) bepalen het formaat van de buffer cache in bytes. Db_block_size wordt bepaald op het moment van creatie van de database. Het kan daarna niet meer worden gewijzigd, tenzij je de database opnieuw creeërt. Het enige wat je dus nadien nog kunt wijzigen, is het aantal blocks in db_block_buffers (1 buffer bevat 1 block). De Cache Hit Ratio toont hoeveel blokken er al in het geheugen stonden versus het aantal blokken die in het geheugen geladen moesten worden. Anders gezegd: de 'logical reads' (inclusief 'db block gets' voor blokken die je in gebruik hebt en 'consistent gets' van originele blokken van rollback-segmenten die door anderen worden gewijzigd) versus de 'physical reads'. Oracle beveelt een hit-ratio van minstens 80% aan. De hit-ratio kan uit v$sysstat worden gehaald, die constant up-to-date wordt gehouden en die de statistieken toont sinds het opstarten van de database. Dit is echter alleen toegankelijk voor iemand met DBA-rechten. Je bepaalt het als volgt:

SELECT ( 1 - ( pr.value / (
dbg.value + cg.value ) ) ) * 100
FROM v$sysstat pr
, v$sysstat dbg
, v$sysstatcg
WHERE pr.name = 'physical reads'
AND dbg.name = 'db block gets'
AND cg.name = 'consistent gets';

Indien je een lage Cache Hit Ratio hebt, kun je het effect van het toevoegen van buffers uittesten door de regel 'db_block_lru_extended_statistics = 1000' op te nemen in de init.ora, vervolgens een shutdown en startup uit te voeren en daarna een paar uur te wachten om tot een representatief voorbeeld te komen.

Oracle bepaalt hoeveel Additional Cache Hits (ACH) er zouden optreden voor elke 'query' en transactie voor elk van de 1000 bufferophogingen - een andere waarde dan 1000 invoeren mag natuurlijk ook... - en plaatst dat in de tabel x$kcbrbh (alleen toegangkelijk voor SYS). Om de nieuwe Cache Hit Ratio te meten met bijvoorbeeld 100 extra buffers, doe je het volgende:

select sum(count) "ACH" from x$kcbrbh where indx < 100;

en de uitkomst hiervan gebruik je als volgt in de Cache Hit Ratio formule:

SELECT ( 1 - ( ( pr.value ? &ACH
) / ( dbg.value + cg.value ) ) )
* 100
FROM v$sysstat pr
, v$sysstat dbg
, v$sysstat cg
WHERE pr.name = 'physical reads'
AND dbg.name = 'db block gets'
AND cg.name = 'consistent gets';

Indien de hit ratio lager was dan 80% en nu - met ACH hoger - zou je db_block_buffers met dat aantal kunnen ophogen en de database opnieuw opstarten. Probeer een aantal verschillende waarden uit voor extra buffers om de meest optimale te vinden. Vergeet niet om db_block_lru_extended_ statistices te verwijderen uit de init.ora. Dit zorgt er tevens voor dat x$kcbrbh wordt geleegd. Wees er ook zeker van dat je systeem wel voldoende geheugen heeft om deze ophoging te kunnen realiseren. Indien de geheugenruimte beperkt is en de cach hit?ratio zit boven de 80%, zou je kunnen proberen wat het effect is van verlaging van het aantal buffers. Op die manier kun je proberen geheugen vrij te maken dat gebruikt kan worden voor andere processen (hetgeen echter ook de databasetransacties zou kunnen vertragen) Om dit uit te testen, zet je 'db_block_lru_statistics = true' in de init.ora en herstart je de database. Dit zorgt ervoor dat er statistieken over Additional Cache Misses (ACM) worden verzameld, die zouden optreden voor elke 'query' en transactie voor elk van de bufferafnames tot aan de waarde van db_block_buffers. Daarna worden deze statistieken in de tabel x$kcbcbh gezet (alleen toegankelijk voor SYS).

Om de hit-ratio op te vragen voor bijvoorbeeld 100 buffers minder, bepaal je de ACM als volgt:

SELECT sum(count) "ACM" FROM
x$kcbcbh
WHERE indx >= (SELECT max( indx ) + 1 - 100 FROM x$kcbcbh);

en gebruik dat op de volgende manier in de Cache Hit Ratio formule:

SELECT ( 1 - ( ( pr.value + &ACM
) / ( dbg.value + cg.value ) ) )
* 100
FROM v$sysstat pr
, v$sysstat dbg
, v$sysstat cg
WHERE pr.name = 'physical reads'
AND dbg.name = 'db block gets'
AND cg.name = 'consistent gets';

Indien de ratio nog steeds boven de 80% is, zou je db_block_buffers met dat aantal kunnen verlagen en de database opnieuw opstarten om de wijziging effectief te laten worden. Probeer ook hier een aantal waarden uit om de meest optimale waarde te bepalen. Vergeet ook nu weer niet om db_block_lru_statistics uit de init.ora te verwijderen.

Een drietal scripts om uit te vinden wat de optimale db_block_buffers is voor een bepaalde SID vindt u op het onderdeel van de website van Stephen Rea dat u bereikt via www.uaex.edu/srea/tuning.htm:
• <cache_hit_ratio.sql>: berekent huidige hit?ratio voor db buffer cache.
• adding_buffers.sql >: script dat het resultaat ratio berekent voor een verhoging van de buffer cache size bij een x aantal buffer blocks (uitvoeren als SYS). db_block_lru_extended_statistics moet in de init.ora staan.
• <removing_buffers.sql>: berekent het resultaat ratio voor een vermindering in de buffer cache size (uitvoeren als SYS, db_block_lru_statistics in init.ora).