append 隐患

原理探讨

Freelist 作为一个oracle 存储管理的核心参数，其行为方式由oracle 内部控制，我们一般不需要掌握和控制。但是我们可能会遇到这些问题，当插入一条记录，会插到哪个块中？是使用新块，还是插入有数据的老块？段是什么时候扩展的，如何扩展的？表中只有一条记录，但是作一次select 时代价却是上千个块，为什么？如果我们原理上清楚了oracle 的存储管理方式，对相关这些问题的解决及性能优化就清晰自然了。

Oracle 的逻辑存储结构按表空间、段、区、块进行管理。块是oracle 用来管理存储空间的最基本单元，oracle 数据库在进行输入输出操作时，都是以块为单位进行逻辑读写操作的。区由一系列连续的块组成，oracle 在进行空间分配、回收和管理时是以区为基本单位的。在生成段的时候，会同时分配初始化区，初始化区的第一个块就格式化为segment header ，并被用来记录free list 描述信息、extents 信息和HWM 信息等。

Freelist 是一种单向链表用于定位可以接收数据的块，在字典管理方式的表空间中，oracle 使用freelist 来管理未分配的存储块。Oracle 记录了有空闲空间的块用于insert 或update 。空闲空间来源于两种方式：1 ，段中所有超过HWM 的块，这些块已经分配给段了，但还未被使用。2 ，段中所有在HWM 下的且联入freelist 的块，可以被重用。

如上图所示，假设一个段的HWM 为4 ，那么再次插入或修改数据时，oracle 会先去查找HWM 下面的free 块判断是否有足够的存储空间可以使用，如果没有足够的空间，才考虑使用HWM 以上的unused 空间或向oralce 申请新的extents 。

而对于针对该表的select 查询来说，如果因为没有合适的索引而导致使用access full 全表扫描来查询是否有合适的数据，那么，oracle 将会遍历HWM 以下的所有blocks ，哪怕空闲块（如上图中的3 ）中并没有任何数据。

Append的执行方式

上面我们提到，oracle 在insert 或update 的时候会默认先检索freelist 以确认是否有足够的空间来写入数据，这是一个相对耗时的过程。如果我们确认当前段中可以利用的表空间并不多，或者可以忽略，我们可以强制使用hints/*+ append*/ 来指定insert 或update 操作不搜作freelist 而直接使用HWM 后面的unused blocks ；

Append执行方式测试

1， 创建测试用表t1

create table t1(
fid number ,
fname varchar2 ( 500 )
);
2 ， 为测试用表插入数据

insert into t1
select rownum ,rpad( 'name' , 500 ,rownum ) from dba_objects
where rownum < 1000 ;

commit ;

3 ，检查存储表的extents 信息

select extent_id,file_id,block_id,bytes,blocks
from dba_extents de
where de.owner = 'SYS' and de.segment_name = 'T1'

extents_id

file_id

block_id

bytes

blocks

0

1

132329

65536

8

1

1

132337

65536

8

2

1

132345

65536

8

3

1

132353

65536

8

4

1

132361

65536

8

5

1

132369

65536

8

6

1

132377

65536

8

7

1

132385

65536

8

8

1

132393

65536

8

9

1

132401

65536

8

4 ，删除数据，为表保留一条记录

delete from t1 where fid <> 236 ;

commit ;

重复第三步操作检查表的extents 信息我们将会发现，虽然我们删除了大部分表数据，但空间并没有回收回来，该表的HWM 依然不变。

5 ，检查保留的数据记录所在数据块的free flag 信息

select dbms_rowid.rowid_block_number(rowid ),Dbms_rowid.rowid_relative_fno(rowid ),fid,fname
from t1

block_id	file_id
132346	1

SQL> alter system dump datafile 1 block 132346;

System altered

*** SESSION ID:(10.15119) 2009-02-10 15:27:41.634

Start dump data blocks tsn: 0 file#: 1 minblk 132346 maxblk 132346

buffer tsn: 0 rdba: 0x004204fa (1/132346)

scn: 0x0001.85750034 seq: 0x01 flg: 0x02 tail: 0x00340601

frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Block header dump: 0x004204fa

Object id on Block? Y

seg/obj: 0x167da csc: 0x01.8574fff8 itc: 2 flg: O typ: 1 - DATA

fsl: 2 fnx: 0x4204f9 ver: 0x01

从dump 的结果我们看到flg:O on freelist 该数据块是在freelist 链表中的。

6 ，使用append 插入新的999 条记录

insert /*+ APPEND */ into t1
select rownum ,rpad( 'name' , 500 ,rownum ) from dba_objects
where rownum < 1000 and rownum <> 236 ;

commit ;

再次检查该段的extents 分配情况我们看到oracle 为本次insert 操作新申请了一些数据块，如下表所示。

select extent_id,file_id,block_id,bytes,blocks
from dba_extents de
where de.owner = 'SYS' and de.segment_name = 'T1'

extents_id	file_id	block_id	bytes	blocks
0	1	132329	65536	8
1	1	132337	65536	8
2	1	132345	65536	8
3	1	132353	65536	8
4	1	132361	65536	8
5	1	132369	65536	8
6	1	132377	65536	8
7	1	132385	65536	8
8	1	132393	65536	8
9	1	132401	65536	8
10	1	132409	65536	8
11	1	132417	65536	8
12	1	132425	65536	8
13	1	132433	65536	8
14	1	132441	65536	8
15	1	132449	65536	8
16	1	132489	1048576	128

并且，新插入的数据都在新申请的extents 中。

select min (dbms_rowid.rowid_block_number(rowid )) from t1 t
where fid <> 236

――――――――――

132423

BI 每天在抽取数据之前会先delete 掉一个月的冗余数据，并在之后插入新的一个月的数据。

BI 在将数据从异构数据源中抽取到临时表空间TODS ，从TODS 清理数据到ODS ，从ODS 汇总数据到DW 的过程中大量的使用的hints/*+ append */ 以期提高插入的速度。于是这两种操作方式将会导致的一个严重的存储方式结果在于，在每两天的数据之间会留下可以存储29 天数据的空白空间进入freelist 链表。也就是说我们的数据表段的大小将会是该段实际需要大小的30 倍。这给实际数据存储带来巨大的影响。

同时，由于BI 在复杂报表处理过程中使用了大量的hash_join hints 执行连接，这种join 方式需要大量的access full 。鉴于上面已经描述过的原因，这同样为select 带来很大的性能负面影响。

检查BI 中数据的存储现状

考虑到目前的BI 操作方式可能会造成的巨大隐患，我们有必要检查一下当前BI 中一些使用append 隐式指定insert 方式的表的数据存储情况。

在测试窗口中运行如下代码，检查数据段的total_blocks 和unused blocks ，数据段的使用总块数HWM 为total_blocks-unused blocks ，然后查出数据表中含有数据的数据块数usedblocks ，就可以得出数据段的大致使用情况。

begin
-- Call the procedure
sys.dbms_space.unused_space(segment_owner => :segment_owner,
segment_name => :segment_name,
segment_type => :segment_type,
total_blocks => :total_blocks,
total_bytes => :total_bytes,
unused_blocks => :unused_blocks,
unused_bytes => :unused_bytes,
last_used_extent_file_id => :last_used_extent_file_id,
last_used_extent_block_id => :last_used_extent_block_id,
last_used_block => :last_used_block,
partition_name => :partition_name);
end ;

参数名称	type	values
segment_owner	String	SYS
segment_name	String	T1
segment_type	String	TABLE
total_blocks	Float	80
total_bytes	Float	655360
unused_blocks	Float	3
unused_bytes	Float	24576
last_used_extent_file_id	Float	1
last_used_extent_block_id	Float	132401
last_used_block	Float	5
partition_name	String