Over the last couple of years there has been a lot of debate about space management in Data Warehousing environments and the benefits of having fewer larger extents. Many believe the easiest way to achieve this is to use uniform extents but the obvious benefits can often be out weighed by some not so obvious drawbacks.
For performance reasons most loads leverage direct path operations where the load process directly formats and writes Oracle blocks to disk instead of going through the buffer cache. This means that the loading process allocates extents and fills them with data during the load. During parallel loads, each loader process will allocate it own extent and no two processes work on the same extent. When loading data into a table with UNIFORM extents each loader process will allocate its own Uniform extent and begin loading the data, if the extents are not fully populated the table is left with a lot of partially filled extents, effectively creating ‘HOLES’ in the table. Not only is this space wastage but it also impacts query performance as subsequent queries that scan the table have to scan all of the extents even if they are not fully filled.
What is different with AUTOALLOCATE? AUTOALLOCATE will dynamically adjust the size of an extent and trim the extent after the load in case it is not fully loaded (Extent Trimming)
Tom Kyte covers this problem in great details in his post Loading and Extents but below is a simple example just to illustrate what a huge difference there can be in space management when you load into a table with uniform extents versus a table with autoallocated extents.
1) Create two tablespaces: Test_Uniform (using uniform extent management), Test_Allocate (using auto allocate)
create tablespace test_uniform datafile ‘+DATA/uniform.dbf’ SIZE 1048640K
AUTOEXTEND ON NEXT 100M
EXTENT management local uniform size 100m;
create tablespace test_allocate datafile ‘+DATA/allocate2.dbf’ SIZE 1048640K
AUTOEXTEND ON NEXT 100M
EXTENT management local autoallocate segment space management auto;
2)Create a flat file with a 10,000,000 records.
-rw-r–r– 1 oracle dba 1077320689 May 17 16:59 TEST.dat
3)Do a parallel direct path load of this file into each tablespace:
create table UNIFORM_TEST parallel
tablespace Test__uniform as select * from big_table_ext;
create table AUTOALLOCATE_TEST
parallel
tablespace Test_allocate
as select * from big_table_ext;
Let’s view the space usage using a PL/SQL package called show_space .
As you can see from the results there are no unformatted blocks in the autoallocate table as extent trimming has taken place after the load was complete. The same can not be said for the uniform_test table. It is quite evident from the numbers that there is substantial space wastage in the uniform_test table. Although the count of full blocks are the same in both cases, the Total Mbytes used is 10x greater in the Uniform table.