Oracle extent allocation: AUTOALLOCATE vs. UNIFORM

Posted by David Yahalom under General IT

Starting with Oracle 9i, DBAs can now create locally managed tablespaces.

A Locally Managed TBS manages its own list of free extents in a bitmap block placed inside the header of the first data file of the tablespace. Inside the bitmap block, each bit maps to a free block in the tablespace.

When creating a locally managed tablespace, you can specify the extent allocation method to be used.

AUTOALLOCATE - means that the extent sizes are managed by Oracle.
Oracle will choose the optimal next size for the extents starting with 64KB. As the segments grow and more extents are needed, Oracle will start allocating larger and larger sizes ranging from 1Mb to eventually 64Mb extents. This might help conserve space but will lead to fragmentation. This is usually recommended for small tables or in low managed systems.

UNIFORM - specifies that the extent allocation in the tablespace is in a fixed uniform size. The extent size can be specified in M or K. The default size for UNIFORM extent allocation is 1M. Using uniform extents usually minimizes fragmentation and leads to better overall performance.

SQL>CREATE TABLESPACE test_tablespcae DATAFILE '/emc/oradata/test_tablespace1.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

SQL>CREATE TABLESPACE test_tablespcae DATAFILE '/emc/oradata/test_tablespace1.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;

I usually prefer to keep large production-grade tables in UNIFORM sized tablespaces and smaller tables or tables in unmanaged environments in AUTOALLOCATE tablespaces.