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

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
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 ?

  1. 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.
  2. 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
: http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html

Received on Mon Apr 28 2008 - 07:36:00 CDT

Original text of this message