Re: Cache Hit Ratio from system views

From: Bob Jones <email_at_me.not>
Date: Tue, 28 Aug 2007 17:14:17 GMT
Message-ID: <JJYAi.30814$RX.20623@newssvr11.news.prodigy.net>

"Richard Foote" <richard.foote_at_nospam.bigpond.com> wrote in message news:FyAAi.26736$4A1.1866_at_news-server.bigpond.net.au...
> "Bob Jones" <email_at_me.not> wrote in message
> news:eEnAi.234$ZA5.106_at_nlpi068.nbdc.sbc.com...

>>
>> "Richard Foote" <richard.foote_at_nospam.bigpond.com> wrote in message 
>> news:OGWyi.24448$4A1.10071_at_news-server.bigpond.net.au...
>>> "Bob Jones" <email_at_me.not> wrote in message 
>>> news:aBuyi.50201$YL5.11519_at_newssvr29.news.prodigy.net...
>>>>
>>>> "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.
>>>
>>> Correct, we don't really need to care about any indicator that's as 
>>> ambigious as the BCHR.
>>>
>>> However, response times is an idicator that isn't quite so ambigious and 
>>> hence is something you should care about ...
>>>
>>
>> So you consider repsonse time a metric collected by system? Ok.
>> What does 5 seconds response time tell you? What does 5 minutes response 
>> time tell you?
>

> Are you seriously suggesting having a banking transaction resulting in a
> customer waiting for 5 minutes doesn't tell you anything about your system
> ?

> Are you seriously suggesting that a BCHR that remains the same is a better
> and more "meaningful indicator" than a critical business response time
> that varies from 5 seconds (telling me in answer to your question that
> application users are happy) to 5 minutes (telling me users are not so
> happy) ?

>

No, all I am suggesting is to go back and read the thread again. You will find yourself completely out of the loop.

> Your "very meaningful indicator" hasn't budged at all (still sitting at
> 99%) but the application has ground to halt ...

> You remind me of someone who considered the health and well being of the
> Titanic to be based on the ratio of notes being played by the string
> quartet, all things being equal !!

>

Before trying to read my mind, please read the thread correctly first. Apparently some people here are debating with their ears blocked. Received on Tue Aug 28 2007 - 12:14:17 CDT