Nethence Documentation Lab Webmail Your IP BBDock  

Those documents are obsolete, please use the Nethence Documentation instead.


Buffer cache hit ratio
Those sql commands have been tested on 9i and 10g.
Current ratio
Best is between 90 and 95%.
First method
Check the buffer cache hit ratio,
SELECT (P1.value + P2.value - P3.value) / (P1.value + P2.value)
FROM v$sysstat P1, v$sysstat P2, v$sysstat P3
WHERE = 'db block gets'
AND = 'consistent gets'
AND = 'physical reads';
ref. (other tests available, with v$recent_bucket and v$current_bucket)
Second method
Check the buffer cache hit ratio,
select name, value from v$sysstat
where name in ('db block gets', 'consistent gets', 'physical reads');
and calculate,
Hit ratio = 1 - (physical reads / (db block gets + consistent gets))
otherwise get the ratio directly,
100*(1 - (v3.value / (v1.value + v2.value))) "Cache Hit Ratio [%]"
v$sysstat v1, v$sysstat v2, v$sysstat v3
where = 'db block gets' and = 'consistent gets' and = 'physical reads';
Third method (almost the same)
Check the buffer cache hit ratio,
SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE = 'db block gets'
AND = 'consistent gets'
AND = 'physical reads'
Estimated best ratio
Estimate buffer cache size,
select /*+ ordered use_hash(b) */ n.bp_name buffer_pool,
count(*) current_buffers, count(*) +
count(decode(lru_flag, 0, decode(tch, 0, null, 1, null, 1))) -
count(decode(state, 0, 1, decode(lru_flag, 8, decode(tch, 0, 1, 1, 1))))
from (select /*+ ordered */ p.bp_name, s.addr
from x$kcbwbpd p, x$kcbwds s
where s.inst_id = userenv('Instance')
and p.inst_id = userenv('Instance')
and s.set_id >= p.bp_lo_sid
and s.set_id <= p.bp_hi_sid
and p.bp_size != 0) n, x$bh b
where b.inst_id = userenv('Instance')
and b.set_ds = n.addr
group by n.bp_name
Display cache advice,
column c1 heading 'Cache Size (m)' format 999,999,999,999
column c2 heading 'Buffers' format 999,999,999
column c3 heading 'Estd Phys|Read Factor' format 999.90
column c4 heading 'Estd Phys| Reads' format 999,999,999
size_for_estimate c1,
buffers_for_estimate c2,
estd_physical_read_factor c3,
estd_physical_reads c4
name = 'DEFAULT'
block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
advice_status = 'ON';
look at the Estd Phys Read Factor field. Possibles values go from 0.1 to 2.0.
Further readings

Last update: Jul 08, 2011