Smaller extents actually *help* in reporting and capacity planning. You see a more timely and more accurate changes to the database size (without having to resort to 'complex' dbms_space scripts), which aids in decision making for more disks etc etc

In previous versions of oracle (Oracle 5) multiple extents DID cause performance problems. Loved your book (and also asktom column) by the way. ( I did do a test at the time but no longer have Oracle 5.)

In fact, dynamic allocation used to be a problem in dictionary-managed tablespaces: ( From the Oracle8i Designing and Tuning for Performance on-lie book )

" Avoid dynamic extension in dictionary-mapped tablespaces. "

As to the number and size of extents:

" For dictionary-mapped tablespaces, do not let the number of extents exceed 1,000. If extent allocation is local, then do not have more than 2,000 extents. Having too many extents reduces performance when dropping or truncating tables.

Larger extents tend to benefit performance for the following reasons:

Blocks in a single extent are contiguous, so one large extent is more contiguous than multiple small extents. Oracle can read one large extent from disk with fewer multiblock reads than would be required to read many small extents. Therefore, make sure that the extent size is a multiple of DB_FILE_MULTI_BLOCK_READ_COUNT.

Segments with larger extents are less likely to be extended.

You cannot put large segments into single extents, because of file size and file system size limitations. When you enable segments to allocate new extents over time, you can take advantage of faster, less expensive disks.

For a table that is never full-table scanned, it makes no difference in terms of query performance whether the table has one extent or multiple extents.

The performance of searches using an index is not affected by the index having one extent or multiple extents.

Using more than one extent in a table, cluster, or temporary segment does not affect the performance of full scans on a multi-user system.

Using more than one extent in a table, cluster, or temporary segment does not materially affect the performance of full scans on a dedicated single-user batch processing system, if the extents are properly sized and if the application is designed to avoid expensive DDL operations.

If extent sizes are appropriately matched to the I/O size, then the performance cost of having many extents in a segment is minimized.

For rollback segments, many extents are preferable to few extents. Having many extents reduces the number of recursive SQL calls to perform dynamic extent allocations on the segments. "

Hoping to clarify.

We have recently moved our tables from dictionary tblspaces to lmts.
Previously we had a procedure which used to use dbms_space to check the space in each table and index and when the free space reached a threshold eg 75% it would send us an email so that we knew when to allocate some more exents to that table eg

alter table t1 allocate extent (size 2M);

etc


Am I right in thinking that now we have lmts we do not need to do this anymore?
ie the lmt will allocate the space as required and there is no need for us to preallocate extents in the lmt??

I have issued the following for a table in an lmt

alter table t1 allocate extent;

and the commnad worked, so I assume this did in fact allocate an extent, but in doing this am I just doing redundant work that the database will do dynamically anyway when needed??

Wouldn't pre allocating the extents be slightly quicker than the lmt allocating them as needed??

I thought that if you left it for the database to allocate the extent dynamically at runtime it would be slower than pre allocating the extent yourself manually in advance??
Is that not true?

If this is not the case, then all we need to do is make sure the tablespace has enough room for the tables to extend (by themselves), right?

Here is what I usually do when createing tablespaces. I use LMT with uniform extents and automatic segment space management (9.2 and above).

I typically have three tablespaces with extent sizes 64K, 256K and 1024K. Objects are allcoated to each tablespace based on their expected segment size.

Question:
---------

The only technical reasons that I can think of to do this with LMT, rather than just using say one 64K tablespace and storing all objects in there is to improve the efficiency of FTS. (FTS will try and read DBFMBRC*db_block_size blocks, but cannot read past extent boundaries).

So is FTS multiblock I/O the only good reason to use tablespaces with large unifirm extents?

I had actually forgotted about this feature. Maybe it is time to dig out your book again :o). Maybe I'll just wait the release of the next edition.

Are you saying that in ALL cases you would recommend using system managed allocation?

My initial concern is that of fragmentation, but reading your book, the system managed algorithm increases the extent sizes as the table gets larger, but will always have multiple extents of each incremental size.

Is there a documented upper bound on how large these extents can get?

If so, I would hope that this is platform specific and woudl relate to the db_block_size and maximum possible setting of DBFMBRC on the specific platform. Any ideas?

Thanks for your advise.

300 extents for 10G segment would suggest extents larger than 1MB, probably up to 5MB in size.

Initially I was thinking that this would be inefficient since Oracle cannot full scan past an extent boundary. This is faulty thinking. Assuming a segment of 10G and that Oracle can read maximum of 1024K in multiblock reads (DBFMBRC=128, block size=8K 128 x 8K = 1024K) then I am expecting that the FTS performance of having multiple uniform 1024K extents would be roughly the same as having uniform larger extents. Both cases are the same problem, really since the same amopunt of physical reads would have to occur (in the absense of caching effects)

Inefficiencies would creep in if these larger extents were not some multiple of DBFMBRC x db_block_size.

I have to assume that with system managed extents and a quickly growing segment the extent sizes will rapidly grow from 64K to be (DBFMBRC x db_block_size)K and thereafter be an integer multiple of this size.

Do you agree that this *ought* to be how the software behaved? Clearly there is some testing to do here.

I agree with you that in a pure DW the tablepaces might be defined differently - managed manually. Certainly I expect that this would make resource (disk) planning much simpler.

Mat.