当索引数据块需要分裂时,会从Freelist中找到空闲的数据块满足分配需要,在10224的跟踪文件中,可以看到以下信息记录了新数据块的分配:
splitting leaf,dba 0x03c00419,time 13:58:32.558
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x03c00419,time 13:58:32.573
kdisnew_bseg_srch_cbk rejecting block ,dba 0x03c00419,time 13:58:32.573
kdisnew_bseg_srch_cbk using block,dba 0x03c0041a,time 13:58:32.573
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x03c00419,time 13:58:32.573
kdisnew_bseg_srch_cbk rejecting block ,dba 0x03c00419,time 13:58:32.573
kdisnew_bseg_srch_cbk using block,dba 0x03c0041a,time 13:58:32.573
如果索引数据块上的数据被全部删除,该数据块就会被放置在freelist的前面,但并不从B树结构上删除:
HELLODBA.COM> conn demo/demo
Connected.
HELLODBA.COM> truncate table idx_split;
Table truncated.
HELLODBA.COM> alter session set events '10224 trace name context forever,level 1';
Session altered.
HELLODBA.COM> begin
2 for i in 1..64
3 loop
4 insert into idx_split (a, b, c) values (i*3, lpad('A', 100, 'A'), sysdate);
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
HELLODBA.COM> commit;
Commit complete.
HELLODBA.COM> alter session set events 'immediate trace name treedump level 199127';
Session altered.
HELLODBA.COM> delete from idx_split where a between 17*3 and 32*3;
16 rows deleted.
HELLODBA.COM> commit;
Commit complete.
HELLODBA.COM> alter session set events 'immediate trace name treedump level 199127';
Session altered.
Connected.
HELLODBA.COM> truncate table idx_split;
Table truncated.
HELLODBA.COM> alter session set events '10224 trace name context forever,level 1';
Session altered.
HELLODBA.COM> begin
2 for i in 1..64
3 loop
4 insert into idx_split (a, b, c) values (i*3, lpad('A', 100, 'A'), sysdate);
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
HELLODBA.COM> commit;
Commit complete.
HELLODBA.COM> alter session set events 'immediate trace name treedump level 199127';
Session altered.
HELLODBA.COM> delete from idx_split where a between 17*3 and 32*3;
16 rows deleted.
HELLODBA.COM> commit;
Commit complete.
HELLODBA.COM> alter session set events 'immediate trace name treedump level 199127';
Session altered.
从跟踪文件可以看到:当数据块中的实际记录数(rrow)为0时,被放到了freelist,但是并未从树结构中拿走。
kdimod adding block to free list,dba 0x03c00419,time 14:10:49.785
----- begin tree dump
branch: 0x3c00405 62915589 (0: nrow: 4, level: 1)
leaf: 0x3c00418 62915608 (-1: nrow: 16 rrow: 16)
leaf: 0x3c00419 62915609 (0: nrow: 16 rrow: 0)
leaf: 0x3c0041a 62915610 (1: nrow: 16 rrow: 16)
leaf: 0x3c0041b 62915611 (2: nrow: 16 rrow: 16)
----- end tree dump
----- begin tree dump
branch: 0x3c00405 62915589 (0: nrow: 4, level: 1)
leaf: 0x3c00418 62915608 (-1: nrow: 16 rrow: 16)
leaf: 0x3c00419 62915609 (0: nrow: 16 rrow: 0)
leaf: 0x3c0041a 62915610 (1: nrow: 16 rrow: 16)
leaf: 0x3c0041b 62915611 (2: nrow: 16 rrow: 16)
----- end tree dump
在下一次数据块分裂时,从freelist上获取到该数据块,然后将其从树结构中删除,重新分配到树结构中:
HELLODBA.COM> insert into idx_split (a, b, c) values (65*3, lpad('A', 100, 'A'), sysdate);
1 row created.
HELLODBA.COM> commit;
Commit complete.
HELLODBA.COM> alter session set events 'immediate trace name treedump level 199127';
Session altered.
1 row created.
HELLODBA.COM> commit;
Commit complete.
HELLODBA.COM> alter session set events 'immediate trace name treedump level 199127';
Session altered.
跟踪文件显示了这一数据块被回收利用的过程:
splitting leaf,dba 0x03c0041b,time 14:10:49.831
kdisprobe on path succeeded, branch,dba 0x03c00405,time 14:10:49.847
kdisprobe regot child,dba 0x03c00419,time 14:10:49.847
kdisdelete probe successful, proceed,dba 0x03c00419,time 14:10:49.863
delete leaf,dba 0x03c00419,time 14:10:49.863
kdisdelbr1 sno 0,dba 0x03c00405,time 14:10:49.863
kdisnew_bseg_srch_cbk using block,dba 0x03c00419,time 14:10:49.878
----- begin tree dump
branch: 0x3c00405 62915589 (0: nrow: 4, level: 1)
leaf: 0x3c00418 62915608 (-1: nrow: 16 rrow: 16)
leaf: 0x3c0041a 62915610 (0: nrow: 16 rrow: 16)
leaf: 0x3c0041b 62915611 (1: nrow: 16 rrow: 16)
leaf: 0x3c00419 62915609 (2: nrow: 1 rrow: 1)
----- end tree dump
kdisprobe on path succeeded, branch,dba 0x03c00405,time 14:10:49.847
kdisprobe regot child,dba 0x03c00419,time 14:10:49.847
kdisdelete probe successful, proceed,dba 0x03c00419,time 14:10:49.863
delete leaf,dba 0x03c00419,time 14:10:49.863
kdisdelbr1 sno 0,dba 0x03c00405,time 14:10:49.863
kdisnew_bseg_srch_cbk using block,dba 0x03c00419,time 14:10:49.878
----- begin tree dump
branch: 0x3c00405 62915589 (0: nrow: 4, level: 1)
leaf: 0x3c00418 62915608 (-1: nrow: 16 rrow: 16)
leaf: 0x3c0041a 62915610 (0: nrow: 16 rrow: 16)
leaf: 0x3c0041b 62915611 (1: nrow: 16 rrow: 16)
leaf: 0x3c00419 62915609 (2: nrow: 1 rrow: 1)
----- end tree dump
需要注意的是,数据块被放入freelist的条件是该数据块上的实际记录数(rrow)为0,而不是等待删除这些数据的事务提交:
HELLODBA.COM> delete from idx_split where a between 17*3 and 32*3;
16 rows deleted.
HELLODBA.COM> alter session set events 'immediate trace name treedump level 199127';
Session altered.
16 rows deleted.
HELLODBA.COM> alter session set events 'immediate trace name treedump level 199127';
Session altered.
事务未提交,但从跟踪文件可以看到数据块已经被放到freelist上去了:
kdimod adding block to free list,dba 0x03c0040d,time 14:35:35.582
----- begin tree dump
branch: 0x3c00405 62915589 (0: nrow: 4, level: 1)
leaf: 0x3c00413 62915603 (-1: nrow: 16 rrow: 16)
leaf: 0x3c0040d 62915597 (0: nrow: 16 rrow: 0)
leaf: 0x3c0040e 62915598 (1: nrow: 16 rrow: 16)
leaf: 0x3c0040f 62915599 (2: nrow: 16 rrow: 16)
----- end tree dump
----- begin tree dump
branch: 0x3c00405 62915589 (0: nrow: 4, level: 1)
leaf: 0x3c00413 62915603 (-1: nrow: 16 rrow: 16)
leaf: 0x3c0040d 62915597 (0: nrow: 16 rrow: 0)
leaf: 0x3c0040e 62915598 (1: nrow: 16 rrow: 16)
leaf: 0x3c0040f 62915599 (2: nrow: 16 rrow: 16)
----- end tree dump
如果此时发生分裂,因为该数据块在freelist的前列,因此仍然会被获取到,但是,由于其上面的事务并未提交,所以不会被分配:
splitting leaf,dba 0x03c0040f,time 14:35:35.644
kdisnew_bseg_srch_cbk rejecting block ,dba 0x03c0040d,time 14:35:35.644
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x03c0040f,time 14:35:35.660
kdisnew_bseg_srch_cbk rejecting block ,dba 0x03c0040f,time 14:35:35.660
kdisnew_bseg_srch_cbk using block,dba 0x03c00410,time 14:35:35.676
kdisnew_bseg_srch_cbk rejecting block ,dba 0x03c0040d,time 14:35:35.644
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x03c0040f,time 14:35:35.660
kdisnew_bseg_srch_cbk rejecting block ,dba 0x03c0040f,time 14:35:35.660
kdisnew_bseg_srch_cbk using block,dba 0x03c00410,time 14:35:35.676
下一次分裂时,由于其还在freelist,但事务仍未提交,会再次发生这一过程——这就导致了IO的增加:
splitting leaf,dba 0x03c00410,time 14:35:35.738
kdisnew_bseg_srch_cbk rejecting block ,dba 0x03c0040d,time 14:35:35.738
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x03c00410,time 14:35:35.738
kdisnew_bseg_srch_cbk rejecting block ,dba 0x03c00410,time 14:35:35.754
kdisnew_bseg_srch_cbk using block,dba 0x03c00406,time 14:35:35.754
kdisnew_bseg_srch_cbk rejecting block ,dba 0x03c0040d,time 14:35:35.738
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x03c00410,time 14:35:35.738
kdisnew_bseg_srch_cbk rejecting block ,dba 0x03c00410,time 14:35:35.754
kdisnew_bseg_srch_cbk using block,dba 0x03c00406,time 14:35:35.754
第二种需要注意的情况是,当删除的空数据块被放置到freelist后(事务也已提交),此时它仍然在树结构中,此时如果有正好属于该数据块在树中位置的数据被插入,数据仍然会被写入该数据块上,但并不从freelist上移走:
HELLODBA.COM> delete from idx_split where a between 17*3 and 32*3;
16 rows deleted.
HELLODBA.COM> commit;
Commit complete.
HELLODBA.COM> insert into idx_split (a, b, c) values (17*3, lpad('A', 100, 'A'), sysdate);
1 row created.
HELLODBA.COM> alter session set events 'immediate trace name treedump level 199127';
Session altered.
HELLODBA.COM> insert into idx_split (a, b, c) values (65*3, lpad('A', 100, 'A'), sysdate);
1 row created.
HELLODBA.COM> alter session set events 'immediate trace name treedump level 199127';
Session altered.
--跟踪内容
kdimod adding block to free list,dba 0x03c00411,time 14:46:29.181
----- begin tree dump
branch: 0x3c00405 62915589 (0: nrow: 4, level: 1)
leaf: 0x3c00420 62915616 (-1: nrow: 16 rrow: 16)
leaf: 0x3c00411 62915601 (0: nrow: 16 rrow: 0)
leaf: 0x3c00412 62915602 (1: nrow: 16 rrow: 16)
leaf: 0x3c00413 62915603 (2: nrow: 16 rrow: 16)
----- end tree dump
*** 2009-10-09 14:46:53.229
----- begin tree dump
branch: 0x3c00405 62915589 (0: nrow: 4, level: 1)
leaf: 0x3c00420 62915616 (-1: nrow: 16 rrow: 16)
leaf: 0x3c00411 62915601 (0: nrow: 1 rrow: 1)
leaf: 0x3c00412 62915602 (1: nrow: 16 rrow: 16)
leaf: 0x3c00413 62915603 (2: nrow: 16 rrow: 16)
----- end tree dump
16 rows deleted.
HELLODBA.COM> commit;
Commit complete.
HELLODBA.COM> insert into idx_split (a, b, c) values (17*3, lpad('A', 100, 'A'), sysdate);
1 row created.
HELLODBA.COM> alter session set events 'immediate trace name treedump level 199127';
Session altered.
HELLODBA.COM> insert into idx_split (a, b, c) values (65*3, lpad('A', 100, 'A'), sysdate);
1 row created.
HELLODBA.COM> alter session set events 'immediate trace name treedump level 199127';
Session altered.
--跟踪内容
kdimod adding block to free list,dba 0x03c00411,time 14:46:29.181
----- begin tree dump
branch: 0x3c00405 62915589 (0: nrow: 4, level: 1)
leaf: 0x3c00420 62915616 (-1: nrow: 16 rrow: 16)
leaf: 0x3c00411 62915601 (0: nrow: 16 rrow: 0)
leaf: 0x3c00412 62915602 (1: nrow: 16 rrow: 16)
leaf: 0x3c00413 62915603 (2: nrow: 16 rrow: 16)
----- end tree dump
*** 2009-10-09 14:46:53.229
----- begin tree dump
branch: 0x3c00405 62915589 (0: nrow: 4, level: 1)
leaf: 0x3c00420 62915616 (-1: nrow: 16 rrow: 16)
leaf: 0x3c00411 62915601 (0: nrow: 1 rrow: 1)
leaf: 0x3c00412 62915602 (1: nrow: 16 rrow: 16)
leaf: 0x3c00413 62915603 (2: nrow: 16 rrow: 16)
----- end tree dump
此时如果发生分裂,该数据块仍然会被获得,但是分配失败,此时,它才会被从freelist上移走:
splitting leaf,dba 0x03c00413,time 14:47:35.58
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x03c00411,time 14:47:35.58
kdisnew_bseg_srch_cbk rejecting block ,dba 0x03c00411,time 14:47:35.74
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x03c00412,time 14:47:35.90
kdisnew_bseg_srch_cbk rejecting block ,dba 0x03c00412,time 14:47:35.90
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x03c00413,time 14:47:35.90
kdisnew_bseg_srch_cbk rejecting block ,dba 0x03c00413,time 14:47:35.90
kdisnew_bseg_srch_cbk using block,dba 0x03c00414,time 14:47:35.105
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x03c00411,time 14:47:35.58
kdisnew_bseg_srch_cbk rejecting block ,dba 0x03c00411,time 14:47:35.74
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x03c00412,time 14:47:35.90
kdisnew_bseg_srch_cbk rejecting block ,dba 0x03c00412,time 14:47:35.90
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x03c00413,time 14:47:35.90
kdisnew_bseg_srch_cbk rejecting block ,dba 0x03c00413,time 14:47:35.90
kdisnew_bseg_srch_cbk using block,dba 0x03c00414,time 14:47:35.105