| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Buffer Cache Hit Ration Formulas??
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
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)
 |
 |