Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: ASSM & buffer busy waits ?

RE: ASSM & buffer busy waits ?

From: Pete Sharman <peter.sharman_at_oracle.com>
Date: Mon, 21 Apr 2003 09:46:41 -0800
Message-ID: <F001.00585F80.20030421094641@fatcity.com>


Sorry for the delay in responding. It's been, as always, an incredibly busy and tiring Easter (as anyone who's been in a church choir over that period could testify!) - thank God it's over! :)

I'm just getting back to email from Friday, so you can imagine how many Oracle-l mails there were!

I suppose the answer to your question is that using MINIMIZE_RECORDS_PER_BLOCK gives you a bit more accuracy (provided the token rows you entered are typical of the rows that will end up in the table). Setting PCTFREE / PCTUSED is a much more broad brush 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: Friday, April 18, 2003 12:22 AM
To: Multiple recipients of list ORACLE-L

Hi Pete

How is this Different from Setting a HIGH PCTFREE for the Table & Loading data thereafter into it ?

Thanks

-----Original Message-----
Sent: Thursday, April 17, 2003 10:42 PM
To: Multiple recipients of list ORACLE-L

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

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

-- 
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: 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).
Received on Mon Apr 21 2003 - 12:46:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US