Re: Help me refine this Buffer Cache Hit Ratio query!

Jeff Perry writes

> I currently use the following query to get the raw numbers
> in determing the buffer cache hit ratio:
>
> select name, value
> from v$sysstat
> where name IN ('db block gets', 'consistent gets'
> 'physical reads');
>
> Next, I manually figure the hit ratio using the following
> formula:
>
> hit ratio = 1 - (phy read / (db blk gets + consis gets) )
>
>
> Can someone show me how this script could be modified to
> produce this hit ration value itself???

Jeff, the follwing script should do the trick.

select (1 - (c.value / (a.value + b.value) ) ) HIT_RATIO,

        a.value db_block_gets, b.value consistent_gets, c.value phy_reads
        from v$sysstat a, v$sysstat b, v$sysstat c
        where a.name = 'db block gets' AND
        b.name = 'consistent gets' AND
        c.name = 'physical reads';

It is a bit coarse, but it does the job!!!

Inte Ali			Department of Computer Science
				University of Warwick
inte_at_uk.ac.warwick.dcs		England

___________________________________________________________________

-- 

___________________________________________________________________

Inte Ali			Department of Computer Science