Path: g2news2.google.com!postnews.google.com!57g2000hsv.googlegroups.com!not-for-mail
From:  "fitzjarr...@cox.net" <fitzjarr...@cox.net>
Newsgroups: comp.databases.oracle.server
Subject: Re: Cache Hit Ratio from system views
Date: Wed, 08 Aug 2007 11:21:14 -0700
Organization: http://groups.google.com
Lines: 104
Message-ID: <1186597274.626919.244240@57g2000hsv.googlegroups.com>
References: <1186588653.996873.185140@l70g2000hse.googlegroups.com>
   <1186590153.654630.291560@19g2000hsx.googlegroups.com>
   <1186590619.940930.167770@22g2000hsm.googlegroups.com>
   <1186592029.331221.54360@57g2000hsv.googlegroups.com>
   <1186594560.682120.99610@r34g2000hsd.googlegroups.com>
NNTP-Posting-Host: 138.32.32.166
Mime-Version: 1.0
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)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 8 Aug 2007 18:21:14 +0000 (UTC)
In-Reply-To: <1186594560.682120.99610@r34g2000hsd.googlegroups.com>
User-Agent: G2/1.0
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)
Complaints-To: groups-abuse@google.com
Injection-Info: 57g2000hsv.googlegroups.com; posting-host=138.32.32.166;
   posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0

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.
>
> 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