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

Comments in-line.

Nuno Souto wrote:

> As usual, they say everything is slow. ;-)
...
> However, quite a few were very happy.

Sounds like a contradiction to me. Do they perceive improvement because of the spin count tuning or not?

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

Actually, this methodology is from Oracle. It works in ever single case and finds the worst bottleneck directly and quickly, without guesswork.

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

Ratio tuning like this is nonsense, of course. I think you already know this.

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

sar and the like are excellent tools for examining the underlying platform. Unfortunately sar is not consistent across all operating systems. Nevertheless, it is generally useful. The trick is to avoid misinterpreting its output. For example, many people look at cpu load or the ratio of user to system cpu, without looking at process run queues. The process run queue, averaged per cpu, will tell you right away if you are cpu-bound. Similar information is offered for IO.

The ratios don't really illustrate this. And, don't forget to evaluate variance - don't just peek at a single snap. Plus, the report must be from the same busy period when the "problem" occurs. Lots of people fail to follow these rules and end up with garbage.

There is a new white paper on Metalink that describes this method and illustrates operating system performance analysis. Look under "top tech docs", "performance", "database", "tuning methodology..." and it should be about the first one listed. COE Performance Method.

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

Right, I forgot that. It was changed in 8i for exactly this reason.

• ricky