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
阅读(1838) | 评论(0) | 转发(0) |