最近从《oracle9iSTATSPACK高性能调整》上看到关于insert append的一段话：
“
我们假定数据库有一个表名为transaction，有200个数据操作员在不断向其中加入行。这个transaction表定义了200个自由表(freelist)，这个表会通过提升oracle表的高水位(high-water)标志来收集自由块。这个例子假定当在insert语句上使用append提示的时候，表的自由表上没有任何自由块。由于我们知道oracle会采用5个块的幅度提升表的高水位标志，所以200个同时进行的insert操作将会产生密集的insert活动，它会在表中生成1000个数据块的集合。
”
读到此处有些疑惑，因为以前做过insert append的实验，好像并没有想树上所说的那样每个insert会分配5个块，如果是这样的话，在高并发时进行insert append将是极为恐怖的事情，表的空间会迅速膨胀。也可能是insert append受不同环境的影响结果也会不同，还是作实验验证一下。
在freelist和assm两种情况下测试：
首先是freelist：
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for 32-bit Windows: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production
create tablespace test datafile 'd:\oracle\oradata\test\test.dbf' size 10m;
SQL> select segment_space_management from dba_tablespaces where tablespace_name=
'TEST';
SEGMEN
------
MANUAL
SQL> CREATE TABLE TEST STORAGE(FREELISTS 99) TABLESPACE TEST AS SELECT * FROM DB
A_OBJECTS WHERE ROWNUM<11;
表已创建。
SQL> SELECT FILE_ID,EXTENT_ID,BLOCK_ID,BLOCKS FROM DBA_EXTENTS WHERE SEGMENT_NAM
E='TEST' AND OWNER='SYSTEM';
FILE_ID EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
3 0 9 8
导出段头,看出高水标记在11块。
Start dump data blocks tsn: 3 file#: 3 minblk 9 maxblk 9
buffer tsn: 3 rdba: 0x00c00009 (3/9)
scn: 0x0000.000f3cd6 seq: 0x01 flg: 0x00 tail: 0x3cd61001
frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 7
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x00c0000b ext#: 0 blk#: 1 ext size: 7
删除test表的数据，
SQL> delete test;
已删除10行。
SQL> commit;
提交完成。
insert append插入1行数据
SQL> insert /*+ append */ into test select * from dba_objects where rownum = 1;
已创建 1 行。
SQL> commit;
提交完成。
再次导出段头，看看高水的情况，向后推移了1个数据块，第12个块。
证明append起作用了，insert的记录在高水的块，第0条记录。
Start dump data blocks tsn: 3 file#: 3 minblk 9 maxblk 9
buffer tsn: 3 rdba: 0x00c00009 (3/9)
scn: 0x0000.000f9d45 seq: 0x02 flg: 0x00 tail: 0x9d451002
frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 7
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x00c0000c ext#: 0 blk#: 2 ext size: 7
再次insert append，看看情况
Start dump data blocks tsn: 3 file#: 3 minblk 9 maxblk 9
buffer tsn: 3 rdba: 0x00c00009 (3/9)
scn: 0x0000.000f9e5e seq: 0x02 flg: 0x00 tail: 0x9e5e1002
frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 7
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x00c0000d ext#: 0 blk#: 3 ext size: 7
高水向后推移了1个数据块，到了第13个数据块。
用get_rowid查看一下，
SQL> select get_rowid(rowid) from test;
GET_ROWID(ROWID)
--------------------------------------------------------------------------------
Object# is :6898
Relative_fno is :3
Block number is :11
Row number is :0
Object# is :6898
Relative_fno is :3
Block number is :12
Row number is :0
看来每个insert append要从高水分配一个数据块来插入数据，并不想书上所说的那样，一次就分配5个块，不过这样也是有浪费的。
再试试insert append一次插入多个块的情况，先把数据delete掉。
SQL> delete test;
已删除2行。
SQL> commit
2 ;
提交完成。
SQL> insert /*+ append */ into test select * from dba_objects where rownum < 6;
已创建5行。
SQL> commit;
提交完成。
SQL> alter system dump datafile 3 block 9;
系统已更改。
导出的结果，高水向后移动了一个块。
Start dump data blocks tsn: 3 file#: 3 minblk 9 maxblk 9
buffer tsn: 3 rdba: 0x00c00009 (3/9)
scn: 0x0000.000f9fd7 seq: 0x02 flg: 0x00 tail: 0x9fd71002
frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 7
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x00c0000e ext#: 0 blk#: 4 ext size: 7
SQL> select get_rowid(rowid) from test;
GET_ROWID(ROWID)
--------------------------------------------------------------------------------
Object# is :6898
Relative_fno is :3
Block number is :13
Row number is :0
Object# is :6898
Relative_fno is :3
Block number is :13
Row number is :1
Object# is :6898
Relative_fno is :3
Block number is :13
Row number is :2
Object# is :6898
Relative_fno is :3
Block number is :13
Row number is :3
Object# is :6898
Relative_fno is :3
Block number is :13
Row number is :4
从段头和get_rowid的情况看，一次性的批量插入也只是把高水向后扩展一个块，也就是说insert append和一次插入的记录多少无关，只是和插入的次数有关。
上面是用freelist管理的表作的实验，下面再看看assm管理的表什么情况：
SQL> drop tablespace test including contents and datafiles;
表空间已丢弃。
SQL> create tablespace test datafile 'd:\oracle\oradata\test\test.dbf' size 10m
autoextend on segment space management auto;
表空间已创建。
SQL> create table test tablespace test as select * from dba_objects where rownum
< 11;
表已创建。
SQL> delete test;
已删除10行。
SQL> commit;
提交完成。
这时导出段头，看看高水的位置，assm的段头一般在段的第3个块
SQL> select file_id,block_id,blocks from dba_extents where segment_name='TEST';
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
3 9 8
SQL> ALTER SYSTEM DUMP DATAFILE 3 BLOCK 11;
系统已更改。
Start dump data blocks tsn: 3 file#: 3 minblk 11 maxblk 11
buffer tsn: 3 rdba: 0x00c0000b (3/11)
scn: 0x0000.000ff33f seq: 0x01 flg: 0x00 tail: 0xf33f2301
frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x00c0000d ext#: 0 blk#: 4 ext size: 8
这时高水在块13上面，insert append插入一条记录看看，
SQL> insert /*+ append */ into test select * from dba_objects where rownum=1;
已创建 1 行。
SQL> commit;
提交完成。
SQL> ALTER SYSTEM DUMP DATAFILE 3 BLOCK 11;
系统已更改。
Start dump data blocks tsn: 3 file#: 3 minblk 11 maxblk 11
buffer tsn: 3 rdba: 0x00c0000b (3/11)
scn: 0x0000.000ff4c0 seq: 0x03 flg: 0x00 tail: 0xf4c02303
frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x00c0000e ext#: 0 blk#: 5 ext size: 8
可以看出高水提升了一个数据块，再试试一次插入多条记录的情况，
SQL> delete test;
已删除 1 行。
SQL> commit;
提交完成。
SQL> insert /*+ append */ into test select * from dba_objects where rownum < 6;
已创建5行。
SQL> commit;
提交完成。
SQL> alter system dump datafile 3 block 11;
系统已更改。
Start dump data blocks tsn: 3 file#: 3 minblk 11 maxblk 11
buffer tsn: 3 rdba: 0x00c0000b (3/11)
scn: 0x0000.000ff5bc seq: 0x03 flg: 0x00 tail: 0xf5bc2303
frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x00c0000f ext#: 0 blk#: 6 ext size: 8
一次插入多条也是只把高水提升了1个块.
那么书上说的一次提升5个块是什么情况呢？也许是8i的情况，由于手头暂时没有8i的库，没有验证，至少9i是每次提升1个块。不过即使每次提升一个块，也是值得注意的一件事情，因为这个块被insert完数据之后就变成了full的状态，在块中的数据被删除之前这个块不能再被利用，可块中实际有很多的空闲空间，这在多个insert append并发的时候会造成空间的极大浪费并会使表占用的空间猛增。
总而言之，在给insert 加append提示的时候一定要考虑空间利用的情况，不能只考虑速度与性能。
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
