Re: Cache Hit Ratio from system views

"Richard Foote" <richard.foote_at_nospam.bigpond.com> wrote in message news:fgixi.22091$4A1.5979_at_news-server.bigpond.net.au...
>
> "Bob Jones" <email_at_me.not> wrote in message
> news:eB8xi.1326$i75.244_at_newssvr19.news.prodigy.net...
>>>> Why is BHCR meaningless? The answer should be short and simple. I want
>>>> to hear your opinion.
>>>
>>> One can not prove a negative.
>>> Where is your proof BCHR is a reliable indicator of GOOD performance?
>>
>> BCHR alone does not tell you about overall performance. It simply tell
>> you the disk I/O percentage. It is an indicator, a very meaningful one.
>>
>
> If your "disk I/O percentage" is really really high, what does that
> actually indicate ? Does it indicate all is well with the database or does
> it indicate all might not be well ? If you have SQL nasties that use index
> scans inappropriately or incorrectly loop through full scans of cached
> tables again and again and again, you might have users experiencing
> extremely poor response times. Or you might have users that are happy with
> their instant response times. You can't really tell and so it doesn't
> really give you much of an indicator.
>
> If your "disk I/O percentage" is really really low, what does that
> actually indicate ? Does it indicate all is well with the database or does
> it indicate all might not be well ? It might indicate SQL nasties that use
> index scans inappropriately or incorrectly loop through full scans of
> tables (both large or small) and have users experiencing extremely poor
> response times. Or you might have users that are happy with their instant
> response times as all their online transactions run instantaneously
> because the various large batch reports that are running and causing all
> the high "disk I.O percentage" don't directly impact them at all. Just the
> BCHR ...
>
> Sometimes when the BCHR changes from one level to another, it might mean
> there's an issue. Sometimes it doesn't.
>
> The one constant though is that when there are performance issues,
> response times suffer for those folk/processes experiencing the
> performance issues. That can happen if the BCHR is low or high. And the
> actual cause of a performance issue needs to be investigated whether the
> BCHR is high or low to determine an appropriate fix for the issue.
>
> Now if there are performance issues relating to excessive "disk I/O
> percentage" bottlenecks for SQLs that are efficient either in terms of LIO
> counts or execution counts, then an increase in memory might be a
> reasonable cause of action. However, that requires looking at the cause of
> the issue, not the possible symptoms.
>
> Therefore the best indicator, the most meaningful one, is whether response
> times are meeting business requirements or not. And if not why not,
> regardless of the BCHR because a low or high BCHR may or may not be
> contributing to the problem. If response times do meet business
> requirements, then who really cares what the BCHR might be ?
>

If that's the case, we don't really need to care about any indicator. Your argument is basically the same as others here. Please read my earlier postings. Received on Tue Aug 21 2007 - 00:19:02 CDT