Cache Hit Ratio from system views

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 Received on Wed Aug 08 2007 - 10:57:33 CDT