'buffer busy waits' on Header Block (#2) of Tempfile
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 28 Apr 2008 20:36:00 +0800
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
Other related posts:
