本帖最后由 wei-xh 于 2013-7-11 10:45 编辑
不知道你是否对这些有过疑问？
我随机的删除了表里的一些记录，我不知道我的索引空间是不是会被重用？
我删除了表里大部分记录，我不知道表上一个自增的索引的空间会不会被重用？
我列举了三种场景下索引空间的重用，希望对你有一点点的帮助和提高。
【场景一】
测试的思路：
1)删除一个索引块里的某几个entry，但是不要删除完。之所以不删除完，是考虑到，如果索引块内的entry被完全删除，那么这个索引块会被加入到索引段头的空闲bitmap里，这个场景是我们测试的第二个场景。
2)新开一个事物，往表里新插入一条记录，观察新插记录会不会导致ORACLE清除索引块里的deleted entry。
我们先准备下实验的环境：创建测试表，共9条数据，在id列上创建索引
drop table test;
create table test as select rownum id,object_name from dba_objects where rownum<10;
create index i on test(id);
我们用ANALYZE对索引做一下分析，然后看一下我们本次实验比较care的几个统计值。
ANALYZE INDEX i VALIDATE STRUCTURE;
select LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_ROWS DEL_LF_ROWS
---------- -----------
9 0
LF_ROWS：代表索引上总共的entry数(包含deleted的entry)。
DEL_LF_ROWS：代表索引上总共的deleted的entry，这些entry还没有被cleanout。
采用ANALYZE分析索引后，我们可以知道这个索引一共9个entry，还没有被删除的entry：DEL_LF_ROWS的值为0。
我们继续实验，删除4个entry：
DELETE test WHERE id in (2,4,6,8);
commit;
ANALYZE INDEX i VALIDATE STRUCTURE;
select LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_ROWS DEL_LF_ROWS
---------- -----------
9 4
查询的结果跟我们的预期相符。索引块内共9个entry，其中4个是被删除的。
我们可以通过ORACLE提供的索引tree dump工具来观察下这个时候索引entry的样子：
tree dump:
col object_name for a30
select object_name,object_id from dba_objects where object_name in ('I');
OBJECT_NAME OBJECT_ID
------------------------------ ----------
I 119015
alter session set events 'immediate trace name treedump level 119023';
tree dump 里的内容：
----- begin tree dump
leaf: 0x119994c 18454860 (0: nrow: 9 rrow: 5)
----- end tree dump
从tree dump可以看到索引只有一个块，这是由于实验的数据量非常少，这个块既充当了根块也充当了分支块还充当了叶子块。
根据leaf: 0x119994c 18454860 (0: nrow: 9 rrow: 5)，我们知道这个索引块的地址18454860，共有9个entry(nrow: 9),实际存在的entry有5个(rrow: 5)，说明有4个是delete的。
我们再来看看此时索引块内的entry长什么样子：
根据索引块地址18454860 ，我们来通过工具包转换成我们需要的格式，然后dump这个索引块。
SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18454860),DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18454860)FROM dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18454860) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18454860)
---------------------------------------------- -----------------------------------------------
4 1677644
alter system dump datafile 4 block 1677644;
dump的结果：
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 19 99 44 00 00row#1[8008] flag: ---D--, lock: 2, len=12col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 19 99 44 00 01
row#2[7996] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 19 99 44 00 02row#3[7984] flag: ---D--, lock: 2, len=12col 0; len 2; (2): c1 05
col 1; len 6; (6): 01 19 99 44 00 03
row#4[7972] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 19 99 44 00 04row#5[7960] flag: ---D--, lock: 2, len=12col 0; len 2; (2): c1 07
col 1; len 6; (6): 01 19 99 44 00 05
row#6[7948] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 01 19 99 44 00 06row#7[7936] flag: ---D--, lock: 2, len=12col 0; len 2; (2): c1 09
col 1; len 6; (6): 01 19 99 44 00 07
row#8[7924] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 01 19 99 44 00 08
标红部分---D--，代表这个entry已经被删除，ORACLE只是做了删除标记，并未物理的释放这个entry占有的空间。
下面的步骤就比较重要了，我们往表里插入一条记录：
insert into test values(10,'weixh');
1 row created.
commit;
alter system checkpoint;(写脏数据)
我们再次dump之前的索引块：
alter system dump datafile 4 block 1677644;
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 19 99 44 00 00
row#1[7996] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 19 99 44 00 02
row#2[7972] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 19 99 44 00 04
row#3[7948] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 01 19 99 44 00 06
row#4[7924] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 01 19 99 44 00 08row#5[7912] flag: ------, lock: 2, len=12------------新插入的entrycol 0; len 2; (2): c1 0b
col 1; len 6; (6): 01 19 99 46 00 00
----- end of leaf block dump -----
我们从dump的显示中可以知道，之前的索引entry已经被cleanout，也就是说空间已经被重用了，索引的最后是我们新插入的数据。
通过分析索引的统计信息，也说明了这一点：一共6个索引entry，不存在被删除的entry。
ANALYZE INDEX i VALIDATE STRUCTURE;
select LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_ROWS DEL_LF_ROWS
---------- -----------
6 0场景一总结：
1)任何一个新事务，往叶子块插入一条记录，ORACLE都会移除/释放这个索引块内所有的已被删除的entry。
2)在一个随机插入的索引列上，被删除空间的重用往往不会存在什么问题，就像我们看到的，只要一有新插入，空间就会释放。
【场景二】
很多人对于这个场景有误解：
一张表的字段id是自增的，如果我们按照id的某个范围，把记录删除。那么这些记录占用的索引空间将不会被重用。
测试思路：
1)新建一张表，表上有一个自增的索引字段，一万记录数，然后清空表。
2)往表里再插入一万记录，但是这一万记录的值要比上面步骤一里的大。
3)观察索引的叶子块数有没有变化
我们来试验验证一把。
drop table test;
create table test as select rownum id,object_name from dba_objects where rownum<10000;
create index i on test(id);
ANALYZE INDEX i VALIDATE STRUCTURE;
Index analyzed.
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 0
经过上面的步骤，我们创建了一张9999条记录的一张表，id的值从1到9999。id上有一个普通的索引。
我们然后把表清空。
delete from test;
commit;
ANALYZE INDEX i VALIDATE STRUCTURE;
Index analyzed.
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 9999
表清空后，我们分析了索引，查询INDEX_STATS视图，也印证了表里有9999条记录，而且全部是已经被删除的，这些记录一共占取了21个索引块。
然后我们往表里插入10000条记录，id的值从20001开始递增，保证不会跟上面的id有重复。如果说这些叶子块不会被重用的话，我们插入数据后，叶子块的数目应该是要变多的。
INSERT INTO test SELECT rownum+20000, 'weixh' FROM dba_objects where rownum<= 10000;
commit;
ANALYZE INDEX i VALIDATE STRUCTURE;
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 10000 0
你发现了什么？
索引叶子块的数目没发生任何的变化！而且，DEL_LF_ROWS的值为0，说明这些被删除的entry，已经被cleanout，被oracle重用了。场景二总结：
索引的叶子块被清空后，会把这个叶子块做为空闲块加入到索引段的空闲bitmap里(如果你使用的是ASSM)，这样后面的事务就可以重新使用这些空闲的索引块了。需要指出的是，这些被清空的索引块虽然被加入到了索引的空闲列表中，但是他们仍然存在在索引的逻辑结构中，被分支节点所参照(link)，直到被重新使用，才会被重新的relink。
可能需要fan qiang才能看，我把里面重要的文字摘录：
Yes, index blocks that contain nothing but deleted index entries are placed on the index freelist and can be recycled by Oracle but until they’ve been reused, they remain linked in place in the index structure. In some scenarios, these empty index blocks can be problematic and is a classic example of when an index rebuild might be beneficial.
【场景三】
这个场景知道的人比较少，可以在Richard Foote博客里找到类似的博文。
延迟块清除会对索引叶子块做cleanout，释放索引的空间。
测试思路：
1)新建一张一万记录的表，表上存在一个普通的索引
2)清空表，不要提交
3)发生检查点
4)提交步骤2的事物
5)分析索引(分析索引会导致物理读，发生延迟块清除)，查看索引统计值
drop table test;
create table test as select rownum id,object_name from dba_objects where rownum<10000;
create index i on test(id);
ANALYZE INDEX i VALIDATE STRUCTURE;
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 0
我们经过上面的步骤，创建了一个有9999条记录的表。在id上存在一个普通索引。
delete from test;
另开一个session 执行：alter system flush buffer_cache;
然后对delete的会话进行commit;
上面的步骤，在清空表数据后，把脏数据全部刷新磁盘，这样就能保证，ORACLE不能做任何的块清除。
经过上面两个场景的锻炼，我想你一定能知道，现在索引里有9999个entry，其中9999个是被删除的entry。
ANALYZE INDEX i VALIDATE STRUCTURE;
我们通过ANALYZE来分析一下索引，注意哦，这个命令会真实的去读索引块，读取索引块的过程中，发生延迟块清除。并且这里ORACLE会在延迟块清除的过程中，把索引的被删除的entry做cleanout，空间也会被释放了。
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 0 0
可以看到DEL_LF_ROWS，LF_ROWS的值都为0。索引空间已经在ANALYZE后完全释放了。(并不是ANALYZE的功劳，而是这个操作递归导致的延迟块清楚产生的效果)
来让我们具体dump下，索引叶子块现在长什么样子。如果像INDEX_STATS里显示的，那么此时索引的叶子块应该是没有entry的。
col object_name for a30
select object_name,object_id from dba_objects where object_name in ('I');
OBJECT_NAME OBJECT_ID
------------------------------ ----------
I 119023
alter session set events 'immediate trace name treedump level 119025';
Session altered.
branch: 0x119b1ec 18461164 (0: nrow: 21, level: 1)
leaf: 0x119b1ed 18461165 (-1: nrow: 0 rrow: 0)
leaf: 0x119b1ee 18461166 (0: nrow: 0 rrow: 0)
leaf: 0x119b1ef 18461167 (1: nrow: 0 rrow: 0)
leaf: 0x119b1f0 18461168 (2: nrow: 0 rrow: 0)
leaf: 0x119b1f1 18461169 (3: nrow: 0 rrow: 0)
leaf: 0x119b1f2 18461170 (4: nrow: 0 rrow: 0)
leaf: 0x119b1f3 18461171 (5: nrow: 0 rrow: 0)
leaf: 0x119b1f4 18461172 (6: nrow: 0 rrow: 0)
leaf: 0x119b1f5 18461173 (7: nrow: 0 rrow: 0)
leaf: 0x119b1f6 18461174 (8: nrow: 0 rrow: 0)
leaf: 0x119b1f7 18461175 (9: nrow: 0 rrow: 0)
leaf: 0x119b1f8 18461176 (10: nrow: 0 rrow: 0)
leaf: 0x119b1fa 18461178 (11: nrow: 0 rrow: 0)
leaf: 0x119b1fb 18461179 (12: nrow: 0 rrow: 0)
leaf: 0x119b1fc 18461180 (13: nrow: 0 rrow: 0)
leaf: 0x119b1fd 18461181 (14: nrow: 0 rrow: 0)
leaf: 0x119b1fe 18461182 (15: nrow: 0 rrow: 0)
leaf: 0x119b1ff 18461183 (16: nrow: 0 rrow: 0)
leaf: 0x119b200 18461184 (17: nrow: 0 rrow: 0)
leaf: 0x119b201 18461185 (18: nrow: 0 rrow: 0)
leaf: 0x119b202 18461186 (19: nrow: 0 rrow: 0)
----- end tree dump
随便找个叶子块dump一下。
SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18461165),DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18461165)FROM dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18461165) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18461165)
---------------------------------------------- -----------------------------------------------
4 1683949
alter system dump datafile 4 block 1683949;
Leaf block dump
===============
header address 133640292=0x7f73064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 0
kdxcofbo 36=0x24
kdxcofeo 8032=0x1f60
kdxcoavs 7996
kdxlespl 0
kdxlende 0
kdxlenxt 18461166=0x119b1ee
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
----- end of leaf block dump -----
发现索引叶子块出了块头的一些信息，没有任何的entry，真的是空空如也！被删除的索引entry都已经被ORACLE做了cleanout了。
上面的步骤里，如果你省去了flush buffer cache这个步骤，查询的INDEX_STATS的结果就会是下面这样了。由于commit发生的时候数据都在内存中，因此ORACLE会对块做快速块清除，快速的块清除不会去做索引entry的cleanout。：
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 9999
场景三的总结：
1)一些大的事物由于脏块较多，非常可能会导致事物提交的时候，一些脏块已经被刷出磁盘了
2)当这些没有清除的块再次被访问的时候，会发生延迟块清除。
3)延迟块清除会把这个块上所有的被删除的索引entry做cleanout。
后记：看了上面的三个场景，好几个同学给我留言说，我们是不是不需要在意这些被标记为删除的空间，早晚ORACLE会重用这些空间。
其实不能这么说我给你举一个场景。有一个业务表emp，上面存在一个索引id，此id列是依据序列生成，是自增的。如果你随机的删除了表里的一些记录。那么此时，索引里的情况会像我说的场景一，索引里的entry，有删除的，有没被删除的。但是你要知道的是，你的id是依序列递增的，你后续的插入，只会去增长索引的右边的叶子块，不会再用到我们之前做过删除的索引叶子块。这个情况下，这些空间就不会被重用。
如果你的id值是随机生成的，那就不一样了，一旦你插入到了之前存在删除entry的叶子块上，这些空间就可以被重用。
还有就是，虽然ORACLE的索引空间回收机制做的比较好，但是如果你索引的空间一段时间内空出很多的“GAP"，可能对于范围扫描，也会影响一些性能。因为扫描的索引叶子块变得多了一些。至于数据做了删除后，索引要不要重建，也实在是个很大话题，可以另外再讨论。实验过程中，我们用到了analyze xxx validate structure，此命令使用的时候要小心，除非你已经充分认识到这个命令的风险，否则不要轻易的在生产环境使用。此命令执行过程中，会对表加一个类型为4的TM锁，阻塞表上的一切的DML操作。
314
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
