oracle表空间 unifor,表空间uniform size和 autoallocate的区别

看循序渐进和数据库性能优化时进行的测试，记录一下。[@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: (TNS V1-V3)

*** 2008-10-22 03:22:09.566

*** ACTION NAME:() 2008-10-22 03:22:09.564

*** MODULE NAME:( (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: (TNS V1-V3)

---

> Unix process pid: 3897, image: (TNS V1-V3)

15,19c15,19

< *** 2008-10-22 03:46:04.029

< *** ACTION NAME:() 2008-10-22 03:46:04.028

< *** MODULE NAME:( (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:( (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了.