BI中的append历险――虚惊一场

BI中的append历险――虚惊一场

日前，在研究oracle的HWM（high water mark高水位标记）和freelist（空闲空间列表）的时候发现了一个BI中可能的巨大存储隐患。现将发现问题到得出最终结论的过程记录如下，希望能为以后的数据库性能调优和开发过程提供点参考资料。

原理探讨

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的操作方式及隐患

为了满足客户可能的异常处理需求，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

select count(distinct substr(rowid,7,3)||substr(rowid,10,6)) from t1 t

    按照我们上面的描述，和BI系统执行的现状，毫无疑问这两者之间的差距应该是相当大的。但查询结果却让我陷入苦闷，抽样检查结果表明，这两者相差无几。

歪打正着的失误代码

    于是我再次将目标转回到BI的操作代码上，结果令我豁然开朗。哈哈，开发的哥们写错了^_^将insert /*+ append */ into tablename 写成了 insert into /*+ append */形似而神非，^_^,结果导致所有的append都失效。

select * from dba_source ds
where ds.owner = 'CTL'
  and instr(upper(ds.text),'APPEND') > 0

 

owner	PACKAGENAME	ROWID	MEMO
CTL	PKG_ODS_ADJ_FIP	431	insert /*+APPEND*/ into
CTL	PKG_FBI_AUTO_ETL	472	v_INSERT_SQL :='insert into /*+APPEND*/ ' ||
CTL	PKG_FBI_AUTO_ETL	901	v_INSERT_SQL := 'insert into /*+APPEND*/ ' ||
CTL	PKG_FBI_AUTO_ETL	1363	v_INSERT_SQL := 'insert into /*+APPEND*/ '
CTL	PKG_DW_LD_FIP	50	insert /*+APPEND*/ into
CTL	PKG_DW_LD_FIP	187	insert /*+APPEND*/ into

。。。

小结：

这次“失败”的调优经历以戏剧化的虚惊一场结束，但还是给了我很多暗示，很多看似可以提高整体性能的操作方式结合之后产生的最终结果有可能反而对性能产生巨大的影响。

至少这次，我们应该感谢开发商的歪打正着^_^。