RE: RE: Tablespace management.

To quote the paper:

"Oracle supports an unlimited number of extents in a segment. The performance for DML operations is largely independent of the number of extents in the segment. However, certain DDL operations such as dropping and truncating of segments are sensitive to the number of extents. Performance measures for these operations have shown that a few thousand extents can be supported by Oracle without a significant impact on performance. A reasonable maximum has been determined to be 4096. The goal of our recommended algorithm is to keep the number of extents below 1024 which is well within the range that Oracle can efficiently handle. When a segment reaches 1024 extents it is a candidate to be moved to the next larger extent size tablespace. The segment does not necessarily have to be moved immediately or at all. The segment may be near its peak steady state size, in which case even if it has a few thousand extents, it should be left where it is. It is only the segments which are growing that have to be targeted and potentially moved to tablespaces with larger extents."

A few comments:

• This was written in the days of DMTs, so not everything that is said applies to LMTs. The nr of extent stuff certainly does not.
• Event within the confines of DMTs it clearly states that only drop and truncate are sensitive to the nr of extents (because of the necessary DML to FET$ and UET$).
• And even then, 1024 is not really a limit, just a recommended comfort level: "The goal of our recommended algorithm is to keep the number of extents below 1024 which is well within the range that Oracle can efficiently handle" and "The segment does not necessarily have to be moved immediately or at all"

At 11:59 AM 5/30/2003 -0800, you wrote:
>The "How To Stop Defragmenting..." paper says it in section 2.1.4.
>
>
>Rich
>
>Rich Jesse System/Database Administrator
>rich.jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.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).