RE: Interpreting How to Stop Defragmenting and Start Living

Cherie

Here is an Oracle document that I found useful: http://otn.oracle.com/deploy/availability/htdocs/fragment.html

It is similar to what you have quoted, but states it a little more forcefully:

Oracle has a number of recommendations.
First, set all the extents in a tablespace to the same size. . . . Second, . . . Choose tablespaces for segments based on three recommended extent sizes: 128K, 4MB, or 128MB. For example, segments smaller than 128MB should be placed in tablespaces with 128KB extent size.

Personally, I'm still trying to get my head around the whole uniform extents / LMT philosophy, so I am hardly an expert. In particular, I want to make the last statement say: "smaller than 128MB should be placed in tablespaces with 4MB extent size." I guess that if you are placing the index partitions in separate tablespaces, then the total index size doesn't apply, just the size of the individual partition, since that is the object you are putting in the correct tablespace.

        Another out would be the statement "where fragmentation is expected". Possibly if you are confident you won't experience fragmentation
(maybe through accurate table growth prediction), then you may be less
concerned with strict application of these techniques.

Please share anything you learn as you apply these new techniques.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Monday, January 28, 2002 12:35 PM
To: Multiple recipients of list ORACLE-L

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: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.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).