Variable length extents

You can only have one variable extent in a storage area and it must be the last extent.  If you only use one variable extent, then the database engine must go to the OS to get another cluster of blocks every time you extend the area which can impact performance significantly.

One advantage of using smaller fixed-length extents instead of one large extent is that you can put the extents on multiple spindles (called "poor man's striping").  This helps performance in that the disk I/O is spread across multiple (physical) disks.  After-imaging does support multiple variable extents, but striping them would not be helpful because you only write to one at a time.

Inode indirection is not an item of concern for journalled file systems. Are there really non-journalled filesystems in use these days? File indirection is an issue only in ufs filesystems and derivatives.  UFS is basically only used these days on special occasions; AFAIK all filesystems in use these days are either derivatives of the Berkely Fast Filesystem or are journalled, so there is not a large scaling in access time with file size as there is with UFS.  In other words, only in special cases will you have a situation where large files are much slower than small files.

Having more smaller data extents instead than few data extents, especially if everything goes into the same disk, has no impact on performance. However it is also important to take into account management of the database outside of production.  It is more challenging to find a 50 GB slice (or multiple slices) to copy the database to instead of 10 x 5 GB slices.  This applies to other machines as well as moving files over the network.  They move “faster” in smaller sizes and are more manageable in so far as if an FTP fails you don’t have to start all over again.  For example, 500 meg extents would not be recommended as there are too many to manage at that point.  You're also likely to hit both Progress and OS limits scaling extent sizes as 'small and many' (maxarea 32000, max ext per area 1024) While, limiting data extents below 2 GB guards against reaching some forgotten OS/user/filesystem limit when the DB is in production. These can usually be overridden/ reconfigured but after the fact,  time consuming and involve production downtime. It also gives you more control on managing the extents themselves and transferring between environments when needed.

General recommendations apply to both 9.1x, 10.1x and 11.x DB structures and underline the use of the extent "properties" (i.e. the block size, record per block and clustering from 10.x+). Always spend some time in defining the best combination: "area - table", assuming you agree on the approach one big table = one separate area, regardless of the number of extents.

+ Largefiles allowed?

Assuming the OS is enabled for largefiles and your ulimits allow ..

With an Enterprise DB, are you going to enable large-files?

If yes, consider using -bithold to stall runaway transactions

If not, remember the 2GB file size limit on variable extents (which should be monitored regardless) specifically on  BI & AI logs which may cause a DB outage

Without Enterprise DB, the 2GB extent limit applies regardless of your requirements.

+ A large number of concurrent users?

The total number of file handles being consumed may become an issue, so use fewer larger extents rather than more smaller to control file handle consumption. Each self-service user consumes one file handle per database extent, plus a few.  If you have hundreds or thousands of users, default kernel configuration of per-user and total system-wide file handle usage for all OS's except Windows and AIX become a limitation.

IMO if the DB is smallish (<30 GB), use 2GB extents or all variable extents. Otherwise use 4Gb or 8Gb extents and 2GB for areas with little growth forecast.

m2c ;)

"Poor mans striping" is, IMHO, a waste of time.  Actually I occasionally run into it on older customer systems and it is sometimes actively harmful because it tends to *concentrate* the hot spots rather than spread them around.  You are *much* better off letting the OS or the SAN handle it for you.

Extending extents is relatively a rare thing -- especially if you are using type 2 areas.  If you have 4k blocks and 64 blocks per cluster and your variable extent grows 1GB per week you will extend 4096 times in the week -- even if all the growth is during business hours that is still only one extend of 256K per 36 seconds.  Which is not much of a strain on any disk.  Even a RAID5 can handle that.  (And if you are growing that fast you should be using more blocks per cluster too.)

I have found single variable extents with type 2 areas to be a very effective way to setup small to medium databases.  Simplifying the administrative side of things outweighs the theoretical performance impact.

Actually it is often easier to get a storage admin to give you one big chunk of space.  They're lazy.  They'd rather give you a big fat terabyte than 50GB here, 125GB there and 12GB over there...  especially if you start asking to make sure that slice A doesn't use the same disks as slice B.  Which they will lie to you about anyway.

I couldn't agree more. No one should use this technique these days. I used to recommend it to people who did not have systems equipped with logical volume managers and could only use JBOD storage. "Striping" extents yourself is a losing proposition.

With modern filesystems, varaible size extents work quite well for all but the most unusual situations.