Re: Buffer Cache Hit Ration Formulas??

Can anyone tell me when v$buffer_pool_statistics comes in, i.e. which version?

Can anyone explain the difference Jurij highlights with the analysis below?

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. Jurij Modic <jmodic_at_src.si> wrote in message news:37b5aba2.16744578_at_news.siol.net...
> On Wed, 11 Aug 1999 15:17:59 GMT, jdanton1_at_yahoo.com wrote:
>
> >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.
>
> I'd say 1 and 4 are not correct in the concept. Logical reads
> (db_block_gets + consistent_gests) are all the reads that are
> performed, part of which must be done by accessing physical datafiles
> - and this are physical reads. So physical reads are really a subset
> of logical reads, i.e. they are counted both as logical reads and as
> physical reads. So the queries number 2 and 3 are conceptually
> correct.
>
> The different story is why this three statistics do not match in
> V$SYSSTAT as opposed to V$BUFFER_POOL_STATISTICS. Here is a query I
> run a couple of times soon after the database was restarted. Note that
> I have only DEFAULT buffer pool (no KEEP and NO recycle POOL).
>
> Connected to:
> Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
> With the Partitioning and Java options
> PL/SQL Release 8.1.5.0.0 - Production
>
> SQL> select SUBSTR(n.name,1,20) name,
> 2 s.value sysstat,
> 3 DECODE(n.name, 'physical reads', b.physical_reads,
> 4 'db block gets' , b.db_block_gets,
> 5 'consistent gets', b.consistent_gets)
> buffstat,
> 6 s.value - DECODE(n.name, 'physical reads', b.physical_reads,
> 7 'db block gets' , b.db_block_gets,
> 8 'consistent gets', b.consistent_gets)
> diff
> 9 from v$sysstat s, v$statname n, v$buffer_pool_statistics b
> 10 where n.statistic# = s.statistic#
> 11 and n.name in ('physical reads','db block gets','consistent
> gets');
>
> NAME SYSSTAT BUFFSTAT DIFF
> -------------------- ---------- ---------- ----------
> db block gets 364 364 0
> consistent gets 15447 11559 3888
> physical reads 773 737 36
>
>
> SQL> /
>
> NAME SYSSTAT BUFFSTAT DIFF
> -------------------- ---------- ---------- ----------
> db block gets 399 399 0
> consistent gets 81998 48970 33028
> physical reads 839 803 36
>
> SQL> /
>
> NAME SYSSTAT BUFFSTAT DIFF
> -------------------- ---------- ---------- ----------
> db block gets 447 447 0
> consistent gets 83934 50177 33757
> physical reads 923 887 36
>
> It seems that only db block gets are counted totally equal. Physical
> reads seems to show that immediately after the database startup there
> is a certain offset between the values in both statistics (in my case
> 36), but then they are both incremented equally. The largest
> difference is shown in consistent gets. I don't understand why it is
> counted considerably less often in V$BUFFER_POOL_STATISTICS than in
> V$SYSSTAT, but I tend to consider V$SYSSTAT more reliable about this
> statistics (perhaps because it's been around for much longer time than
> V$BUFFER_POOL_STATISTICS).
>
> >Thanks,
> >
> >Joey D'Antoni
>
> Regards,
> Jurij Modic <jmodic_at_src.si>
> Certified Oracle DBA (7.3 & 8.0 OCP)
> ================================================
> The above opinions are mine and do not represent
> any official standpoints of my employer
Received on Thu Aug 12 1999 - 10:42:03 CDT