Re: Oracle slowed down

Bob Jones wrote:
> > Don't agree - Bob *knows* a BCHR of 30% is bad - so he must
> > also know whether 85% is good or bad.
> >
>
> Frank, there is no single performance stat that can tell you the overall
> performance is good or bad, including the "waits" you talked about. Are they
> all irrelevant?
>
> > I know it means nothing, but Bob seems to think otherwise, but
> > cannot explain that to the 6-year old I sometimes am. Like now
> >
>
> Of course, it means something.
>
> Here is a simple example. Let's say
>
> x + y = z
>
> The value of x alone does not determine the value of z, because z also
> depends on y. Can we say x is IRRELEVANT to z?
>
> BCHR alone does not determine performance, but it is a performance factor,
> therefore relevant.

It is not a performance factor, but a derivative ratio. Like dividing x, y and z each by w, w is irrelevant to z.

>
> BCHR is placed in the main page of Performance Manager. I don't think Oracle
> made a mistake on that one.

I think they did. If you look at Buffer Cache Size Advice, you'll notice they say "Relative Change in Physical Reads." But people refer to it as BCHR out of habit, I guess (for example, p.l 114 of Niemec's Oracle 9i Performance Tuning). If you follow the advice, often you find that the next time it then tells you to increase your buffers, and so on and so on. If you watch what you are doing, you may notice that it is getting smaller and smaller percentage increases. That's fine, since you can ignore the advice, until you try to automate the tuning, then you can get stupid tuning if the app code isn't perfect. Then you get weird bugs like Oracle insisting on making the shared pool larger because there are too many unique SQL's that should have been bound, which slows things down because it takes so long for Oracle to scan the pool to see if the SQL is there... so the correct response would have been to make the pool smaller. Where did I see that? Anyways, BCHR on Performance Manager doesn't mean BCHR is an important or good thing, it just means old myths die hard.

jg

-- 
@home.com is bogus.
http://www.eugenemirman.com/