Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Buffer Cache Hit Ration Formulas??
Very interesting - are the databases static (no one using them) while you
perform the tests.
Otherwise some of what you see will be a moving goal post problem.
Regards
--
Kevin A Lewis (BOCM PAULS LTD - Animal Feed Manufacturer - Ipswich England)
<KevinALewis_at_HotMail.com>
The views expressed herein by the author of this document
are not necessarily those of BOCM PAULS Ltd.
<jdanton1_at_yahoo.com> wrote in message news:7os46q$bm8$1_at_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 Thu Aug 12 1999 - 04:55:01 CDT