看循序渐进和数据库性能优化时进行的测试，记录一下。[@more@]

UNIFORM SIZE的测试

站在用户的角度思考问题，与客户深入沟通，找到禹会网站设计与禹会网站推广的解决方案，凭借多年的经验，让设计与互联网技术结合，创造个性化、用户体验好的作品，建站类型包括：成都做网站、成都网站制作、企业官网、英文网站、手机端网站、网站推广、申请域名、网页空间、企业邮箱。业务覆盖禹会地区。

SQL> CREATE TABLESPACE eric DATAFILE
2 '/u01/app/oradata/orcl/eric01.dbf' SIZE 10M AUTOEXTEND OFF
3 LOGGING
4 PERMANENT
5 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
6 BLOCKSIZE 8K
7 SEGMENT SPACE MANAGEMENT MANUAL
8 FLASHBACK ON;

Tablespace created.

SQL> select tablespace_name,extent_management,allocation_type
2 from dba_tablespaces where tablespace_name='ERIC';

TABLESPACE_NAME EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
ERIC LOCAL UNIFORM

SQL> create table test
2 tablespace eric as
3 select * from dba_users where 1=0;

Table created.

SQL> select extent_id,block_id,blocks
2 from dba_extents where segment_name='TEST' and tablespace_name='ERIC';

EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ----------
0 9 128

test表创建时是从block_id为9的开始的,前8个block都被系统保留了.其中数据块1和2用于记录数据文件头的信息,数据块

3~8用于记录区间的位图信息.

SQL> alter system dump datafile 9 block min 1 block max 3;

System altered.

SQL> SELECT d.VALUE
2 || '/'
3 || LOWER (RTRIM (i.INSTANCE, CHR (0)))
4 || '_ora_'
5 || p.spid
6 || '.trc' trace_file_name
7 FROM (SELECT p.spid
8 FROM v$mystat m, v$session s, v$process p
9 WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p ,
10 (SELECT t.INSTANCE
11 FROM v$thread t, v$parameter v
12 WHERE v.NAME = 'thread'
13 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
14 (SELECT VALUE
15 FROM v$parameter
16 WHERE NAME = 'user_dump_dest') d
17 /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/admin/orcl/udump/orcl_ora_3868.trc

根据生成的跟踪文件,可以发现前两个block记录的是文件头的信息

/u01/app/admin/orcl/udump/orcl_ora_3868.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/product/10201/
System name: Linux
Node name: rhel131
Release: 2.6.9-42.ELsmp
Version: #1 SMP Wed Jul 12 23:27:17 EDT 2006
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 3868, image: oracle@rhel131 (TNS V1-V3)

*** 2008-10-22 03:22:09.566
*** ACTION NAME:() 2008-10-22 03:22:09.564
*** MODULE NAME:(sqlplus@rhel131 (TNS V1-V3)) 2008-10-22 03:22:09.564
*** SERVICE NAME:(SYS$USERS) 2008-10-22 03:22:09.564
*** SESSION ID:(159.6) 2008-10-22 03:22:09.564
Start dump data blocks tsn: 9 file#: 9 minblk 1 maxblk 3
Block 1 (file header) not dumped: use dump file header command
buffer tsn: 9 rdba: 0x02400002 (9/2)
scn: 0x0000.001398aa seq: 0x02 flg: 0x00 tail: 0x98aa1d02
frmt: 0x02 chkval: 0x0000 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CEB6800 to 0x0CEB8800
CEB6800 0000A21D 02400002 001398AA 00020000 [......@.........]
CEB6810 00000000 00000009 00000080 00000500 [................]
CEB6820 00000001 00000000 00000000 00000007 [................]
CEB6830 00000488 00000001 00000008 00000000 [................]
CEB6840 00000000 00000000 00000000 00000000 [................]
CEB6850 00000009 00000080 00000000 00000000 [................]
CEB6860 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
CEB87F0 00000000 00000000 00000000 98AA1D02 [................]
File Space Header Block:
Header Control:
RelFno: 9, Unit: 128, Size: 1280, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 7, Tail: 1160, First: 1, Free: 8
Deallocation scn: 0.0
Header Opcode:
Save: No Pending Op
buffer tsn: 9 rdba: 0x02400003 (9/3)
scn: 0x0000.001398aa seq: 0x01 flg: 0x00 tail: 0x98aa1e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CEB6800 to 0x0CEB8800
CEB6800 0000A21E 02400003 001398AA 00010000 [......@.........]
CEB6810 00000000 00000009 00000009 00000000 [................]
CEB6820 00000001 0000F7FF 00000000 00000000 [................]
CEB6830 00000000 00000000 00000001 00000000 [................]
CEB6840 00000000 00000000 00000000 00000000 [................]
Repeat 506 times
CEB87F0 00000000 00000000 00000000 98AA1E01 [................]
File Space Bitmap Block:
BitMap Control:
RelFno: 9, BeginBlock: 9, Flag: 0, First: 1, Free: 63487
0100000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000

从第3个块开始,记录了位图的信息.
01是16进制转换为二进制就是0000 0001,经过高位低位的字节交换得出的结果是1000 0000(linix平台为little-Endian),

表示分配了一个区间

下面再分配一个区间
SQL> alter table test allocate extent;

Table altered.

此时的位图变成了:
buffer tsn: 9 rdba: 0x02400003 (9/3)
scn: 0x0000.00139a4b seq: 0x01 flg: 0x00 tail: 0x9a4b1e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CEB6800 to 0x0CEB8800
CEB6800 0000A21E 02400003 00139A4B 00010000 [......@.K.......]
CEB6810 00000000 00000009 00000009 00000000 [................]
CEB6820 00000002 0000F7FE 00000000 00000000 [................]
CEB6830 00000000 00000000 00000003 00000000 [................]
CEB6840 00000000 00000000 00000000 00000000 [................]
Repeat 506 times
CEB87F0 00000000 00000000 00000000 9A4B1E01 [..............K.]
File Space Bitmap Block:
BitMap Control:
RelFno: 9, BeginBlock: 9, Flag: 0, First: 2, Free: 63486
0300000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000

03代表0000 0011,经过高低位的转换也就是1100 0000,代表前两个区间被分配了.

在这个数据文件里再建立一个对象,共分配四个区间

SQL> create table test2 tablespace eric
2 as select * from dba_objects where 1=2;

Table created.

SQL> alter table test2 allocate extent;

Table altered.

SQL> /

Table altered.

SQL> /

Table altered.

SQL> col segment_name for a10
SQL> select segment_name,extent_id,block_id,blocks
2 from dba_extents where file_id=9;

SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST 0 9 128
TEST 1 137 128
TEST2 0 265 128
TEST2 1 393 128
TEST2 2 521 128
TEST2 3 649 128

6 rows selected.

再转储文件头,区间位图已变化为:
buffer tsn: 9 rdba: 0x02400003 (9/3)
scn: 0x0000.00139d3d seq: 0x01 flg: 0x04 tail: 0x9d3d1e01
frmt: 0x02 chkval: 0x4d8d type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CEB6800 to 0x0CEB8800
CEB6800 0000A21E 02400003 00139D3D 04010000 [......@.=.......]
CEB6810 00004D8D 00000009 00000009 00000000 [.M..............]
CEB6820 00000006 0000F7FA 00000000 00000000 [................]
CEB6830 00000000 00000000 0000003F 00000000 [........?.......]
CEB6840 00000000 00000000 00000000 00000000 [................]
Repeat 506 times
CEB87F0 00000000 00000000 00000000 9D3D1E01 [..............=.]
File Space Bitmap Block:
BitMap Control:
RelFno: 9, BeginBlock: 9, Flag: 0, First: 6, Free: 63482
3F00000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000

3F转换为二进制是0011 1111,高低位转换结果是1111 1100,也就是对应了前6个区间被分配了

再建立一个对象.
SQL> create table test3 tablespace eric
2 as select * from dba_objects where 1=2;

Table created.

SQL> select segment_name,extent_id,block_id,blocks
2 from dba_extents where file_id=9;

SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST 0 9 128
TEST 1 137 128
TEST2 0 265 128
TEST2 1 393 128
TEST2 2 521 128
TEST2 3 649 128
TEST3 0 777 128

7 rows selected.

此时的位图变化为:

/u01/app/admin/orcl/udump/orcl_ora_3894.trc

File Space Bitmap Block:
BitMap Control:
RelFno: 9, BeginBlock: 9, Flag: 0, First: 7, Free: 63481
7F00000000000000 0000000000000000 0000000000000000 0000000000000000

7F转换为二进制为0111 1111,经过位转换结果为1111 1110 ,前7个区已分配.
下面drop掉test2表看看

SQL> drop table test2;

Table dropped.

SQL> col segment_name for a10
SQL> select segment_name,extent_id,block_id,blocks
2 from dba_extents where file_id=9;

SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST 0 9 128
TEST 1 137 128
TEST3 0 777 128

奇怪了,怎么还是7F?跟drop前是一样的.

/u01/app/admin/orcl/udump/orcl_ora_3897.trc

File Space Bitmap Block:
BitMap Control:
RelFno: 9, BeginBlock: 9, Flag: 0, First: 7, Free: 63481
7F00000000000000 0000000000000000 0000000000000000 0000000000000000

下面比较了一下最后两后的trace文件,也没有发现不同点.

[oracle@rhel131 ~]$ diff /u01/app/admin/orcl/udump/orcl_ora_3894.trc

/u01/app/admin/orcl/udump/orcl_ora_3897.trc
1c1
< /u01/app/admin/orcl/udump/orcl_ora_3894.trc
---
> /u01/app/admin/orcl/udump/orcl_ora_3897.trc
13c13
< Unix process pid: 3894, image: oracle@rhel131 (TNS V1-V3)
---
> Unix process pid: 3897, image: oracle@rhel131 (TNS V1-V3)
15,19c15,19
< *** 2008-10-22 03:46:04.029
< *** ACTION NAME:() 2008-10-22 03:46:04.028
< *** MODULE NAME:(sqlplus@rhel131 (TNS V1-V3)) 2008-10-22 03:46:04.028
< *** SERVICE NAME:(SYS$USERS) 2008-10-22 03:46:04.028
< *** SESSION ID:(146.56) 2008-10-22 03:46:04.028
---
> *** 2008-10-22 03:47:50.875
> *** ACTION NAME:() 2008-10-22 03:47:50.874
> *** MODULE NAME:(sqlplus@rhel131 (TNS V1-V3)) 2008-10-22 03:47:50.874
> *** SERVICE NAME:(SYS$USERS) 2008-10-22 03:47:50.874
> *** SESSION ID:(146.61) 2008-10-22 03:47:50.874

终于找到原因了，是10g recyclebin引起的。

在drop test2时，没有清空回收站的信息，所以在文件头还是会记录空间已分配，试了另外一个表就知道原因了。

SQL> drop table test purge;

Table dropped.

再来看dump出来的文件
File Space Bitmap Block:
BitMap Control:
RelFno: 9, BeginBlock: 9, Flag: 0, First: 0, Free: 63483
7C00000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000

变成7C了,转成二进制是0111 1100,高低位转换后就成为0011 1110

再来purge test2.

SQL> purge table test2;

Table purged.

dump出来的信息为:

File Space Bitmap Block:
BitMap Control:
RelFno: 9, BeginBlock: 9, Flag: 0, First: 0, Free: 63487
4000000000000000 0000000000000000 0000000000000000 0000000000000000

变成40了,转成二进制是0100 0000,高低位转换后就成为0000 0010了.

本文标题：表空间uniformsize和autoallocate的区别
URL标题：http://kswjz.com/article/gopjpe.html