Path: text.usenetserver.com!out03a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!r34g2000hsd.googlegroups.com!not-for-mail From: Teresa Masino Newsgroups: comp.databases.oracle.server Subject: Re: Cache Hit Ratio from system views Date: Wed, 08 Aug 2007 10:36:00 -0700 Organization: http://groups.google.com Lines: 96 Message-ID: <1186594560.682120.99610@r34g2000hsd.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> NNTP-Posting-Host: 168.67.1.19 Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Trace: posting.google.com 1186594560 3471 127.0.0.1 (8 Aug 2007 17:36:00 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 8 Aug 2007 17:36:00 +0000 (UTC) In-Reply-To: <1186592029.331221.54360@57g2000hsv.googlegroups.com> User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: r34g2000hsd.googlegroups.com; posting-host=168.67.1.19; posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0 Xref: usenetserver.com comp.databases.oracle.server:432655 X-Received-Date: Wed, 08 Aug 2007 13:36:00 EDT (text.usenetserver.com) On Aug 8, 12:53 pm, "fitzjarr...@cox.net" wrote: > On Aug 8, 11:30 am, Teresa Masino wrote: > > > > > > > On Aug 8, 12:22 pm, "fitzjarr...@cox.net" wrote: > > > > On Aug 8, 10:57 am, Teresa Masino 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