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
- The query on
V$BHuses thedb_block_buffersparameter, an older parameter: check that it exists on the target version before running it.