uniform vs autoallocate

http://blogs.ittoolbox.com/database/david/archives/oracle-extent-allocation-autoallocate-vs-uniform-15340

在创建表空间的时候，使用LMT目前来说是毫无疑问的，但是对于extent的分配方式却有不同
采用uniform size还是autoallocate，这是需要我们进行考虑的，还有就是大部分是采用oracle默认的方式也就是autoallocate，现转载老外的一篇文章，看看他是怎么对待该问题的

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
总结：他的思路基本是对于数据量比较小的表可以考虑用autoallocate，反之用uniform size

接下来对extent的一些测试，Oracle10201，OS linux
SQL> conn test/test
Connected.
SQL>  select TABLESPACE_NAME,INITIAL_EXTENT,EXTENT_MANAGEMENT,ALLOCATION_TYPE from dba_tablespaces where tablespace_name='TEST1' ;

TABLESPACE_NAME                INITIAL_EXTENT EXTENT_MAN ALLOCATIO
------------------------------ -------------- ---------- ---------
TEST1                                   65536 LOCAL      SYSTEM

SQL>
SQL> create table t (id number) tablespace test1;

Table created.

SQL> select extent_id,block_id,blocks from dba_extents where owner = user and segment_name='T';

 EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         0         25          8

SQL> declare
  2  begin
  3    for i in 1 .. 320 loop
  4      execute immediate 'alter table t allocate extent';
  5    end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select extent_id,block_id,blocks from dba_extents where owner = user   and segment_name='T';

 EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         0         25          8
         1         33          8
         2         41          8
         3         49          8
         4         57          8
         5         65          8
         6         73          8
         7         81          8
         8         89          8
         9         97          8
        10        105          8

 EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
        11        113          8
        12        121          8
        13        129          8
        14        137          8
        15        145          8
        16        265        128
        17        393        128
        18        521        128
        19        649        128
        20        777        128

......

 EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
       297      36233        128
       298      36361        128
       299      36489        128
       300      36617        128
       301      36745        128
       302      36873        128
       303      37001        128
       304      37129        128
       305      37257        128
       306      37385        128
       307      37513        128

 EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
       308      37641        128
       309      37769        128
       310      37897        128
       311      38025        128
       312      38153        128
       313      38281        128
       314      38409        128
       315      38537        128
       316      38665        128
       317      38793        128
       318      38921        128

 EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
       319      39049        128
       320      39177        128
321 rows selected.

10201中我为测试表分配了1200个extent，而此时的extent大小仍然是128 block
0~15每个extent为8 block=64K，接下来每次分配128 block=1M