Oracle : taux de succès du buffer cache
Mesurer le taux de succès du Database Buffer Cache, estimer l'effet d'une autre taille de cache et repérer les objets qui occupent le cache.
Le Database Buffer Cache conserve les blocs de données lus en mémoire. Son taux de succès (hit ratio) indique la part des lectures servies sans accès disque. Les requêtes ci-dessous permettent de le mesurer, d'estimer l'effet d'une taille de cache différente et de repérer les objets qui occupent le plus de cache. Sources : sqlpac.com et pafumi.net.
Taille de bloc
select value from v$parameter where name = 'db_block_size';
select distinct bytes/blocks from user_segments;
Estimation de l'effet de la taille du cache
La vue V$DB_CACHE_ADVICE donne, pour différentes tailles de cache, le nombre de lectures physiques estimé.
set pagesize 400
set linesize 10000
COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size (MB)'
COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers'
COLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd Phys|Read Factor'
COLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys| Reads'
SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor,
estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT'
AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
AND advice_status = 'ON';
Calcul du taux de succès global
Première formule, à partir de V$SYSSTAT :
select 1 - (phy.value / (cur.value + con.value )) "Cache Hit Ratio"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name = 'db block gets'
and con.name='consistent gets'
and phy.name='physical reads';
Variante en pourcentage, avec DECODE :
select round(((sum(decode(name,'db block gets',value))
+ sum(decode(name,'consistent gets',value)))
- sum(decode(name,'physical reads',value))) /
(sum(decode(name,'db block gets',value))
+ sum(decode(name,'consistent gets',value))) * 100) "Buffer Cache Hit Ratio"
from v$sysstat;
Taux de succès par pool
select name,
(1 - physical_reads / (db_block_gets + consistent_gets )) "HIT RATIO"
from v$buffer_pool_statistics
where db_block_gets + consistent_gets > 0
Objets qui occupent le cache
La vue V$BH contient un buffer par bloc en cache. La requête liste les objets qui en occupent plus de la moitié du nombre indiqué par le paramètre db_block_buffers.
set linesize 400
COLUMN owner FORMAT a15 head 'owner';
COLUMN object_type FORMAT a15 head 'object_type';
COLUMN object_name FORMAT 999,999,999 heading 'count'
select o.owner, o.object_type, o.object_name, count(b.objd)
from v$bh b, dba_objects o
where b.objd=o.object_id
group by o.owner, o.object_type, o.object_name
having count(b.objd) > (select to_number(value*0.5) from v$parameter
where name='db_block_buffers')
order by count(b.objd) desc ;
Contention sur le cache
Nombre de buffers inspectés par le processus d'écriture, puis attentes liées au cache :
select name, value
from v$sysstat
where name='free buffer inspected';
select event, total_waits
from v$system_event
where event in ('free buffer waits','buffer busy waits');
Remarques
- La requête sur
V$BHutilise le paramètredb_block_buffers, un paramètre ancien : vérifier qu'il existe sur la version cible avant de l'exécuter.