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: Tue, 18 Jun 2002 13:37:30 GMT
Message-ID: <3D0F381D.C4255702@more.net>


You make an important point here, that bottlenecks are a matter of contention for resources between sessions. Certainly the user suffering is not necessarily the user causing the problem. MS SQL Server users witness this daily as OLTP activity comes to a halt when someone decides to run a report and that single reader blocks all writers.

On the other hand, observing bottlenecks and relieving them is the *only* logical way to improve performance of any complex queueing system. Oracle is no exception to queueing theory principles, merely a particularly complex example.

So, I think it is an overstatement to describe this method as defective. You mentioned elsewhere you don't much care for tools like Statspack. That's too bad, really. Statspack is the only reliable tool I have found that will isolate the worst bottleneck in an Oracle instance pretty much at a glance. Certainly more drill down is typically necessary to find the root cause of something like "buffer busy" conditions, but the purpose of Statspack is to guide you directly to that effort, and does so nicely. No point stabbing at the library cache if the buffer cache is the culprit.

It would be great if some single-button tool would take the next steps for us and isolate the root cause and fix it automatically, but the fact is it still takes knowledge of the underlying processes and structures, as well as the overarching applications and business processes, to make this product hum. The requirement of human expertise will never go away.

Nevertheless, as you point out it is not magic at all, simply logic. Find the worst bottleneck and relieve it, as simple as that. You can find it by guesswork, divination, chance or deduction. No matter the path, that bottleneck must eventually be found and relieved to obtain optimal performance. The advantage of the method to which I refer is only that it gets you there most quickly and most reliably.

Jonathan Lewis wrote:
>
> It is a defect (perhaps too strong a word) of the nice, clean,
> simple, strategy of tuning by bottlenecks that the bottleneck
> is sometimes easy to see, but the cause of the bottleneck is
> somewhere completely different. It's not magic, of course, but
> it is the point where you may have to change from a simple
> procedure to in-depth knowledge of Oracle, the application and
> the business.
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminars
> UK June / July
> Australia July / August
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Nuno Souto wrote in message
> <3d0f0d8c$0$28009$afc38c87_at_news.optusnet.com.au>...
>
> >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.
> >
Received on Tue Jun 18 2002 - 08:37:30 CDT

Original text of this message

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