Oracle 未链接空索引块的开销（转）

转自：http://yumianfeilong.com/html/2008/09/21/225.html

索引的空块和空闲空间是可以重用的。只说B-tree索引。当某个索引叶节点里的index entry都被删除掉的时候，这个索引块被放置在索引的freelist上，但这个索引块仍然在索引树型结构中，它的branch块仍然有指针指向它，它自己也有pre pointer和after pointer指向它的前后叶兄弟。

当index split的时候，oracle需要分配新的block给索引，这个在freelist上的block就需要从索引树型结构上从现有位置移动到新的位置，和不同的leaf block,branch block建立新的链接关系。这个就是unlinking empty index block。

由于index block中不存在它的parent index block的指针(branch block里有指针指向leaf block)，unlink 使用什么算法定位parent index block并将parent block中的point entry抹掉的那。

猜测，通过empty index block，找到临近的兄弟节点，通过兄弟节点中的index value作index range scan，找到他们的parent block。当然又要扫描一遍root block.

发生unlinking empty index block的时候，必然会需要额外的IO操作去维护树型链接结构。

测试环境

create table test (name varchar2(300));
insert into test values (rpad('0',2000,'0') ||'a');
insert into test values (rpad('0',2000,'0') ||'b');
insert into test values (rpad('0',2000,'0') ||'c');
insert into test values (rpad('0',2000,'0') ||'d');
insert into test values (rpad('0',2000,'0') ||'e');
insert into test values (rpad('0',2000,'0') ||'f');
insert into test values (rpad('0',2000,'0') ||'g');
insert into test values (rpad('0',2000,'0') ||'h');
insert into test values (rpad('0',2000,'0') ||'i');
insert into test values (rpad('0',2000,'0') ||'j');
insert into test values (rpad('0',2000,'0') ||'k');
insert into test values (rpad('0',2000,'0') ||'m');
insert into test values (rpad('0',2000,'0') ||'n');
insert into test values (rpad('0',2000,'0') ||'o');
insert into test values (rpad('0',2000,'0') ||'p');
insert into test values (rpad('0',2000,'0') ||'q');
insert into test values (rpad('0',2000,'0') ||'r');
insert into test values (rpad('0',2000,'0') ||'s');
insert into test values (rpad('0',2000,'0') ||'t');
insert into test values (rpad('0',2000,'0') ||'ta');
insert into test values (rpad('0',2000,'0') ||'tb');
insert into test values (rpad('0',2000,'0') ||'x');
insert into test values (rpad('0',2000,'0') ||'y');

SQL> create index testindx on test(name);
Index created.

SQL> select * from dba_objects where object_name='TESTINDX';
OBJECT_ID
-----------
281297

SQL>ALTER SESSION SET EVENTS 'immediate trace name treedump level 281297';

*** SESSION ID:(1481.38926) 2008-09-20 20:48:54.874
----- begin tree dump
branch: 0x6c1bdfe 113360382 (0: nrow: 2, level: 2)
branch: 0x6c1be04 113360388 (-1: nrow: 4, level: 1)
leaf: 0x6c1bdff 113360383 (-1: nrow: 3 rrow: 3)
leaf: 0x6c1be00 113360384 (0: nrow: 3 rrow: 3)
leaf: 0x6c1be01 113360385 (1: nrow: 3 rrow: 3)
leaf: 0x6c1be02 113360386 (2: nrow: 3 rrow: 3)
branch: 0x6c1be07 113360391 (0: nrow: 3, level: 1)
leaf: 0x6c1be03 113360387 (-1: nrow: 3 rrow: 3)
leaf: 0x6c1be05 113360389 (0: nrow: 3 rrow: 3)
leaf: 0x6c1be06 113360390 (1: nrow: 3 rrow: 3)
----- end tree dump

现在我们就有了如上的树型结构，root block下有2个branch block,分别挂着4个，3个leaf block。

然后我们进行删除操作，将一个index leaf block中所有的index entry都删除掉。

SQL> delete from test where name like '%d';
1 row deleted.

SQL> delete from test where name like '%e';
1 row deleted.

SQL> delete from test where name like '%f';
1 rows deleted.

SQL> commit;
Commit complete.

SQL> conn /
Connected.
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 281297';
Session altered.

----- begin tree dump
branch: 0x6c1bdfe 113360382 (0: nrow: 2, level: 2)
branch: 0x6c1be04 113360388 (-1: nrow: 4, level: 1)
leaf: 0x6c1bdff 113360383 (-1: nrow: 3 rrow: 3)
leaf: 0x6c1be00 113360384 (0: nrow: 3 rrow: 0)
leaf: 0x6c1be01 113360385 (1: nrow: 3 rrow: 3)
leaf: 0x6c1be02 113360386 (2: nrow: 3 rrow: 3)
branch: 0x6c1be07 113360391 (0: nrow: 3, level: 1)
leaf: 0x6c1be03 113360387 (-1: nrow: 3 rrow: 3)
leaf: 0x6c1be05 113360389 (0: nrow: 3 rrow: 3)
leaf: 0x6c1be06 113360390 (1: nrow: 3 rrow: 3)

leaf index bock 0×6c1be00 里的index entry都被删除掉了（rrow=0），这个block当前在freelist里，且仍在树型结构中。

每个index leaf block只存放3个index entry (nrow=3)。如果我们继续往树的最右边insert，将发生99:1 的index split，且新的纪录将插入到空的leaf block0×6c1be00 里。

同时观察需要使用的IO和event 10224的输出。

SQL> select * from v$statname where STATISTIC#=40 or STATISTIC#=41;
STATISTIC# NAME
---------- ----------------
40 db block gets
41 consistent gets

SQL> select * from v$mystat where STATISTIC# in (40,41);
SID STATISTIC# VALUE
---------- ---------- ----------
1763 40 3
1763 41 88

SQL> alter session set events '10224 trace name context forever,level 16';
Session altered.
/*ORA-10224: index block split/delete trace */

SQL> select * from v$mystat where STATISTIC# in (40,41);

SID STATISTIC# VALUE
---------- ---------- ----------
1763 40 3
1763 41 88

SQL> insert into test values (rpad('0',2000,'0') ||'x');
1 row created.

SQL> select * from v$mystat where STATISTIC# in (40,41);
SID STATISTIC# VALUE
---------- ---------- ----------
1763 40 35
1763 41 91

这个 (rpad(’0′,2000,’0′) ||’x') 将导致99:1的index leaf block split. “db block gets”增加了32。


SQL> insert into test values (rpad('0',2000,'0') ||'y');
1 row created.

SQL> select * from v$mystat where STATISTIC# in (40,41);
SID STATISTIC# VALUE
---------- ---------- ----------
1763 40 40
1763 41 93

插入这个(rpad(’0′,2000,’0′) ||’y')并不会导致index block split，”db block gets”增加了5。


察看trace日志和新的B-tree树结构。

SQL> alter session set events '10224 trace name context off';
Session altered.
SQL> exit

*** SESSION ID:(1763.44666) 2008-09-20 21:02:34.228
splitting leaf,dba 0x06c1be06,time 21:02:34.228
kdisnew first,dba 0x06c1be00,time 21:02:34.230
delete leaf,dba 0x06c1be00,time 21:02:34.230
kdisdelbr sno 0,dba 0x06c1be04,time 21:02:34.231
kdisnew using block,dba 0x06c1be00,time 21:02:34.231

SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 281297';
Session altered.

*** SESSION ID:(5284.64299) 2008-09-20 21:09:35.046
----- begin tree dump
branch: 0x6c1bdfe 113360382 (0: nrow: 2, level: 2)
branch: 0x6c1be04 113360388 (-1: nrow: 3, level: 1)
leaf: 0x6c1bdff 113360383 (-1: nrow: 3 rrow: 3)
leaf: 0x6c1be01 113360385 (0: nrow: 3 rrow: 3)
leaf: 0x6c1be02 113360386 (1: nrow: 3 rrow: 3)
branch: 0x6c1be07 113360391 (0: nrow: 4, level: 1)
leaf: 0x6c1be03 113360387 (-1: nrow: 3 rrow: 3)
leaf: 0x6c1be05 113360389 (0: nrow: 3 rrow: 3)
leaf: 0x6c1be06 113360390 (1: nrow: 3 rrow: 3)
leaf: 0x6c1be00 113360384 (2: nrow: 2 rrow: 2)
----- end tree dump

0×06c1be06发生了index leaf split;然后发生了empty index block unlink过程， 0×06c1be00从branch 0×6c1be04 移动到了branch 0×6c1be07 下。

这些额外的IO，都是为了维护树型结构和空闲索引块空间重用。

再看index leaf block 99:1 split需要的IO。

现在的索引结构。如果再往0×6c1be08 插入新的纪录，将发生split.

*** 2008-09-20 22:38:18.934
*** SESSION ID:(2044.8187) 2008-09-20 22:38:18.932
----- begin tree dump
branch: 0x6c1bdfe 113360382 (0: nrow: 3, level: 2)
branch: 0x6c1be04 113360388 (-1: nrow: 3, level: 1)
leaf: 0x6c1bdff 113360383 (-1: nrow: 3 rrow: 3)
leaf: 0x6c1be01 113360385 (0: nrow: 3 rrow: 3)
leaf: 0x6c1be02 113360386 (1: nrow: 3 rrow: 3)
branch: 0x6c1be07 113360391 (0: nrow: 4, level: 1)
leaf: 0x6c1be03 113360387 (-1: nrow: 3 rrow: 3)
leaf: 0x6c1be05 113360389 (0: nrow: 3 rrow: 3)
leaf: 0x6c1be06 113360390 (1: nrow: 3 rrow: 3)
leaf: 0x6c1be00 113360384 (2: nrow: 3 rrow: 3)
branch: 0x6c1be09 113360393 (1: nrow: 1, level: 1)
leaf: 0x6c1be08 113360392 (-1: nrow: 3 rrow: 3)
----- end tree dump

SQL> conn /
Connected.
SQL> select * from v$mystat where STATISTIC# in (40,41);
SID STATISTIC# VALUE
---------- ---------- ----------
4888 40 3
4888 41 88

SQL> insert into test values (rpad('0',2000,'0') ||'zz');
1 row created.

SQL> commit;

Commit complete.

SQL> select * from v$mystat where STATISTIC# in (40,41);
SID STATISTIC# VALUE
---------- ---------- ----------
4888 40 26
4888 41 92

branch: 0x6c1bdfe 113360382 (0: nrow: 3, level: 2)
branch: 0x6c1be04 113360388 (-1: nrow: 3, level: 1)
leaf: 0x6c1bdff 113360383 (-1: nrow: 3 rrow: 3)
leaf: 0x6c1be01 113360385 (0: nrow: 3 rrow: 3)
leaf: 0x6c1be02 113360386 (1: nrow: 3 rrow: 3)
branch: 0x6c1be07 113360391 (0: nrow: 4, level: 1)
leaf: 0x6c1be03 113360387 (-1: nrow: 3 rrow: 3)
leaf: 0x6c1be05 113360389 (0: nrow: 3 rrow: 3)
leaf: 0x6c1be06 113360390 (1: nrow: 3 rrow: 3)
leaf: 0x6c1be00 113360384 (2: nrow: 3 rrow: 3)
branch: 0x6c1be09 113360393 (1: nrow: 2, level: 1)
leaf: 0x6c1be08 113360392 (-1: nrow: 3 rrow: 3)
leaf: 0x6c1be0a 113360394 (0: nrow: 1 rrow: 1)

如上，插入纪录 (rpad(’0′,2000,’0′) ||’zz’)，leaf 0×6c1be08 发生了99:1 的split,新纪录插入到0×6c1be0a 中，增加“db block gets“ 23。

这表示单纯的unlink empty index block的“db block gets”为9 （32-23=9），单纯的99:1 index leaf block split的“db block gets”为18 （23-5=18）,单纯的right hand index entry insert的“db block gets”为5.

我们可以看到索引的维护成本不是一般的高。