Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: why different db cache hit ratios?

Re: why different db cache hit ratios?

From: Frank van Bortel <fbortel_at_nescape.net>
Date: Wed, 24 Dec 2003 19:58:45 +0100
Message-ID: <bscn3u$tsr$1@news3.tilbu1.nb.home.nl>


Ibrahim DOGAN wrote:

> 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...

1st of all - you started this thread. And you also participated in the original 'buffer hit cache ratio' thread.

And in the sql.bsq file there are still references to IOR (remeber V5?), and I would not be surprised if they still were in 10g. Why - ask Oracle - not me.

> 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:
> DECLARE
> 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

Is this your pro-active mode? Hope you run statspack as well.

-- 
Merry Christmas and a Happy New Year,
Frank van Bortel
Received on Wed Dec 24 2003 - 12:58:45 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US