Re: 'buffer busy waits' on Header Block (#2) of Tempfile
- From: "goran bogdanovic" <goran00@xxxxxxxxx>
- To: hkchital@xxxxxxxxxxxxxx
- Date: Mon, 28 Apr 2008 15:35:57 +0200
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 <hkchital@xxxxxxxxxxxxxx>
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
> 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:
