Re: Cache Hit Ratio from system views

On Aug 8, 10:57 am, Teresa Masino <teresa.mas..._at_peninsula.org> wrote:
> We have acquired a software package that sends alerts based on defined
> thresholds and/or events. We are working on a simple alert like when
> the cache hit ratio falls below a given threshold. The package came
> with a query that uses the V$BUFFER_POOL_STATISTICS view. We have
> typically used a query based on the V$SYSSTAT view. We left the query
> in place that came with the product, but it reports low cache hit
> ratios pretty frequently whereas putting another alert in place that
> uses V$SYSSTAT does not.
>
> We only have one pool -- DEFAULT. So we would expect the values to be
> at least close, if not the same. They are usually the same, but
> several times in the course of a day they are VERY different.
>
> In addition to be different enough to trigger the alert, the values in
> V$BUFFER_POOL_STATISTICS are sometimes a negative value. What's up
> with that?
>
> Can anyone explain to me why V$BUFFER_POOL_STATISTICS sometimes has
> negative values or has very different values from V$SYSSTAT? We'd
> like to know if we really have an issue with the size of our cache, or
> one of those views isn't the right one to use.
>
> Here are the queries being:
>
> SELECT ROUND(((SUM(cur.value) + SUM(con.value) - SUM(phy.value)) /
> (SUM(cur.value) + SUM(con.value))) * 100, 2) AS
> CLUSTER_CACHE_HIT_RATIO
> FROM v$sysstat cur, gv$sysstat con, gv$sysstat phy
> WHERE cur.name = 'db block gets'
> AND con.name = 'consistent gets'
> AND phy.name = 'physical reads'
>
> SELECT DECODE(NAME, 'DEFAULT', DECODE(block_size, 2048, 'CACHE_2K',
> 4096, 'CACHE_4K', 8192, 'CACHE_8K', 16384, 'CACHE_16K', 'CACHE_32K'),
> NAME) NAME , PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
> ( 1 - ((DECODE (PHYSICAL_READS, 0, 1, PHYSICAL_READS)) /
> DECODE((DB_BLOCK_GETS + CONSISTENT_GETS),0, DECODE(PHYSICAL_READS, 0,
> 1, PHYSICAL_READS) ,(DB_BLOCK_GETS + CONSISTENT_GETS)))) * 100 AS
> BUFFER_RATIO
> FROM V$BUFFER_POOL_STATISTICS
>
> Any assistance in helping us make sense of this is greatly
> appreciated.
>
> Teresa Masino

Which release of Oracle is this?

David Fitzjarrell Received on Wed Aug 08 2007 - 11:22:33 CDT