Re: Interpreting How to Stop Defragmenting and Start Living

Cherie,
 I would use extent sizing that can accomplish 3 things: 1. Is it a multiple of the block size?,
2, Is the extent size chosen used by other tables/tablespaces?( fragmentation prevention)
3, Are there multiple tables/tablespaces in the datafile. I used 4k, 4M and 20M extents for the database. The largest table partition only has 38 extents. My tables grow very little each year except when they are archived to the read only tablespaces. The current data space remains unchanged and can handle the data necessary before the archiving process truncates the tables. With the same sized extents in the same tablespace/datafile there is not an issue with fragmentation.
 I don't see any hard and fast rule that must be followed. Just make it easier on your self to manage and life will be great. Ron.
ROR mª¿ªm

>>> Cherie_Machler=Received: from CONNECT-MTA by galotter40gelco.com 01/28/02 01:35PM
>>>>

Following is an excerpt from the white paper How to Stop Defragmenting and
Start Living:

These extent sizes are chosen to be a multiple of five blocks since Oracle7
will round all extent sizes to a multiple of five blocks. In Oracle8 extents won't be rounded to a multiple of five blocks if they are a multiple of the minimum extent size for the tablespace. For Oracle8 databases, choosing the following extent sizes is a little simpler.

1. Segments smaller than 128M should be placed in 128K extent tablespaces.
2. Segments between 128M and 4G should be placed in 4M extent tablespaces.
3. Segments larger than 4G should be placed in 128M extent tablespaces For the remainder of this paper we will assume that the Oracle7 extent sizes are used since they can be used in both releases.

I have some partitioned indexes that I am trying to split out into individual tablespaces, one tablespace per partition. In the excerpt below, I'm not sure what the meaning of segment is. My question is this.
If the entire index is 152M and an individual index partition is 5m, then
what size extent sizes should I use in my index partition tablespaces? Note that this is an 8.0.4 database.

Thanks for your clarification on this issue.

Cherie Machler
Oracle DBA
Gelco Information Network

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
--
Author:
  INET: Cherie_Machler_at_gelco.com 

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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.com
--
Author: Ron Rogers
  INET: RROGERS_at_galottery.org

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).