Nethence Newdoc Olddoc Lab Your IP BBDock  

Warning: those guides are mostly obsolete, please have a look at the new documentation.


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

(obsolete, see the new doc)