Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Locally Managed Tablespace Uniform Extent

RE: Locally Managed Tablespace Uniform Extent

From: Jack C. Applewhite <>
Date: Wed, 14 Mar 2001 14:29:39 -0800
Message-ID: <>


I'm probably a bit extreme here, but, with all due respect to Steve Adams (because I really do), I wouldn't worry terribly much about numbers of extents.

Our 8.1.6 production db on Win2k has 8KB block size and uniform extent size of 1MB in all tablespaces. Our largest segment stores the out-of-line CLOBs of a partition of our largest table - it has over 22,000 extents. Another partition has a CLOB segment of over 18,000 extents. Since we hit those segments by RowID during InterMedia Text index queries, we've had absolutely no performance problems - we get 1 to 5 second response times. Actually, the InterMedia Text index segments have over 1,000 extents.

A bunch of our tables with non-LOB data have hundreds of extents as well, which probably puts them in line with Steve's guidelines, but I wouldn't be worried if they got up into the thousands of extents.

I'm sure there are numerous situations where different extent sizes in different tablespaces makes sense. Maybe I'm just too lazy. We don't have that many small tables where 1MB extents waste a lot of space and, like I said, I don't worry about too many extents, though if extents approached the hundreds of thousands, I might create a tablespace or two with large extents for those segments. I've just not seen really convincing arguments that large
(but not huge) numbers of extents cause significant
performance problems, especially compared to the really BAD SQL that Duhvelopers seem so fond of writing. ;-)

A big advantage is that I can't even remember the last time I worried about coelescing and fragmentation!


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas

-----Original Message-----
Jim Walski
Sent: Wednesday, March 14, 2001 2:10 PM
To: Multiple recipients of list ORACLE-L

I have to move a 7.3.4 database to 8i and I want to use the uniform extent
size to reduce fragmentation. I read the article "stop defragmenting and
start living" and it indicates to have 3 extent sizes - 128K, 4M, 128M.

I also read an article on Steve Adams site that indicates to keep the number
of extents under (db_block_size/16) - 7 ( which in my case  8k block) would
be 505 extents. )

In the database I am moving there are some segments that are currently 1GB
in size, if i was to put those in the 4M tablespace it would already have
over 250 extents starting off. Are there any other performance type issues
to consider?

Should I create another extent size between 4M and the 128M? Maybe 64M


Please see the official ORACLE-L FAQ:
Author: Jack C. Applewhite

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: (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 Wed Mar 14 2001 - 16:29:39 CST

Original text of this message