LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon