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

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Mon, 28 Apr 2008 15:08:19 +0200
Message-ID: <4815CC43.4000708@roughsea.com>

Hemant,

    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

/
clear col
clear breaks

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.

Stéphane Faroult

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
> 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-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 28 2008 - 08:08:19 CDT