Re: Buffer Hit Ratio

From: Verma <ranju-ravi_at_worldnet.att.net>
Date: 11 Jun 1999 18:17:04 GMT
Message-ID: <01beb436$ef057ae0$14694e0c@Verma.medco.com>

This works fine. Thanks.

Another gentleman sent me this SQL :

select 100 * ( 1 - ( sum(DECODE(NAME,'physical reads',value)) / ( sum(DECODE(NAME,'db block gets',value)) + sum(DECODE(NAME,'consistent gets',value)) )) ) a
from v$sysstat
where name in ('physical reads','db block gets','consistent gets')

Results of both are listed here :

SQL> select (1 - (phy.value /( cur.value + con.value ))) * 100 hit_ratio   2 from v$sysstat cur, v$sysstat con, v$sysstat phy   3 where cur.name = 'db block gets' and   4 con.name = 'consistent gets' and
  5 phy.name = 'physical reads'
  6 /

HIT_RATIO

---

85.857171

SQL> select (1-(sum(decode(name, 'physical reads', value,0))/

  2      (sum(decode(name, 'db block gets', value,0)) +
  3      (sum(decode(name, 'consistent gets', value,0)))))) * 100
  4      "Buffer Hit Ratio"

  5 from v$sysstat
  6 /

Buffer Hit Ratio

---

       85.879768

Sebastian Rehm <sebastian.rehm_at_de.bosch.com> wrote in article <375E2362.6283177C_at_de.bosch.com>...
> I don't know whats going wrong with the code ...
> use the following to calculate the buffer hit ratio:
>
> select (1 - (phy.value /( cur.value + con.value ))) * 100 hit_ratio
> from v$sysstat cur, v$sysstat con, v$sysstat phy
> where cur.name = 'db block gets' and
> con.name = 'consistent gets' and
> phy.name = 'physical reads'
> /
>
> Sebastian
>
> Verma wrote:
>
> > Any clue what is going wrong ?
>
>
>
>
Received on Fri Jun 11 1999 - 13:17:04 CDT