Re: 'buffer busy waits' on Header Block (#2) of Tempfile
Date: Mon, 28 Apr 2008 15:08:19 +0200
Have you any idea about the queries that are using temporary storage? A query such as this might help:
break on blocks on segtype
select s.BLOCKS, s.SEGTYPE, t.SQL_TEXT
from (select sum(blocks) blocks,
segtype, sqladdr, sqlhash from v$tempseg_usage group by sqladdr, sqlhash, segtype) s, v$sqltext t
where t.address = s.sqladdr
and t.hash_value = s.sqlhash
order by s.blocks desc, t.hash_value, t.piece
I find it surprising, on what is obviously an OLTP system, to find so many sessions performing sorts (or hash joins) on disk. It may be something as simple as a missing or poorly designed index.
Hemant K Chitale 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
> "A 'No' uttered from the deepest conviction is better than a 'Yes'
> merely uttered to please, or worse, to avoid trouble."
> Mohandas Gandhi Quotes :