Parallel Load: Uniform or AutoAllocate extents?

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.

Conclusion

Space utilization is much better with autoallocate becuase of extent trimming. As I said before more information on this topic can be found on Tom's Kyte post.

 

Comments:

Does advance compression helps any when using uniform extents or does extent trimming still as bad? Thanks Michael.

Posted by mdinh on May 25, 2011 at 07:43 AM PDT #

On the other hand, non uniform extents in a tablespace generate a lot of other problems when the tables involved are volatile. In these cases, it is much preferable to match the load size to the fixed extent size. If the load size is less than the extent size, make it match by increasing the load size. Do not make the database storage dependent on an external format: that is a sure path for disaster later on. It all may look good now, but what happens when you need to reuse space from a moved or dropped table that has varied extents sizes that don't match any other table's in that tablespace? What, that is not "wasteful"? It's a very old problem, and the main reason for the existence of uniform extent LMTs.

Posted by Noons on May 25, 2011 at 08:08 AM PDT #

No, Advance Compression may reduce the number of extents you need during the load, but if the last extent used is partially filled you will continue have "holes" in the table.

Posted by rekha balwada on June 06, 2011 at 08:20 AM PDT #

It is correct there are many factors that you have to think about when deciding on your space managment strategy, I was pointing out one of the side effect of Uniform Extent size when you do direct path loads. I agree that one alternative solution would be to use large Intial and next extents for tables that will get direct path loaded in an autocallocate tablespace. For my volatile object you could use a uniform tablespace.

Posted by guest on June 06, 2011 at 08:30 AM PDT #

Post a Comment:
Comments are closed for this entry.
About

The data warehouse insider is written by the Oracle product management team and sheds lights on all thing data warehousing and big data.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
4
5
6
7
8
9
10
11
12
13
14
16
18
19
20
21
23
24
25
26
27
28
29
30
   
       
Today