Re: Buffer Cache Hit Ration Formulas??

My spreadsheet give me the following

for                        value
Consistent Gets     2
DB Block Gets     5
Physical Reads     1


(cg+dbg-pr)*100/(cg+dbg)             85.71428571            TOAD

(dbg+cg)/((dbg+cg)+(pr))*100       87.5                           1

(1-((pr)/(dbg+cg)))*100                 85.71428571             2

(1-(pr/(dbg+cg)))*100                    85.71428571             3

((dbg+cg)/(dbg+cg+pr))*100          87.5                           4

I can't comment on the v$buufer pool_statistics yet but I would guess from the weight of evidence that (1) and (4) are faulty maths. I have only seen variations of the other formulas in the Oracle manuals and in peoples scripts.

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. Kevin A Lewis <KevinALewis_at_Hotmail.com> wrote in message news:OWws3.7871$B%2.631_at_newreader.ukcore.bt.net...
> Even more interesting the T.O.A.D. tool uses the following formula
>
> SELECT ROUND((CONGETS.VALUE + DBGETS.VALUE - PHYSREADS.VALUE ) * 100 /
> (CONGETS.VALUE + DBGETS.VALUE ) ,4) FROM V$SYSSTAT CONGETS,V$SYSSTAT
> DBGETS,V$SYSSTAT PHYSREADS WHERE CONGETS.NAME = 'consistent gets' AND
> DBGETS.NAME = 'db block gets' AND PHYSREADS.NAME = 'physical reads'
>
> And I would say at second look that in your example that mathamatically
> examples 1 and 4 are more alike and 2 and 3 are more alike. See what you
> think.
>
> 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.
> Kevin A Lewis <KevinALewis_at_Hotmail.com> wrote in message
> news:PLws3.7869$B%2.832_at_newreader.ukcore.bt.net...
> > 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 - 05:22:40 CDT