Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: why different db cache hit ratios?
Ibrahim DOGAN wrote:
> Frank van Bortel <fbortel_at_nescape.net>
>
>>Who cares about BDCHR - what platform and version are you anyway?
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 BortelReceived on Wed Dec 24 2003 - 12:58:45 CST