RE: 'buffer busy waits' on Header Block (#2) of Tempfile

From: Tim Gorman <>
Date: Mon, 28 Apr 2008 11:26:06 -0600
Message-ID: <>


Are we "violently disagreeing" on the same things? I'm a little confused as to the violence of your disagreement, as it seems more tepid than you characterize in that first sentence. Can you clarify? After all, there are alcoholic beverages at stake here.... ;-)

I don't want to go too far into this medical analogy -- I had intended to use it to clarify things, not to muddy the issue at hand. Let's drop it before it goes too far...

You appear to be advocating the view that something is wrong with the temp tablespace and/or the PGA management, based solely on the fact that this is a new system without prior history. While it is a possibility, I would say that it is far more likely that something is wrong or missing with the application code.

How does that fact (i.e. new system) argue against a problem in the application not the database -- such as a missing or malformed index? The "newness" of the application environment makes such a simple oversight at the application coding level even more compelling, to my mind.

Also, how does the effort of understanding what is happening in the application code by simply looking at the culprit SQL as suggested by Stephane? At the risk of continuing the medical analogy (sorry!), I see that as deciding upon minor surgery prior to completing the admissions intake form. Surely, without knowledge of the SQL being executed by these hundreds of sessions, it is premature to recommend *any* solution whatsoever?


P.S. I'm partial to Patron anejo, myself....

Quoting "Mark W. Farnham" <>:

> While I violently agree with your analysis in the general case, and because
> I have the same view of the outcome either way, I'll take that bet. This is
> a new system, so the existing plan is the only plan, not a new plan.
> Indeed, continuing your medical analogy, suggestions to alleviate the
> current systemic bottleneck should have the immediate impact of keeping the
> patient alive at a higher functioning state while a cure is sought for the
> underlying problem. So even if the "allopathic" course makes the system
> appear healthy, the "homeopathic" followup should be pursued.
> Slapping in the parallelism and reduction in frequency of allocation fixes
> in this case are likely cheap and effective to at least improve the
> throughput. Again, since the system is new, and has apparently only been
> subjected to high current load with the "go-live" process is seems quite
> likely to me that a fair number of commonly constructed queries may be
> spawning large hash plans and/or sorts, so it is less likely the real fix
> will be a quick fix of a single query. (Of course you're right that one
> missing index or a poorly-conceived index could generate a lot of problems.
> It is also entirely possible we're seeing a ramp up in data volume of a new
> system where the nearly empty stats generated all manner of fts/hash plans
> that will become good plans as soon as the stats are updated, and the former
> tiny in memory hashes and sorts flashed by but now dump to disk.)
> So take the two aspirin (for the pain) and see if you can't find a way to
> stop hitting your thumb with the hammer. Now if it wasn't the rough
> equivalent of two aspirin (as well as being responsive to the OP's actual
> question), but rather was an expensive thing to relieve the symptoms while
> he searches for the real cure, I'd vote for some interim load control while
> the underlying cause(s) is/are cured. But spreading out temp access and
> reducing the allocation frequency should be cheap and relatively effective.
> ... just don't stop there.
> :) mwf (Where am I buying the 1st or 2nd round of drinks where you're buying
> the 2nd or 1st round of drinks?)
> -----Original Message-----
> From: []
> On Behalf Of Tim Gorman
> Sent: Monday, April 28, 2008 11:31 AM
> To:
> Cc:;
> Subject: Re: 'buffer busy waits' on Header Block (#2) of Tempfile
> I agree with Stephane's response in principal as well as specifics.
> Responses on this thread have fallen into one of two camps:
> 1. address the symptom as described
> 2. find out more about what is going on, because the
> observed effect may be based on an anomaly in the application
> In the medical arena, course #1 can be described as "allopathic" while
> course #2 can be described as "homeopathic". On,
> "homeopathy" is defined as "the method of treating disease by drugs,
> given in minute doses, that would produce in a healthy person symptoms
> similar to those of the disease", while "allopathy" is described as
> "the method of treating disease by the use of agents that produce
> effects different from those of the disease treated". In essence,
> homeopathy is the more considered response (i.e. "why is symptom XYZ
> happening"), while allopathy is the knee-jerk response (i.e. "treat
> symptom XYZ with response ABC"). Each has their appropriate
> application in medicine, but in database and application tuning, I
> would argue that the homeopathic course is more effective in the
> larger number of cases. Find out what is going on, why so much TEMP
> space is being demanded, because the reason could be something simple
> like a missing or poorly formed index.
> In fact, I will gladly bet a round of drinks that the problem is a new
> execution plan recently adopted by the CBO or a missing index, or a
> poorly-conceived index, or a combination of all three, and has nothing
> to do with the configuration or implementation of the temporary
> tablespaces, nor does it have anything to do with PGA configuration.
> ...I'm glad to be wrong as well as right, because either outcome still
> results in a round of drinks...
> Any takers?
> Quoting goran bogdanovic <>:
>> Hi,
>> Since your contention is on header block, increasing extent size from 256k
>> to 1mb sound reasonably to me.
>> One of the reasons why processes checks the segment header is to extend
> the
>> HWM.
>> Have you found which queries are consuming much of the temp space? Can
> they
>> be optimized?
>> regards,
>> goran
>> On Mon, Apr 28, 2008 at 2:36 PM, Hemant K Chitale
> <>
>> wrote:
>>> We'd rolled out a new system today and user connections rapidly
>>> went up to 900 sessions. OK, we are supposed to be able to handle that.
>>> However, by the afternoon, I saw 'buffer busy waits' on Block#2 of the
>>> first tempfile of the Temporary Tablespace (using a custom Temporary
>>> tablespace instead of 'TEMP', although the default 'TEMP' exists).
>>> By evening, we had 300 sessions waiting on 'buffer busy waits' on the
> same
>>> block
>>> (querying V$SESSION_WAIT for P1, P2).
>>> Although the tablespace has 3 tempfiles, the first file has 350 sessions
>>> against it and the other two have less than 100 sesssions put together
>>> (querying V$TEMPSEG_USAGE for SEGFILE#).
>>> These are a mix of SORT and HASH extents.
>>> How can I address this ?
>>> a. Increase PGA_AGGREGATE_TARGET (1GB for 400 concurrent users + 10-15
>>> batch jobs)
>>> and/or switch to WORKAREA_SIZE_POLICY='MANUAL' with SORT_AREA_SIZE and
>>> b. Add more tempfiles to the tablespace and/or rebuild the tablespace
> with
>>> larger
>>> Extent Sizes (1MB instead of the current value of 256K)
>>> Any other suggestions ?
>>> Hemant K Chitale
>>> "A 'No' uttered from the deepest conviction is better than a 'Yes' merely
>>> uttered to please, or worse, to avoid trouble."
>>> Mohandas Gandhi Quotes :
>>> --
> --

Received on Mon Apr 28 2008 - 12:26:06 CDT

Original text of this message