Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Cache Hit Ratio from system views

Re: Cache Hit Ratio from system views

From: Teresa Masino <teresa.masino_at_peninsula.org>
Date: Wed, 08 Aug 2007 11:32:29 -0700
Message-ID: <1186597949.960003.194730@w3g2000hsg.googlegroups.com>


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 Received on Wed Aug 08 2007 - 13:32:29 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US