LLI Wiki — My Digital Knowledge Base

Oracle: buffer cache hit ratio

Measure the Database Buffer Cache hit ratio, estimate the effect of another cache size and find the objects that use the cache.

The Database Buffer Cache keeps data blocks read in memory. Its hit ratio shows the share of reads served without a disk access. The queries below measure it, estimate the effect of a different cache size and find the objects that use the most cache. Sources: sqlpac.com and pafumi.net.

Block size

select value from v$parameter where name = 'db_block_size';
select distinct bytes/blocks from user_segments;

Estimate the effect of the cache size

The view V$DB_CACHE_ADVICE gives, for several cache sizes, the estimated number of physical reads.

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';

Overall hit ratio

First formula, from 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';

Percentage variant, using 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;

Hit ratio per buffer 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

Objects holding the cache

The view V$BH has one row per block in the cache. The query lists the objects that hold more than half of the number given by the db_block_buffers parameter.

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 ;

Cache contention

Number of buffers inspected by the database writer, then waits related to the 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');

Notes

Mastodon LLIMastodon SocialFosstodon