RE: 'buffer busy waits' on Header Block (#2) of Tempfile
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <hkchital@xxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 28 Apr 2008 09:34:56 -0400
Since you're apparently competing for allocation within a single temporary
tablespace, it is likely that one of the variety of having multiple
temporary tablespaces will help you.
Adding multiple tablespaces to a tablespace group and using the tablespace
group for the temporary tablespace is the probably the simplest thing to try
first if you're up to a release that supports it. (That's in the ALTER USER
and ALTER tablespace bits of the documentation). If you're using a BORING
allocation rather than SAME, you might put the temporary tablespaces that
are components of the tablespace group on different stripesets to fan out
the i/o.
If either that does not reduce the wait or you're not up to that level, then
you could consider creating multiple tablespaces and statistically assigning
the temporary tablespace to be used in a logon trigger to load level your
users, or just use a fixed distribution of users to tablespaces if that is
an even enough distribution of users per temporary tablespace at run time.
Of course if you have sufficient memory to reduce the need to use temporary
tablespace i/o you could try that. Often a pretty reasonable upward bump in
PGA_AGGREGATE_TARGET dramatically reduces i/o.
Another thing is to examine your extent size. A large uniform extent size
will tend to mean fewer allocations take place. Usually disk acreage for
temp is not that much of a concern, since disk SPACE is cheap and you don't
have to back up temp space.
I'd probably evaluate using additional memory first, but if you have the
acreage available there is little or no down side to adding more
tablespaces.
Good luck!
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Hemant K Chitale
Sent: Monday, April 28, 2008 8:36 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: 'buffer busy waits' on Header Block (#2) of Tempfile
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 HASH_AREA_SIZE.
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
http://hemantoracledba.blogspot.com
"A 'No' uttered from the deepest conviction is better than a 'Yes'
merely uttered to please, or worse, to avoid trouble."
Mohandas Gandhi Quotes
: http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
