表空间管理之研究与测试_20091126
文档来源：<
TOM 大师的 <
表空间管理之研究与测试
目录
1 Extent management
1.1 Locally Managed Tablespaces
1.2 Dictionary-Managed Tablespaces
2 Segment space Management
2.1 Manual
2.2 Auto
3 Storage Clause
4 测试
5 Note
目标：搞清Tablespace 的管理机制，对应extent 的分配、回收方式
搞清Segment的管理机制，对应data block 的分配、回收方式
1 Extent management
1.1 Locally Managed Tablespaces
Locally managed tablespaces track all extent information in the tablespace itself using bitmaps.
(跟踪的是extent信息，如使用情况，而不是跟踪某个Data Block，这里再次说明了数据库逻辑层面的管理是基于Extents，而不是基于Datablocks)
对本地管理表空间，Oracle会使用每个数据文件中存储的一个位图来管理Extent。要得到一个extent，系统所做的只是在位图中将某一位设置为１.要释放空间，系统再把这一位设置为０。
The type of allocation:
Autoallocate --system managed
you can specify initial extent size(为datablock的整数倍) and oracle determines the size of additional extents. With a minimum extent size of 64K, this is the default for permanent tablespace.
Uniform. size -- uniform.
You can specify an extent size or use the default size 1M.
Temporary tablespace only use this type of allocation. (临时表空间只能使用Uniform. size)
1.2 Dictionary-managed Tablespace
Oracle updates the appropriate tables in the data dictionary whenever an extent is allocated, or freed for reuse.(用数据字典来记录Extent的状态)
2 Segment space Management
2.1 Manual
Specifying MANUAL tells Oracle that you want to use free lists for managing free space within segments. Free lists are list of data blocks that have space available for inserting rows. (Free list link data blocks which available)
<
2.2 Auto
This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new stats is reflected in the bitmap. (只要数据块有空间可用来插入row piece，则表示此data block可用.
1 这里的bitmap与 tablespace locally management 中的bitmap是一回事吗 tablespace 的bitmap其实就是通过segment中的bitmap来实现的？)
3 Storage Clause
3.1 Extent Storage Clause
Initial extent: LMT and DMT are suitable.
NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, DEFAULT STORAGE are not valid for extents that are managed locally.
3.2 Data block storage Clause
Manual Segment Space Management(NSSN): freelists, freelistgroups, pctused，pctfree,initrans, maxtrans.
Auto Segment Space Management(ASSM):Buffer_pool, pctfree, initrans, maxtrans.
具体的含义参考官方文档。
4 测试
4.1 普通测试
create tablespace demo datafile '/u01/app/oracle/oradata/mydb/demo01.dbf' size 5M;
idle> create table demo(x number) tablespace demo;
Table created.
idle> select extent_id, block_id, bytes, blocks from dba_extents where segment_name='DEMO';
EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
0 9 65536 8
bytes=64k表明default size of extent is 64k.
能否指定initial extent 为其它的大小
create table demo_1(x number)
STORAGE ( INITIAL 50K
NEXT 50K
MAXEXTENTS 10
PCTINCREASE 25 )
tablespace demo;
idle> select table_name,INITIAL_EXTENT,NEXT_EXTENT,PCT_INCREASE from user_tables where table_name='DEMO_1';
TABLE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
------------------------------ -------------- ----------- ------------
DEMO_1 57344 65536 0
57344怎么得来的，initial=50k=51200，怎么会是57344.难道是第一个extent要包括其它头？
idle> select 50 * 1024 from dual;
50*1024
----------
51200
idle> select extent_id, block_id, bytes, blocks from dba_extents where segment_name='DEMO_1';
EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
0 17 65536 8
当指定initial=50k时，实际分配的extents仍为64k.注意next, maxextents, pctincrease are not valid for LMT.
create table demo_2 (x number)
STORAGE ( INITIAL 72K
NEXT 50K
MAXEXTENTS 10
PCTINCREASE 25 )
tablespace demo;
当指定initial=72k时，则分配了两个64k的extent.
idle> select table_name,INITIAL_EXTENT,NEXT_EXTENT,PCT_INCREASE from user_tables where table_name='DEMO_2';
TABLE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
------------------------------ -------------- ----------- ------------
DEMO_2 73728 65536 0
idle> select extent_id, block_id, bytes, blocks from dba_extents where segment_name='DEMO_2';
EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
0 33 65536 8
1 41 65536 8
idle> create table demo_3 tablespace demo as select * from dba_objects ;
Table created.
idle> select table_name,INITIAL_EXTENT,NEXT_EXTENT,PCT_INCREASE from user_tables where table_name='DEMO_3';
TABLE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
------------------------------ -------------- ----------- ------------
DEMO_3 65536 65536 0
idle> select extent_id, block_id, bytes, blocks from dba_extents where segment_name='DEMO_3';
EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
0 43025 65536 8
1 43033 65536 8
2 43041 65536 8
3 43049 65536 8
4 43057 65536 8
5 43065 65536 8
6 43073 65536 8
7 43081 65536 8
8 43089 65536 8
9 43097 65536 8
10 43105 65536 8
11 43113 65536 8
12 43121 65536 8
13 43129 65536 8
14 43137 65536 8
15 43145 65536 8
16 43273 1048576 128
17 43401 1048576 128
18 43529 1048576 128
数据量超过64k,则extent的大小由oracle 确定.8的倍数128,1024 ...
由此可知，
在Tablespace space management = LMT
allocation type = autoallocate
Segment space management = manual
的条件下。对extent的分配是以64k为单位的。
idle> alter tablespace demo add datafile '/u01/app/oracle/oradata/mydb/demodefault02.dbf' size 2M;
Tablespace altered.
create table demo_4 tablespace demo as select * from dba_objects ;
idle> select extent_id, FILE_ID,block_id, bytes, blocks from dba_extents where segment_name='DEMO_2';
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ---------- ----------
0 10 9 65536 8
1 10 17 65536 8
2 10 25 65536 8
3 10 49 65536 8
4 10 57 65536 8
5 10 65 65536 8
6 10 73 65536 8
7 10 81 65536 8
8 10 89 65536 8
9 10 97 65536 8
10 10 105 65536 8
11 10 113 65536 8
12 10 121 65536 8
13 10 129 65536 8
14 11 9 65536 8
15 11 17 65536 8
16 10 137 1048576 128
17 10 265 1048576 128
18 10 393 1048576 128
从这里可知，不能确定Oracle使用tablespace中的哪个datafile来分配合适的extent。
4.2 uniform方式下的测试：
idle> create tablespace t_uniform. datafile '/u01/app/oracle/oradata/mydb/t_uniform01.dbf' size 5M uniform. size 64k;
Tablespace created.
create table t1 (id number, name varchar2(20)) tablespace t_uniform;
insert into t1 select user_id, user_name from dba_users;
idle> select extent_id, FILE_ID,block_id, bytes, blocks from dba_extents where segment_name='T1';
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ---------- ----------
0 11 9 65536 8
idle> alter table t1 allocate extent;
Table altered.
idle> alter system dump datafile 11 block 3;
System altered.
*** SESSION ID:(7.5) 2004-07-10 13:53:19.493
Start dump data blocks tsn: 11 file#: 11 minblk 3 maxblk 3
buffer tsn: 11 rdba: 0x02c00003 (11/3)
scn: 0x0000.000e0b61 seq: 0x01 flg: 0x00 tail: 0x0b611e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
File Space Bitmap Block:
BitMap Control:
RelFno: 11, BeginBlock: 9, Flag: 0, First: 2, Free: 63486
0300000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
...........................
End dump data blocks tsn: 11 file#: 11 minblk 3 maxblk 3
十六进制03转换为二进制00000011，1个bit表示一个extent,表示有两个extent被使用，与下面查询相吻合.
idle> select extent_id, FILE_ID,block_id, bytes, blocks from dba_extents where segment_name='T1';
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ---------- ----------
0 11 9 65536 8
1 11 17 65536 8
再次测试：
idle> alter table t1 allocate extent;
Table altered.
idle> alter table t1 allocate extent;
Table altered.
idle> alter system dump datafile 11 block 3;
System altered.
*** 2004-07-10 14:05:23.490
Start dump data blocks tsn: 11 file#: 11 minblk 3 maxblk 3
buffer tsn: 11 rdba: 0x02c00003 (11/3)
scn: 0x0000.000e0c5e seq: 0x01 flg: 0x00 tail: 0x0c5e1e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
File Space Bitmap Block:
BitMap Control:
RelFno: 11, BeginBlock: 9, Flag: 0, First: 4, Free: 63484
0F00000000000000 0000000000000000 0000000000000000 0000000000000000
....
0000000000000000 0000000000000000 0000000000000000 0000000000000000
End dump data blocks tsn: 11 file#: 11 minblk 3 maxblk 3
0F
十六进制0F转换为二进制00001111，1个bit表示一个extent,表示有四个extent被使用，同样与下面查询相吻合.
idle> select extent_id, FILE_ID,block_id, bytes, blocks from dba_extents where segment_name='T1';
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ---------- ----------
0 11 9 65536 8
1 11 17 65536 8
2 11 25 65536 8
3 11 33 65536 8
4.3 autoallocate方式下的测试：
create tablespace t_auto datafile '/u01/app/oracle/oradata/mydb/t_uniform02.dbf' size 10M autoallocate;
idle> create table t3 tablespace t_auto as select * from dba_objects;
Table created.
idle> select count(*) from dba_extents where segment_name='T3';
COUNT(*)
----------
19
*** 2004-07-10 15:13:58.181
Start dump data blocks tsn: 12 file#: 12 minblk 3 maxblk 3
buffer tsn: 12 rdba: 0x03000003 (12/3)
scn: 0x0000.000e1425 seq: 0x01 flg: 0x04 tail: 0x14251e01
frmt: 0x02 chkval: 0xec96 type: 0x1e=KTFB Bitmapped File Space Bitmap
File Space Bitmap Block:
BitMap Control:
RelFno: 12, BeginBlock: 9, Flag: 0, First: 64, Free: 63424
FFFFFFFFFFFFFFFF 0000000000000000 0000000000000000 0000000000000000
.....................
0000000000000000 0000000000000000 0000000000000000 0000000000000000
End dump data blocks tsn: 12 file#: 12 minblk 3 maxblk 3
只分配了19个extent，但总共有64 bit .表明一个bit对应一个或者多个block.
个人总结：<<来自Piner的文章>>
1，LOCAL管理方式下的表空间，使用uniform方式分配extent情况下，数据文件中每个bit位对应一个extent；
2，autoallocate方式分配extent情况下，数据文件中的bit与block对应，每个bit位对应一个或者多个block。
3，两种管理方式的不同直接致使高水位标记的增长方式不同。在性能与空间两者中做个平衡，uniform倾向性能，autoallocate倾向空间的节
约。
5 Note
问题1 为什么 BLOCK_ID( Starting block number of the extent )从9开始。
参考文档<<详细解析LMT的datafile的物理结构_20091129.doc>>
问题2 一个数据文件最小能够到多小？以8K block size为例。
在Locally Managed Tablespace（LMT）中，如果是System Allocate的话。
1. LMT的数据文件需要1个block存储datafile header，还需要7个block存储bitmap block，这样是8个block，一共64K
2. 需要保证有至少一个extent的空间，在System Allocate中第一个extent会是64K
因此最小只能到128K。尝试缩小到更小的话，会碰到ORA-03214: File Size specified is smaller than minimum required 错误。
如果表空间创建成bigfile模式，那么最小只能到192K，因为bigfile tablespace还需要额外的8个block来存储bitmap block。
问题3 在segment space management = auto 中，bitmap如何理解，Oracle如何跟踪对某个block的使用情况？，如何dump bitmap。是通过Freelist / HighWater吧?
更多底层的关于数据文件的物理结构，之后研究DSI时再研究。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10248702/viewspace-620930/ ，如需转载，请注明出处，否则将追究法律责任。
