Re: why different db cache hit ratios?

Frank van Bortel <fbortel_at_nescape.net>
> Who cares about BDCHR - what platform and version are you anyway?
It's an incomplete statement. You should have added "if it's high" to it..
You should care and look into the sources that are causing the bulk of physical i/o if BCHR is low.. if it's such a useless stat what is it still included in statspack reports...

I'm tired of reading bchr-bashing propaganda from some DBAs here and everywhere. Here is the end of bchr-bashing myth:

1- You should regularly monitor bchr
2- You need to compute the bchr on delta stats not on cumulative stats in v$sysstat, That is:

    a- take a snapshot of system stats related to bchr and store them in

       variables.
    b- Wait for sometime…
    c- Compute the hit ratio on delta stats (current stats – stats stored in

       variables)
      sample code:

l_logreads  NUMBER;
l_preadsdirlob  NUMBER;
l_preadsdir NUMBER;
l_preads    NUMBER;
l_missratio NUMBER;
 

BEGIN    SELECT s.value, l.value, d.value, p.value  INTO l_logreads, l_preadsdirlob, l_preadsdir, l_preads  FROM v$sysstat s, v$sysstat l, v$sysstat d, v$sysstat p  WHERE s.name = 'session logical reads' AND d.name = 'physical reads direct' AND

    l.name = 'physical reads direct (lob)' AND p.name = 'physical reads';  

 DBMS_LOCK.SLEEP(300); -- wait 5 min  

 SELECT 100*(( (p.value-l_preads) - (l.value-l_preadsdirlob) - (d.value-l_preadsdir)) /

    (s.value-l_logreads)) dbcmr
 INTO l_missratio
 FROM v$sysstat s, v$sysstat l, v$sysstat d, v$sysstat p  WHERE s.name = 'session logical reads' AND d.name = 'physical reads direct' AND

    l.name = 'physical reads direct (lob)' AND p.name = 'physical reads';    

 DBMS_OUTPUT.PUT_LINE ('Buffer Cache Hit Ratio: ' || l_missratio); END; 3- If bchr is low, you need to look into potential database sources that are doing too much physical i/o Received on Wed Dec 24 2003 - 07:39:17 CST