Re: Cache Hit Ratio from system views

"Bob Jones" <email_at_me.not> wrote in message news:JJYAi.30814$RX.20623_at_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.
>

Unfortunately Bob, I have read all the thread and nowhere do you give any indication on why you would consider the BCHR to be a "very meaningful indicator" when it might not not change at all, currently 95% and still 95% while the database grinds to a halt ...

How can this be very meaningful ?

>> 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.

Answer the questions in my other post Bob and lets see who has their ears blocked.

I have read all your contributions Bob, I really have and not once have you actually justified why the BCHR is such a meaningful indicator, nowhere have you mentioned what these other indicators are that you need to use in conjunction with the BCHR, nowhere have you mentioned how your actions and checks vary if the BCHR increases, decreases or remains the same, nowhere have you actually described what all these things are that must be equal for a higher BCHR to always be better.

Oh you've made a lot of claims, but you haven't actually addressed and justified them.

Go on Bob, answer the questions, dare ya !!

If you can ...

Oh and Bob, humour me if you would. You don't by any chance routinely rebuild all your indexes to make your databases run heaps faster you ? You strike me as the kinda guy who rebuilds indexes that have a height greater than 3 or have more than 20% deleted space.

Do you ?

Cheers

Richard Received on Wed Aug 29 2007 - 08:08:21 CDT