Re: Cache Hit Ratio from system views

On 1 Sep, 01:29, "Bob Jones" <em..._at_me.not> wrote:
> > If the BCHR is an objective indicator, please tell us of what you think
> > it is an indicator, and how - specifically you would use it. Richard's
> > indicator (as mine) is how long business transaction take. Definitely
> > objective and relatively straightforward to use and understand.
>
> BHCR shows disk i/o percentage. An important indicator for buffer cache
> tuning.

So how exactly would you use it? If BCHR falls below a certain value increase one or more of the pool sizes? If it rises above a certain value reduce them?

> As I said before, response time is not even a database metric.

well no it isn't, it's a metric for the measurement of the performance of the application as experienced by the end-user. Round in my part of the world we install and use applications to support line of business transactions and it is those installations that we care about rather than the individual component. That's the primary reason that a metric derived from business usage makes most sense to me.

>It is an effect not a cause.

That's true enough, but breaking down the response time into it's component parts, say

      Time spent at web server
      Time spent in application server
      Time spent on network
      Time spent in DB.

At a gross level gives you a much better tuning methodology than requests satisifed from cache for any given component of the stack. Producing an execution profile for the objects of interest really shouldn't be news given that it was first described and demonstrated by Knuth when I was 4 (and I'm ten times that age this year). The more each of those tiers allow you to break down the response time components the better you chance of finding the problems.

> With some people's logic here, even response time is
> meaningless. 5 minutes response time can be better than 5 seconds response
> time, if it is doing 5000 times more work.

That would likely depend on your requirements, in Richard's ATM analogy it would seem unlikely that many banks would prioritise keeping all their customers waiting 5 minutes to withdraw cash over allowing a smaller subset to be happy with their 5 second response. On the other hand if the per customer end of month statement takes 5 seconds, but the batch process for 5000 takes 5 minutes then I know which I'd be scheduling in my month end process. Received on Mon Sep 03 2007 - 04:19:28 CDT