RE: RE: Tablespace management.

"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"

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