Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Got the darn buffer busy waits under control, at last!

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

From: Ricky Sanchez <rsanchez_at_more.net>
Date: Mon, 17 Jun 2002 13:50:51 GMT
Message-ID: <3D0DE9B7.983ABCD2@more.net>


Nuno-

Well, if your Service Level Agreement is with your "log files", then I suppose you can do a victory dance here. On the other hand, what do you users say? 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.

By definition, the worst bottleneck is in fact the most logical place to 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.

Your phrase, "...bottlenecks can easily be masked behind other causes." illustrates the confusion. Bottlenecks are not hidden by "other causes". 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.

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.

So, the find-the-worst-bottleneck-and-relieve-it approach is truly the best. Why? Because it is logical.

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. In such a case, the next best thing is to look for the next worst bottleneck. Any effort other 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.

Regarding spin count and other "underscore" parameters, 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. The spin count parameter affects all latches equally, as they are obtained in a willing-to-wait manner. As other aspects of your instance change - say, for example, you do tune some sql and relieve a "worse" bottleneck - the latch behaviour might suddenly become problematic. It may seem hard to imagine right now, but it happens all the time.

So, proceed with due caution.

"Zo(t)" wrote:
>
> Hmmm sorry, I disagree. The log files show clearly
> there is a marked improvement overall in most buffer
> related latches. And in quite a few of the stats. Since
Received on Mon Jun 17 2002 - 08:50:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US