Re: Read Hit Ratio Question- Suggestions Welcome.

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 6 Aug 1999 05:34:02 GMT
Message-ID: <7ods4a$p9a$6@news.seed.net.tw>

Tapan Trivedi <tapan.trivedi_at_abbnm.com> wrote in message news:37A9AFF1.E7098856_at_abbnm.com...
> Hey Gurus,
> I am doing this query to find out the Read Hit ratios on a Baseline
> system (upon which further development will be done) .
>
> select 1-(sum(decode(name, 'physical reads', value,0))/
> (sum(decode(name, 'db block gets', value,0)) +
> (sum(decode(name, 'consistent gets', value,0))))) * 100
> "Read Hit Ratio"
> from v$sysstat;
>
> I am getting very weird results on about four systems on which I have
> run the queries . I am getting NEGATIVE, yes, NEGATIVE results. What
> does this mean ?

Since you put the parentheses in the wrong place. Simply put another ones:

select (1-(sum(decode(name, 'physical reads', value,0))/ (sum(decode(name, 'db block gets', value,0)) + (sum(decode(name, 'consistent gets', value,0))))) )* 100 "Read Hit Ratio"
from v$sysstat; Received on Fri Aug 06 1999 - 00:34:02 CDT