Re: locally managed tablespace - autoallocate

Mark D Powell wrote:
> The problem with trying to predict the next extent size when
> autoallocate is in use is that the underlying algorithm is subject to
> change with any release according to Oracle. Also depending on the
> Oracle version in use and the history of the tablespace: build as LMT
> or converted the pctincrease parameter still affects the allocated
> extent size. Then again so does the initial extent request size. It
> is very possible to get the initial extent size at 1M even when most
> objects in the tablespace start at 64K. Finally, if the next extent
> size is unavailable in an auto-allocate tablespace then if is possible
> for Oracle to chose to allocate a smaller extent size, but there is no
> guarentee that Oracle will not issue an error.
>
> None of this is to say that you cannot write a useful routine to
> monitor and predict space availability for an autoallocate tablespace,
> it is just that any such routine will have some potentially serious
> limitations.
>
> Based on Connor McDonald testing from 2002
> - auto-alloc extents are 16x64k, then 64x1m, then 120x8m, then 64m,
> when
> the initial_extent is less than 1m
> - auto-alloc extents are 64x1m, then 120x8m, then 64m, when the
> initial_extent is equal/more than 1m

You can't even summarise like that, unfortunately. I just created a table with a 1GB initial extent, and got given 1 8MB extent, 49 1MB extents and then 118 8MB extents again (in that order).

The beauty of autoallocate is that it just does its stuff. Wanting to know what it is doing, or wanting to monitor it or intervene with it, is rather missing the point: we have uniform size for that. If you just want a quiet life, you have autoallocate with autoextend (with a decent NEXT and safe MAXSIZE).

Think 10g's ASM: you simply won't be doing this sort of stuff in the future.

Regards
HJR Received on Wed Dec 08 2004 - 22:23:42 CST