RE: ASSM & buffer busy waits ?

The other nifty trick I've seen to minimize hits on the same block is to use a neat little trick with MINIMIZE RECORDS_PER_BLOCK. Basically, you create a new copy of the table, and populate it with a few token rows (up to the maximum number of rows wanted in each block). Then use the ALTER TABLE <table_name> MINIMIZE RECORDS_PER_BLOCK command. This will force Oracle to search the existing blocks for the table to determine the maximum number of rows in a block, and keep that information in the data dictionary (it is actually kept in encoded form in the SPARE1 column of TAB$). From then on, Oracle will try to ensure that blocks will not have more rows than the maximum it determined when the ALTER TABLE command was issued. You can now delete the token rows and reinsert the original table rows (using a parallel insert operation if speed is required).

One ramification of reducing data density by ANY of the means mentioned above or in Arup's reply is that more memory will be needed to cache the same number of rows, since the rows now use more blocks. This reduces the amount of the database buffer cache that is available for other data, and can have a negative performance impact that outweighs the benefit of eliminating the buffer busy waits. This needs to be considered before taking this approach to reduce data density. Fixing the application to remove the hot blocks is clearly a preferable approach.

Pete
"Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook
"Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA  

-----Original Message-----
Sent: Thursday, April 17, 2003 8:37 AM
To: Multiple recipients of list ORACLE-L

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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Pete Sharman
  INET: peter.sharman_at_oracle.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).