Re: ASSM & buffer busy waits ?

Vivek,

BBW occur due to several reasons, one of them being the lack of freelist contention. However, the primary factor of BBW is the fact that two sessions try to get a single block to the db_block_buffers at the same time. The chance that this happens increases if a block contains several rows as opposed to a few rows. BBWs are inevitable, you can't eliminate them; but you can reduce the occurence by reducing the probablility of sessions trying to grab rows in the same block at the same time. They can be done using

(i) reducing the packing factor - increasing the PCT* params, INITRANS, etc.
(ii) reducing the block size
(iii) in some cases, using reverse key indexes, etc.

ASSM simply corrects the freelist contention, but does not rectify the packing factors; so the basic problem is still the same. Please read a treatise on the topic at http://www.dbazine.com/nanda1.html for a better discussion.

Hope this helps.

Arup Nanda
www.proligence.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, April 16, 2003 4:14 PM

>
> 1) Are there any known performance issues with Automatic Segment Space
Management (ASSM) Tablespaces on Single instance Database like enqueue locking ?
>
> 2) Is buffer busy waits TOTALLY eliminated when using ASSM ?
> Can buffer busy waits wait occur for reasons OTHER than contention for
freelists , PCTUSED , PCTFREE ?
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: VIVEK_SHARMA
> INET: VIVEK_SHARMA_at_infosys.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: orarup_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).