Re: DB Cache Hit Rate > 100%

Fred <noway_at_jose.com> wrote in message news:<noway-CFC9A5.10354101092004_at_news101.his.com>...
> In article <4135cda9$0$5288$afc38c87_at_news.optusnet.com.au>,
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote:
>
> > Fred wrote:
> >
> > > It's early morning and I haven't had my usual dose of caffeine.
> > >
> > > A client is reporting their DB buffer cache is experiencing a hit rate
> > > of 156%. Normally, I wouldn't be alarmed by this bit of spurious data,
> > > but I would like to be able to explain to the customer how this might
> > > occur. Anyone?
> > >
> > > Fred
> >
> > What formula are they using to calculate that?
> >
> > It's a bit meaningless otherwise.
> >
> > I've never seen a hit ratio greater than 100% except when I attempt to
> > calculate it using mental arithmetic alone.
> >
> > Regards
> > HJR
>
> As best I can tell, the formula is simply:
>
> 1 - physical_reads / (consistent_gets + db_block_gets)
>
> The user is using a monitoring tool: VERITAS Savant, and the 156% hit
> rate is showing on the screen.
>
> Like you, I have never seen a hit ratio > 100%, but I have seen cases
> where there is data corruption in the V$ views in long-running instances
> (this was noted in MetaLink a while back). I wonder if this might be the
> case?
>
> Fred

Also depending on the version involved until recently Oracle used 32 bit integers to hold its statistics so depending on how long the database has been up and how heavy a work load it supports it may be that the value overflowed (postive to negative and back again). Events like this can create some pretty strange results. Just last week I saw a post on metalink where someone posted a bug number related to Oracle incorrectly calculating one or two of its statistics. But I am pretty sure that the referenced statistic would not affect the BCHR. Still problems with statistics do pop up from time to time and if it is important a search on metalink might be necessary.

I would use the obviously bogus value as a means to point out how little weight should be given to tuning with individual ratios rather than using specific task run times and complete statspack reports.

HTH -- Mark D Powell -- Received on Wed Sep 01 2004 - 16:27:51 CDT