Re: Got the darn buffer busy waits under control, at last!

In article <3D0DE9B7.983ABCD2_at_more.net>, you said (and I quote):
> suppose you can do a victory dance here. On the other hand, what do you
> users say?

As usual, they say everything is slow. ;-) They've been saying that for the last two years. Never mind they're running three times more programs with four times the amount of data in the same hardware. And somehow, the end of month processing now comes out in one morning. Although when it all started they took one week to produce it. Details, I'm sure.

> Did they notice a marked improvement in performance? No, they
> could not have noticed because the estat reports number show that latch
> free waits are relatively meaningless in your instance.

If you say so. :-)
However, quite a few were very happy.

Bear in mind: this is a Peopleslop application. Have yet to see ONE site that works as fast as they advertise. And have seen many companies over the years who "specialize" in reducing problems in their installations.

Mark Gurry has even got an entire section devoted to it in his latest pocket book and another in his site. We are probably all imagining things and Peopleslop are right: there is no performance problem with their software and never was, it's all a fabrication by greedy consultants.

> begin tuning. If it happens to be SQL, so be it. And once you have gone
> through several iterations of SQL tuning, the wait event patterns may
> change and then change back again as you suggest. Still, the worst
> bottleneck remains the top non-idle wait event.

I hear you and understand the point. I also have seen over the years all sorts of tuning approaches and methodologies. Some even from Oracle themselves, during the Premium Services era. Have yet to find one that works (produces results fast) in EVERY single case. All of them produce results in the end, some are faster than others. It all depends on the conditions.

> The bottleneck is simply the point of contention, caused by something.
> If that cause turns out to be yet more bad sql, so be it.

Exactly. What we have to make sure to start with is what we're measuring is the bottleneck that we intend to tune. That is not always easy.

For example, take this from Toad's (old version) "tuning" stuff:

Library Cache Pin Hit Ratio: 94.9 Comment: shared pool area too small Parse to Execute Ratio (another ratio...): 47.35 Comment: High parse to execute ratio.

Yet, no indication of how long the sample was taken for. Or even what period it was for. Or if one "problem" was not caused by the other. Or even if the code to fix this (it is after all an application "problem"...) is available to the common mortal. Or even (heaven forbid!) if increasing the shared pool area is not gonna cause paging?

It happened to be for 10 seconds and was not repeatable. But all it takes is a "tuner" to come up with this out of context "example" in his/her screen and claim that "nothing is tuned" and management will latch on to it like a limpet to a rock. Some things will never change.

Sometimes I wish tools like these were forbidden once and for all. They cause a lot more damage than they help. Spotlight is quite good, but the tuning stuff in Toad is just plain crazy nowadays. Never mind, I was just using this here as an example of out of context "tuning" info that may indeed cause more damage than it fixes.

> In the Oracle instance, the bottleneck is measured by wait time. So, if
> some covert problem is really latch contention rather than
> IO-consumptive SQL, then the wait events will show "latch free" as a top
> wait event. Plain and simple.

That would appear indeed to be the case. Except when we have from "sar" AND "glance" that I/O is really not an issue. What then?

> Now, it may be that some factors are not subject to change. Perhaps a
> third party application cannot be modified or you have some recalcitrant
> in house developers who refuse to modify SQL.

NOW, you're talking! For example: dynamic SQL (with no binding whatsoever) generated by a "compiled" third party AI application. Impossible to do anything about it.

> In such a case, the next
> best thing is to look for the next worst bottleneck.

Problem is when the first bottleneck masks what the next one really is.

> than that is no better than chasing your tail. Sure, you may be able to
> gather some data to "prove" you improved performance, but if the folks
> paying the tab don't notice it, I suspect the effort is wasted.

That and the fact that some pre-identified SQL which caused prior problems now runs faster than before? I mean: in the absence of any accurate application specific metrics (where are they, Peopleslop???), the only thing we can really do is keep an history of previously problematic SQL and re-run it after every change.

And watch the overall Unix load indicators, of course. If that SQL now runs in much less time and there is no increase in either CPU or I/O, what other proof do we need that improvements are indeed taking place?

> Regarding spin count and other "underscore" parameters,

Whooooaaaaa! Spin count is MOST definitely NOT and underscore parameter in 8.0.6!!! If it was, it's most likely I wouldn't be touching it. Even if I'm known not to be shy of the _.

> the admonishment
> against hip-shooting modifications is not a matter of polical
> correctness. There is real danger in taking on such tasks. Perhaps in
> your case it will all work out fine, but this may not be the case in the
> future.

Absolutely in agreement.

>
> So, proceed with due caution.
>

Without a doubt, chief. And once again, thanks for your feedback. It is much appreciated, even though we may not agree in all facets. It is always a pleasure to have a chat with people in here about performance problems. I'm a firm believer in discussing these things openly, as you know.

Heck, no other way I know of to improve one's knowledge. Oh yes, I forgot: there is always the OCP...

-- 
Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam