'buffer busy waits' on Header Block (#2) of Tempfile
Date: Mon, 28 Apr 2008 20:36:00 +0800
Message-Id: <200804281236.m3SCaa4E007300@smtp14.singnet.com.sg>
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 ?
- 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.
- 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
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Apr 28 2008 - 07:36:00 CDT