Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Automanagement of extent sizing

Re: Automanagement of extent sizing

From: Don Granaman <>
Date: Fri, 14 Sep 2001 02:04:25 -0700
Message-ID: <>

!! Please do not post Off Topic to this List !!

I've been using LMTs in production systems since 1999 with no problems. I've been doing uniform extents for (almost) everything (enforced through controlling the DDL) since 1990 - and wouldn't have it any other way.

My strategy is simple - assign objects to tablespaces bases on three criteria. 1) How big are they (or will they get)
2) What is the nature of the object (Table? Index? Read-only? Truncate/Reload, etc.)
3) Separate objects that are likely to contend with each other into different tablespaces.
Pretty basic stuff really.

Other basic "best practices", in my opinion, are:

I know that autoextend is popular, but consider fragmentation at the filesystem level also. Consider this: You create N * datafiles on a brand new filesystem, using virgin disks and load them up with data. A month later, all of these have autoextended 5 times each (assume - in round robin fashion). How many different contiguous file chunks are now on this filesystem? Answer: 5 * N. Each file would have 5 discontiguous "extents". Is this a problem? Probably not much of one, but "it depends"... I prefer to just create datafiles out of a small set of sizes and add a new datafile when a tablespace needs more space. The other advantage is that moving files about is a lot simpler - you only have a few files sizes and a few "hole sizes" ("hole size" = size of space vacated by a moved datafile).

If you are using raw devices, remember two things. 1) A single raw volume can contain at most one datafile - it never gets fragmented the way a filesystem can.
2) You CAN resize or autoextend a datafile on a raw volume - up to the size of the volume.

The paper so frequently mentioned "How to Stop Defragging and Start Living" is a good one and the latest in the line of evolution, but it certainly isn't the first one or the only one. This philosophy has been around, but perhaps not well known or commonly adopted, for well over a decade. Popular published papers on this topic go back to at least the mid-1990s. One classic is Cary Millsap's "Space - The Final Frontier". For many years, at every major conference there were at least a couple of presentations that were some variation on this same theme. Don't be concerned too much about uniform extents. LMTs are relatively new, but the practice is very well established and thoroughly proven.

I will add one last thing though. There is a potential "gotcha". If you have an object that is routinely cleaned out and reloaded (say via drop|truncate and recreate|reload) in batch, you may not want 1000+ extents. When in doubt about where to put something, based on its size and probable number of extents, it is usually better to "round up".

-Don Granaman
[OraSaurus - Honk if you remember UFI!]

> !! Please do not post Off Topic to this List !!
> How many people are using the new 8i and 9i feature for automatic space
> management? I'm specifically interested in the management of extent
> sizing.
> For those of you that are using it, what has your experience been with it?
> What have you learned? What are the pros and cons? What sort of
> strategy have you used to implement it?
> We are considering using this feature for a new project. Is this a good
> idea? What would be the "right" way to implement this?

Please see the official ORACLE-L FAQ:
Author: Don Granaman

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 Fri Sep 14 2001 - 04:04:25 CDT

Original text of this message