Re: Cache Hit Ratio from system views

On Aug 8, 1:32 pm, Teresa Masino <teresa.mas...@xxxxxxxxxxxxx> wrote:
On Aug 8, 2:21 pm, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx> wrote:





On Aug 8, 12:36 pm, Teresa Masino <teresa.mas...@xxxxxxxxxxxxx> wrote:

On Aug 8, 12:53 pm, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx> wrote:

On Aug 8, 11:30 am, Teresa Masino <teresa.mas...@xxxxxxxxxxxxx> wrote:

On Aug 8, 12:22 pm, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx> wrote:

On Aug 8, 10:57 am, Teresa Masino <teresa.mas...@xxxxxxxxxxxxx> 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- Hide quoted text -

- Show quoted text -

duh, sorry. We're running 10g. Specifically:

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 on one
server and Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
on another.

Teresa- Hide quoted text -

- Show quoted text -

Does this negative output occur on both releases? It appears to work
fine on my 10.2.0.2 database.

David Fitzjarrell- Hide quoted text -

- Show quoted text -

Most of the time the queries return expected results for us too. If I
run the queries in a loop that sleeps for 30 seconds or so and let it
go for a while, they eventually report different results. It can take
10 minutes to see a discrepancy, it can take over an hour. But at
some point, they do report different values. Which leads us to wonder
which one we should pay attention to. One of them tells us things are
fine and the other says they aren't. And then there's the wild part
where the BUFFER_POOL_STATISTICS view has negative values. That part
alone makes me question the validity of those values, but I thought
I'd check here to see if anyone knows for sure.

Thanks
Teresa- Hide quoted text -

- Show quoted text -

Metalink reports the view was buggy up until 9.2.0.2, however it may
be a platform-specific issue. On which O/S are you running Oracle?

David Fitzjarrell- Hide quoted text -

- Show quoted text -

It's the server running 10.1.0.5.0 where we see the differences and
that's on Linux. I saw some old postings saying things like that too
and wondered. Afterall, we are counting on somebody somewhere writing
code to populate that data. FYI, we also opened a ticket with
Oracle, so if I get an answer from them, I'll let you know. But that
case has been open for a couple of weeks now, so I'm not very
optimistic.

Thanks
Teresa- Hide quoted text -

- Show quoted text -

10.1.0.x was fairly 'buggy' to begin with. And depending upon which
'flavor' of Linux this is it could be platform-specific, as I said
earlier.

There's not much more I can tell you. Well, except that I hope your
brick is fairly soft. :)


David Fitzjarrell

.

Relevant Pages

  • Re: Cache Hit Ratio from system views
    ... We are working on a simple alert like when ... the cache hit ratio falls below a given threshold. ... with a query that uses the V$BUFFER_POOL_STATISTICS view. ...
    (comp.databases.oracle.server)
  • Re: Cache Hit Ratio from system views
    ... We are working on a simple alert like when ... the cache hit ratio falls below a given threshold. ... with a query that uses the V$BUFFER_POOL_STATISTICS view. ...
    (comp.databases.oracle.server)
  • Re: Cache Hit Ratio from system views
    ... We are working on a simple alert like when ... the cache hit ratio falls below a given threshold. ... with a query that uses the V$BUFFER_POOL_STATISTICS view. ...
    (comp.databases.oracle.server)
  • Re: Cache Hit Ratio from system views
    ... We are working on a simple alert like when ... the cache hit ratio falls below a given threshold. ... with a query that uses the V$BUFFER_POOL_STATISTICS view. ...
    (comp.databases.oracle.server)
  • Re: Cache Hit Ratio from system views
    ... the cache hit ratio falls below a given threshold. ... with a query that uses the V$BUFFER_POOL_STATISTICS view. ... Metalink reports the view was buggy up until 9.2.0.2, ...
    (comp.databases.oracle.server)