Message from discussion Cache Hit Ratio from system views
From: "fitzjarr...@cox.net" <fitzjarr...@cox.net>
Subject: Re: Cache Hit Ratio from system views
Date: Wed, 08 Aug 2007 11:21:14 -0700
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1186597274 20650 127.0.0.1 (8 Aug 2007 18:21:14 GMT)
NNTP-Posting-Date: Wed, 8 Aug 2007 18:21:14 +0000 (UTC)
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) ; Embedded Web Browser from: http://bsalsa.com/; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)
Injection-Info: 57g2000hsv.googlegroups.com; posting-host=22.214.171.124;
On Aug 8, 12:36 pm, Teresa Masino <teresa.mas...@peninsula.org> wrote:
> On Aug 8, 12:53 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
> > On Aug 8, 11:30 am, Teresa Masino <teresa.mas...@peninsula.org> wrote:
> > > On Aug 8, 12:22 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
> > > > On Aug 8, 10:57 am, Teresa Masino <teresa.mas...@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.
> Teresa- Hide quoted text -
> - Show quoted text -
Metalink reports the view was buggy up until 126.96.36.199, however it may
be a platform-specific issue. On which O/S are you running Oracle?