Buffer Cache Hit Ration Formulas??

From: <jdanton1_at_yahoo.com>
Date: Wed, 11 Aug 1999 15:17:59 GMT
Message-ID: <7os46q$bm8$1@nnrp1.deja.com>

To all,

In trying to calculate hit ratio we have come across 4 different formulas that produce 4 different results. Two of the formulas come from Oracle from V7 and V8 respectively. The other two have come from outside user guides etc.

The formulas are as follows--

1. select (sum(decode(a.name,'db block gets', value,0)) + sum(decode(a.name,'consistent gets',value,0))) / ((sum(decode(a.name,'db block gets', value,0)) + sum(decode(a.name,'consistent gets',value,0))) + (sum(decode(a.name,'physical reads', value,0)))) *100 hr from v$sysstat a, v$statname b where a.statistic# = b.statistic#
   ;
   

2)select (1 - ((sum(decode(a.name,'physical reads', value,0))) /

	(sum(decode(a.name,'db block gets',  value,0)) +
        sum(decode(a.name,'consistent gets',value,0)))))
     * 100 hr

from v$sysstat a, v$statname b
where a.statistic# = b.statistic#
;

3)select name, (1 - (physical_reads / (db_block_gets + consistent_gets)))
* 100 hit_ratio
from v$buffer_pool_statistics
;

4) select name, ((db_block_gets + consistent_gets)/ (db_block_gets + consistent_gets + physical_reads)) * 100 hit_ratio
from v$buffer_pool_statistics
;

We get the following results on several selected instances (named a-e):

     1 	         2                  3	                4

A   99.683729	99.682726	99.64895	99.650178
B   95.15888	94.912592	92.818093	93.299329
C   94.809245	94.525054	94.137671	94.462309
D   95.277418	95.043336	92.366629	92.907988
E   92.300564	91.6583	        91.045514       91.781443

While the spread varies slightly the general trend is the numbers from formulas #1 and #2 are greater than #3 and #4. I find this odd as mathematically 1 is more like 3 and 2 is more like 4. I assume since the number come from different sources they have slightly different values (or algorithms for determining the data).

I would appreciate it if anyone with knowledge of the matter could comment on what might be the most accurate formula.

Thanks,

Joey D'Antoni

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Aug 11 1999 - 10:17:59 CDT