Re: very low hit ratio

"JW" <jwu_at_nctr.fda.gov> wrote in message news:f4d9400.0403261321.445594ec_at_posting.google.com...
> Thank you all for offering your opinions. Users have not complained
> about the performance. I was just checking around to prevent any
> possible complaints. I guess I will just talk to users and developers
> and see how the application is accessing the database and then decide
> if the low hit ratio is supposed to be like that.

Concentrate on waits rather than ratios. Make sure you're not using bind variables (you're a data warehouse after all)
See whether you can reduce the number of physical reads by, for example, denormalising things (think materialised views, index clusters) or by partitioning things (assuming partitioning is an option). Assess whether the developers have correctly implemented 'exotic segments' such as Index Organised Tables or hash clusters or bitmap indexes. If 9i, see whether your developers have heard of bitmap join indexes. Depending on your version, you might be able to reduce physical reads by implementing index and/or table compression.

That sort of thing.

The one thing that's not in the list is 'worry about my hit ratio'.

Regards
HJR
>
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
news:<40622ec2$0$8357$afc38c87_at_news.optusnet.com.au>...
> > "JW" <jwu_at_nctr.fda.gov> wrote in message
> > news:f4d9400.0403241310.150b1fb4_at_posting.google.com...
> > > we have a data warehouse type of database and I noticed that buffer
> > > cache hit ratio is very low: db block gets 37,173,962; consistent gets
> > > 349,994,093; physical reads 888,341,652. Is this normal in a
> > > datawarehouse environment? Thanks.
> >
> > Who cares?
> >
> > Are your users complaining about performance?
> >
> > If not, then your hit ratio is just fine and dandy, whatever it happens
to
> > be.
> >
> > The more general point here is that, Don Burleson and TUSC
notwithstanding,
> > the buffer cache hit ratio is an absolutely abysmal way of tuning
anything.
> > It can sometimes offer a useful corollary to other statistics; to allow
you
> > to distinguish between two otherwise equally plausible causes for, for
> > example, free buffer waits. But as a performance tuning goal in its own
> > right? Furgedaboudit.
> >
> > Regards
> > HJR
Received on Fri Mar 26 2004 - 15:57:11 CST