Re: Cache Hit Ratio from system views

On Aug 8, 1:32 pm, Teresa Masino <teresa.mas..._at_peninsula.org> wrote:
> On Aug 8, 2:21 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > On Aug 8, 12:36 pm, Teresa Masino <teresa.mas..._at_peninsula.org> wrote:
>
> > > On Aug 8, 12:53 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > > On Aug 8, 11:30 am, Teresa Masino <teresa.mas..._at_peninsula.org> wrote:
>
> > > > > On Aug 8, 12:22 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > > > > 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- 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 Received on Wed Aug 08 2007 - 14:53:59 CDT