Oracle表空间用统一区大小注意点
今天在做测试用EXTENT MANAGEMENT LOCAL UNIFORM管理时，遇到看起来比较奇怪的问题，于是研究了一下。
首先创建3个表空间，注意其初始大小和UNIFORM SIZE。
SQL> create tablespace testblock datafile '/oradata/mcstar/testblock01.dbf' SIZE 200M
2 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2m
3 SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
SQL> create tablespace testblock2 datafile '/oradata/mcstar/testblock02.dbf' SIZE 10M
2 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m
3 SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
SQL> create tablespace testblock3 datafile '/oradata/mcstar/testblock03.dbf' SIZE 10M
2 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8m
3 SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
需要注意的是Oracle创建表空间时，自动扩展为关闭
SQL> select file_id,file_name,blocks-user_blocks,AUTOEXTENSIBLE from dba_data_files order by 1;
FILE_ID FILE_NAME BLOCKS-USER_BLOCKS AUT
------- ---------------------------------------- ------------------ ---
1 /oradata/mcstar/system01.dbf 8 YES
2 /oradata/mcstar/undotbs01.dbf 8 NO
3 /oradata/mcstar/sysaux01.dbf 8 YES
4 /oradata/mcstar/users01.dbf 8 YES
5 /oradata/mcstar/mcstar01.dbf 8 NO
6 /oradata/mcstar/xu01.dbf 8 NO
7 /oradata/mcstar/zhoul01.dbf 8 YES
8 /oradata/mcstar/zhoul201.dbf 8 YES
9 /oradata/mcstar/testblock01.dbf 256 NO
10 /oradata/mcstar/undotbs02.dbf 8 YES
11 /oradata/mcstar/testblock02.dbf 128 NO
FILE_ID FILE_NAME BLOCKS-USER_BLOCKS AUT
------- ---------------------------------------- ------------------ ---
12 /oradata/mcstar/testblock03.dbf 256 NO
12 rows selected.
问题来了，可以看到数据文件block和用户可用的block想减，值竟然不一样。
9号文件差值为256*8k/1024/1024=2M,11号文件差值为128*8k/1024/1024=1M,12号文件差值为256*8k/1024/1024=2M。
由前面知道9号文件的uniform size为2M,11号文件的uniform_size为1M，似乎和差值对的上，但12号文件的差值和uniform_size完全对不上
SQL> select file_id,file_name,blocks-user_blocks from dba_data_files
2 order by 1;
FILE_ID FILE_NAME BLOCKS-USER_BLOCKS
------- ---------------------------------------- ------------------
1 /oradata/mcstar/system01.dbf 8
2 /oradata/mcstar/undotbs01.dbf 8
3 /oradata/mcstar/sysaux01.dbf 8
4 /oradata/mcstar/users01.dbf 8
5 /oradata/mcstar/mcstar01.dbf 8
6 /oradata/mcstar/xu01.dbf 8
7 /oradata/mcstar/zhoul01.dbf 8
8 /oradata/mcstar/zhoul201.dbf 8
9 /oradata/mcstar/testblock01.dbf 256
10 /oradata/mcstar/undotbs02.dbf 8
11 /oradata/mcstar/testblock02.dbf 128
FILE_ID FILE_NAME BLOCKS-USER_BLOCKS
------- ---------------------------------------- ------------------
12 /oradata/mcstar/testblock03.dbf 256
12 rows selected.
继续研究，在表空间testblock3上创建表格，并插入一定的数据量。
SQL> create table testblock3 tablespace testblock3 as select * from obj$;
Table created.
SQL> select EXTENT_ID,BLOCK_ID,BLOCKS from dba_extents where file_id=12;
EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ----------
0 9 1024
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>user,tabname=>'TESTBLOCK3',estimate_percent=>100);
PL/SQL procedure successfully completed.
SQL> select AVG_ROW_LEN,NUM_ROWS,SAMPLE_SIZE from dba_tables where lower(table_name)='testblock3';
AVG_ROW_LEN NUM_ROWS SAMPLE_SIZE
----------- ---------- -----------
78 60759 60759
SQL> alter database datafile '/oradata/mcstar/testblock03.dbf' resize 16m;
Database altered.
SQL> begin
2 for i in (select * from testblock3) loop
3 insert into testblock3 select * from testblock3 where rownum=1;
4 commit;
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TESTBLOCK3 by 1024 in tablespace
TESTBLOCK3
ORA-06512: at line 3
根据平均行长度为78，数据文件存放业务数据行数可近似计算为((16*1024/8-8)*(8192-819-60))/78=2040*7313/78=191263，但现在目前只存有88252。
两者相差较大。
SQL> select count(*) from testblock3;
COUNT(*)
----------
88252
由于目前数据文件只有16M，减去1个数据文件头，7个extent map block，插入数据时并足以进行第二个extent扩展，所以目前表格中只有一个extent，
业务表格实际值，远小于理论值，也就解释的通了。
SQL> select EXTENT_ID,BLOCK_ID,BLOCKS from dba_extents where file_id=12;
EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ----------
0 9 1024
通过bbed查看，可以看到业务数据好像写到了1033个块，但理论上Oracle业务数据只要写到1032个block即可。
BBED> dump block 1032
File: /oradata/mcstar/testblock03.dbf (0)
Block: 1032 Offsets: 0 to 511 Dba:0x00000000
------------------------------------
06a20000 07040003 7a259c0e 000a0106 83bc0000 01000000 22c00400 4e0e9c0e
BBED> dump block 1033
File: /oradata/mcstar/testblock03.dbf (0)
Block: 1033 Offsets: 0 to 511 Dba:0x00000000
------------------------------------
06a20000 08040003 7a259c0e 000a0106 c4420000 01b60000 22c00400 4e0e9c0e
BBED> dump block 1034
File: /oradata/mcstar/testblock03.dbf (0)
Block: 1034 Offsets: 0 to 511 Dba:0x00000000
------------------------------------
00a20000 09040000 00000000 00000105 09a30000 00000000 00000000 00000000
于是采用10046进行跟踪，可以看到业务数据存储到block 11032个为止。
SQL> alter system flush buffer_cache;
System altered.
SQL> alter session set db_file_multiblock_read_count=1;
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 1';
Session altered.
SQL> select count(*) from testblock3;
COUNT(*)
----------
88252
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
WAIT #19: nam='db file sequential read' ela= 8 file#=12 block#=1030 blocks=1 obj#=311330 tim=1273413313089160
WAIT #19: nam='db file sequential read' ela= 8 file#=12 block#=1031 blocks=1 obj#=311330 tim=1273413313089194
WAIT #19: nam='db file sequential read' ela= 7 file#=12 block#=1032 blocks=1 obj#=311330 tim=1273413313089226
通过以上分析。12号文件不能被用户使用的256个块，似乎和extent不能扩展有关。
对dba_extent视图查询可知，Oracle开始extent扩展是从第9号block开始（1号block为datafile_head，2-7号block为extent map）。
由于extent扩展至少需要向数据文件一次性申请1个extent大小（本例12号文件一开始为10M，第二次extent扩展时，由于不能获得8M，所以将浪费2M空间，需要注意的是这2M空间包含了datafile_head和extent map）
为验证上述猜想，继续测试，将数据文件resize至刚好能满足2个extent大小的大小。8M*2*1024*1024+8*8K*1024=16777216+65536=16842752
将数据文件扩展至16842752大小
SQL> alter database datafile '/oradata/mcstar/testblock03.dbf' resize 16842752;
Database altered.
继续插入业务数据
SQL> begin
2 for i in (select * from testblock3) loop
3 insert into testblock3 select * from testblock3 where rownum=1;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> begin
2 for i in (select * from testblock3) loop
3 insert into testblock3 select * from testblock3 where rownum=1;
4 commit;
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TESTBLOCK3 by 1024 in tablespace
TESTBLOCK3
ORA-06512: at line 3
可以看到表格testblock3扩展出第二个分区
SQL> select EXTENT_ID,BLOCK_ID,BLOCKS from dba_extents where file_id=12;
EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ----------
0 9 1024
1 1033 1024
SQL> select count(*) from testblock3;
COUNT(*)
----------
204172
采用10046跟踪，可以看到业务数据已经存放至2056为止，该数据文件得到了充分利用。
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 1';
Session altered.
SQL> alter session set db_file_multiblock_read_count=1;
Session altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from testblock3;
COUNT(*)
----------
204172
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
WAIT #1: nam='db file sequential read' ela= 11 file#=12 block#=2054 blocks=1 obj#=311330 tim=1273414177770633
WAIT #1: nam='db file sequential read' ela= 10 file#=12 block#=2055 blocks=1 obj#=311330 tim=1273414177770673
WAIT #1: nam='db file sequential read' ela= 10 file#=12 block#=2056 blocks=1 obj#=311330 tim=1273414177770715
12号数据文件不可用空间也降至8个数据块。
SQL> select file_id,file_name,blocks-user_blocks from dba_data_files
order by 1;
2
FILE_ID FILE_NAME BLOCKS-USER_BLOCKS
------- ---------------------------------------- ------------------
1 /oradata/mcstar/system01.dbf 8
2 /oradata/mcstar/undotbs01.dbf 8
3 /oradata/mcstar/sysaux01.dbf 8
4 /oradata/mcstar/users01.dbf 8
5 /oradata/mcstar/mcstar01.dbf 8
6 /oradata/mcstar/xu01.dbf 8
7 /oradata/mcstar/zhoul01.dbf 8
8 /oradata/mcstar/zhoul201.dbf 8
9 /oradata/mcstar/testblock01.dbf 256
10 /oradata/mcstar/undotbs02.dbf 8
11 /oradata/mcstar/testblock02.dbf 128
FILE_ID FILE_NAME BLOCKS-USER_BLOCKS
------- ---------------------------------------- ------------------
12 /oradata/mcstar/testblock03.dbf 8
12 rows selected.
通过以上测试表明，我们创建数据文件设置的大小只要能满足extent扩展的需要，就能最大程度的节省空间。如果不加考虑就设置大小，在数据文件自动扩展关闭的情况下，那最大程度将浪费(uniform size-8*block_size)的空间大小。
SQL> create tablespace testblock4 datafile '/oradata/mcstar/testblock04.dbf' SIZE 16842752
2 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8m
3 SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
SQL> select file_id,file_name,blocks-user_blocks from dba_data_files
order by 1;
2
FILE_ID FILE_NAME BLOCKS-USER_BLOCKS
------- ---------------------------------------- ------------------
1 /oradata/mcstar/system01.dbf 8
2 /oradata/mcstar/undotbs01.dbf 8
3 /oradata/mcstar/sysaux01.dbf 8
4 /oradata/mcstar/users01.dbf 8
5 /oradata/mcstar/mcstar01.dbf 8
6 /oradata/mcstar/xu01.dbf 8
7 /oradata/mcstar/zhoul01.dbf 8
8 /oradata/mcstar/zhoul201.dbf 8
9 /oradata/mcstar/testblock01.dbf 256
10 /oradata/mcstar/undotbs02.dbf 8
11 /oradata/mcstar/testblock02.dbf 128
FILE_ID FILE_NAME BLOCKS-USER_BLOCKS
------- ---------------------------------------- ------------------
12 /oradata/mcstar/testblock03.dbf 8
13 /oradata/mcstar/testblock04.dbf 8
13 rows selected.
