Nethence Newdoc Olddoc Lab Your IP BBDock  


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

UnixWindowsOracleObsoleteHardwareDIYMechanicsScriptsConfigs

Buffer cache hit ratio
 
 
Introduction
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 P1.name = 'db block gets'
AND P2.name = 'consistent gets'
AND P3.name = 'physical reads';
ref. http://www.dbspecialists.com/files/presentations/buffercache.html (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,
select
100*(1 - (v3.value / (v1.value + v2.value))) "Cache Hit Ratio [%]"
from
v$sysstat v1, v$sysstat v2, v$sysstat v3
where
v1.name = 'db block gets' and
v2.name = 'consistent gets' and
v3.name = 'physical reads';
ref. http://www.cryer.co.uk/brian/oracle/tuning_bchr.htm
 
 
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 cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
/
ref. http://www.orafaq.com/wiki/Improving_Buffer_Cache_Hit_Ratio
 
 
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))))
ideal_buffers
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
/
ref. http://www.dbasupport.com/forums/archive/index.php/t-26172.html
 
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
select
size_for_estimate c1,
buffers_for_estimate c2,
estd_physical_read_factor c3,
estd_physical_reads c4
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';
look at the Estd Phys Read Factor field. Possibles values go from 0.1 to 2.0.
refs.
http://www.dba-oracle.com/art_builder_buffers.htm
http://www.dba-oracle.com/oracle_tips_data_cache_advice.htm
 
 
Further readings
(9i) http://www.praetoriate.com/op_unix_166_data_buffer_cache.htm
(10g) http://www.oracle-training.cc/t_allocating_oracle_buffer_caches.htm
 

(obsolete, see the new doc)