表空间uniform size和 autoallocate的区别

现在的位置: 首页 > 综合 > 正文

表空间uniform size和 autoallocate的区别

2019年10月12日 ⁄ 综合 ⁄ 共 12865字 ⁄ 字号小中大 ⁄ 评论关闭

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

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了.

jolly10 发表于:2008.10.23 11:28 ::分类: ( oracle 10g release 2 学习笔记 ) ::阅读:(245次) :: 评论 (1) :: 引用 (0)

re: 表空间uniform size和 autoallocate的区别 [回复]

下面是autoallocate的测试:

SQL> CREATE TABLESPACE eric2 DATAFILE
2 '/u01/app/oradata/orcl/eric2.dbf' SIZE 500M AUTOEXTEND OFF
3 LOGGING
4 PERMANENT
5 EXTENT MANAGEMENT LOCAL autoallocate
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='ERIC2';

TABLESPACE_NAME EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
ERIC2 LOCAL SYSTEM

allocation_type是SYSTEM,表示是系统自动分配extent.

SQL> create table test4
2 tablespace eric2 as
3 select * from dba_objects where 1=0;

Table created.

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

SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST4 0 9 8

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

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

01转换成二进制是0000 0001,高低位转换后为1000 0000

由于是自动管理的区间,这里的1并不能代表分配了一个区间,只说代表分配了64K大小,因为区间尺寸可能是

64K,1MB,8MB,64M或更大,不过都有一个通用尺寸64KB,所以64KB就是该表空间的一个位标记大小.

下面插入一些数据,让其自动分配extent,发现前16个extent是64K,后面是分配的1M.

SQL> insert into test4
2 select * From dba_objects;

51177 rows created.

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

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

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

21 rows selected.

此时DUMP出来的数据是:
SQL> alter system dump datafile 10 block min 1 block max 3;

File Space Bitmap Block:
BitMap Control:
RelFno: 10, BeginBlock: 9, Flag: 0, First: 96, Free: 63392
FFFFFFFFFFFFFFFF FFFFFFFF00000000 0000000000000000 0000000000000000

可以看到分配了96个位(bit),但并不表示分配了96个extent.这里前两个FF代表分配了16个64KB的extent.剩下的80个位

(96-16)表示1024Kb/64Kb*5 extent=80,表示每个位的大小是64K.

下面不断扩展这个表,看看自动分配区的变化.

SQL> insert into test4
2 select * From dba_objects;

51177 rows created.

SQL> /

51177 rows created.

SQL> /

51177 rows created.

SQL> insert into test4
2 select * From test4;

204708 rows created.

SQL> /

409416 rows created.

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

SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST4 22 905 128
TEST4 23 1033 128
TEST4 24 1161 128
TEST4 25 1289 128
TEST4 26 1417 128
TEST4 27 1545 128
TEST4 28 1673 128
TEST4 29 1801 128
TEST4 30 1929 128
TEST4 31 2057 128
TEST4 32 2185 128

SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST4 33 2313 128
TEST4 34 2441 128
TEST4 35 2569 128
TEST4 36 2697 128
TEST4 37 2825 128
TEST4 38 2953 128
TEST4 39 3081 128
TEST4 40 3209 128
TEST4 41 3337 128
TEST4 42 3465 128
TEST4 43 3593 128

SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST4 44 3721 128
TEST4 45 3849 128
TEST4 46 3977 128
TEST4 47 4105 128
TEST4 48 4233 128
TEST4 49 4361 128
TEST4 50 4489 128
TEST4 51 4617 128
TEST4 52 4745 128
TEST4 53 4873 128
TEST4 54 5001 128

SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST4 55 5129 128
TEST4 56 5257 128
TEST4 57 5385 128
TEST4 58 5513 128
TEST4 59 5641 128
TEST4 60 5769 128
TEST4 61 5897 128
TEST4 62 6025 128
TEST4 63 6153 128
TEST4 64 6281 128
TEST4 65 6409 128

SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST4 66 6537 128
TEST4 67 6665 128
TEST4 68 6793 128
TEST4 69 6921 128
TEST4 70 7049 128
TEST4 71 7177 128
TEST4 72 7305 128
TEST4 73 7433 128
TEST4 74 7561 128
TEST4 75 7689 128
TEST4 76 7817 128

SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST4 77 7945 128
TEST4 78 8073 128
TEST4 79 8201 1024
TEST4 80 9225 1024
TEST4 81 10249 1024

82 rows selected.

As you can see in this example, the first 16 extents each are 64KB . The next 63 are each 1MB, and the
remaining are 8MB.

另外一点需要说明的是,如果此时rollback,那么已分配的区块是不会自动回收的,不过再有新的资料插入时会先使用这些已

分配的区.

refer from:http://jolly10.itpub.net/post/7268/472662