分类： Oracle
2012-02-27 14:34:43
索引页块碎片（Index Leaf Block Fragmentation）
这篇文章将会回答如下问题：
什么是索引页块碎片？什么时候被重用？
什么是半空索引碎片？什么时候被重用？
oracle的标准索引结构是B×tree结构，一个B×tree结构由三种block组成
根块(root block):在B×tree里有且只有一个block，所有访问索引都从这开始，root block下有很多child blocks。
分支块（Branch blocks）:这是中间层，branch block是没有什么限制的，它是随着leaf block的增加而增加的，branch block一般是4层，如果多于4层，就影响性能了。在我们删除行时，branch block是不被删除的。
叶块（leaf block）：叶块是最底层，上面存储着索引条目和rowid
索引和表数据是级联关系的，当删除表数据的时候，索引条目也会被自动删除，这样在index leaf
block就会产生碎片，这也就是在OLTP系统上有大量更新的表上不建议创建大量的索引，很影响性能
有的人说删除过的索引条目空间不会被再用，因为在应用中不会再有insert相同的数据。其实这个
说法不完全对的，除了半空叶块外，其他的删除的索引空间是可被再利用的。
eg：
本文的所有实验都是在如下平台测试：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL>
SQL> create table test_idx as select seq_test.nextval id,2000 syear, a.* from d
ba_objects a;
表已创建。
SQL> insert into test_idx select seq_test.nextval id,2001 syear, a.* from dba_o
bjects a;
已创建50780行。
SQL> insert into test_idx select seq_test.nextval id,2002 syear, a.* from dba_o
bjects a;
已创建50780行。
SQL> commit;
提交完成。
SQL> desc test_idx
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER
SYEAR NUMBER
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> create unique index idx_test on test_idx(syear,id) ;
索引已创建。
SQL>
SQL> select segment_name , bytes/1024/1024 ,
2 blocks, tablespace_name , extents
3 from dba_segments
4 where segment_name = 'IDX_TEST';
SEGMENT_NA BYTES/1024/1024 BLOCKS TABLESPACE_NAME EXTENTS
---------- --------------- ---------- ------------------------------ ----------
IDX_TEST 4 512 USERS 19
SQL>
SQL> col object_name for a10
SQL>
SQL> select object_name, object_id, data_object_id
2 from dba_objects
3 where object_NAME='IDX_TEST' ;
OBJECT_NAM OBJECT_ID DATA_OBJECT_ID
---------- ---------- --------------
IDX_TEST 59545 59545 ---------Used to join X$BH table（从x$bh查询缓存blocks，要用DATA_OBJECT_ID）
SQL>
查看系统现在缓存多少，这个要用sysdba用户执行
SQL> select count(*) from x$bh where obj=59545 ;
COUNT(*)
----------
17
查看执行计划：
SQL> set autot trace exp
SQL> select syear,id from test_idx where syear>=2000 and syear<=2002;
执行计划
----------------------------------------------------------
Plan hash value: 285868359
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 181K| 4613K| 102 (5)| 00:00:02
|
|* 1 | INDEX FAST FULL SCAN| IDX_TEST | 181K| 4613K| 102 (5)| 00:00:02
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SYEAR">=2000 AND "SYEAR"<=2002)
Note
-----
- dynamic sampling used for this statement
SQL>
执行一次查询，让oracle缓存相应的索引block
SQL> set autot trace statis
SQL> select syear,id from test_idx where syear>=2000 and syear<=2002;
已选择152340行。
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
10562 consistent gets
411 physical reads
0 redo size
2964382 bytes sent via SQL*Net to client
112105 bytes received via SQL*Net from client
10157 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
152340 rows processed
SQL> select syear,id from test_idx where syear>=2000 and syear<=2002;
已选择152340行。
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
10562 consistent gets
0 physical reads
0 redo size
2964382 bytes sent via SQL*Net to client
112105 bytes received via SQL*Net from client
10157 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
152340 rows processed
SQL>
这个时候再看看oracle缓存了多少
SQL> select count(*) from x$bh where obj=59545 ;
COUNT(*)
----------
438
由原来的17增加到438
SQL> analyze index idx_test validate structure;
索引已分析
SQL> select height, blocks, lf_blks, lf_rows, br_blks, br_rows , del_lf_rows
om index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS BR_BLKS BR_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- ---------- -----------
2 512 418 152340 1 417 0
SQL>
这个索引idx_test共有418个叶块都已经被缓存里了，和预期的是一样的，下面删除三分之一的数据
SQL> delete from test_idx where syear=2001;
已删除50780行。
SQL> commit;
提交完成。
SQL>
清空数据缓存
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush buffer_cache;
系统已更改。
再次查询，发现缓存数有所下降了，从438到396
SQL> select count(*) from x$bh where obj=59545 ;
COUNT(*)
----------
396
再次执行查询，让其缓存索引块
SQL> set autot trace stat
SQL> select syear,id from test_idx where syear>=2000 and syear<=2002;
已选择101560行。
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7186 consistent gets
425 physical reads
0 redo size
1976416 bytes sent via SQL*Net to client
74870 bytes received via SQL*Net from client
6772 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
101560 rows processed
SQL>
这次查询缓存的数量发现突然增加很多，从438增加到774
SQL> select count(*) from x$bh where obj=59545 ;
COUNT(*)
----------
774
突然增加这么多，推测是因为删除的那些空索引块需要重新从磁盘加载到buffer cache中，所以
缓存的会突然增加，用alter system flush buffer_cache不能完全清楚data cache，下面我reboot
数据库，再来查看下
重启数据库是为了完全清空缓存的索引
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 574619648 bytes
Fixed Size 1297944 bytes
Variable Size 192938472 bytes
Database Buffers 373293056 bytes
Redo Buffers 7090176 bytes
数据库装载完毕。
数据库已经打开。
执行查询，使索引缓存
SQL> select syear,id from test_idx where syear>=2000 and syear<=2002;
已选择101560行。
再来看缓存的多少
SQL> select count(*) from x$bh where obj=59545 ;
COUNT(*)
----------
425
我可以从查询结果中看到，缓存结果425和删除前的438，没有太大的变化，而我删除了三分之一的
数据，按理论说应该缓存的表很少了啊，我们在查看现在的叶块是多少
SQL> analyze index idx_test validate structure;
索引已分析
SQL> select height, blocks, lf_blks, lf_rows, br_blks, br_rows , del_lf_rows fr
om index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS BR_BLKS BR_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- ---------- -----------
2 512 418 152340 1 417 50780
SQL>
从结果来看，叶块和删除前一样418没有变化，这就进一步证明索引叶block虽然被删除了，但是并没有
释放空间，而查询语句并不会跳过这些删除的索引块，所以这些碎片对性能产生很多的影响。
那如何完全删除索引叶块呢？
SQL> alter index idx_test rebuild nologging online;
索引已更改。
SQL> analyze index idx_test validate structure;
索引已分析
SQL> select height, blocks, lf_blks, lf_rows, br_blks, br_rows , del_lf_rows fr
om index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS BR_BLKS BR_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- ---------- -----------
2 384 276 101560 1 275 0
SQL>
SQL> select count(*) from x$bh where obj=59545 ;
COUNT(*)
----------
139
SQL>
通过以上结果可以看到删除的索引叶块的空间被释放了
在删除了2001年后 在insert2003年的
SQL> insert into test_idx select seq_test.nextval id,2003 syear, a.* from dba_
objects a;
已创建50781行。
SQL> commit;
提交完成。
SQL> select segment_name , bytes/1024/1024 ,
2 blocks, tablespace_name , extents
3 from dba_segments
4 where segment_name = 'IDX_TEST';
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024 BLOCKS TABLESPACE_NAME EXTENTS
--------------- ---------- ------------------------------ ----------
IDX_TEST
4 512 USERS 19
SQL> analyze index idx_test validate structure;
索引已分析
SQL> select height, blocks, lf_blks, lf_rows, br_blks, br_rows , del_lf_rows fr
om index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS BR_BLKS BR_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- ---------- -----------
2 512 403 152341 1 402 0
SQL>
从查询结果来看，索引的总的块数为512，在delete和insert后没有增长，说明索引删除的空间
被重用了啊
什么是半空叶块（Half Empty Leaf Blocks）
一个叶块（ Leaf Block）是用索引键值初始化的，当某些键值被删除后，这个叶块即包含删除的
索引键值，也包含未删除的索引键值，这时这个块就被称为”Half Empty Leaf Blocks“。
下面还是以test_idx为例
SQL> insert into test_idx select seq_test.nextval id,2005 syear, a.* from dba_
objects a ;
已创建50781行。
SQL> insert into test_idx select seq_test.nextval id,2005 syear, a.* from dba_
objects a ;
已创建50781行。
SQL> insert into test_idx select seq_test.nextval id,2005 syear, a.* from dba_
objects a ;
已创建50781行。
SQL> insert into test_idx select seq_test.nextval id,2005 syear, a.* from dba_
objects a ;
已创建50781行。
SQL> commit;
提交完成。
SQL> select segment_name , bytes/1024/1024 ,
2 blocks, tablespace_name , extents
3 from dba_segments
4 where segment_name = 'IDX_TEST';
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024 BLOCKS TABLESPACE_NAME EXTENTS
--------------- ---------- ------------------------------ ----------
IDX_TEST
9 1152 USERS 24
SQL> delete from test_idx where syear=2005 and mod(id,2)=0;
已删除101562行。
SQL> commit;
提交完成。
在重新插入101562行数据
SQL> insert into test_idx select seq_test.nextval id,2006 syear, a.* from dba_
objects a ;
已创建50781行。
SQL> insert into test_idx select seq_test.nextval id,2006 syear, a.* from dba_
objects a ;
已创建50781行。
SQL> select segment_name , bytes/1024/1024 ,
2 blocks, tablespace_name , extents
3 from dba_segments
4 where segment_name = 'IDX_TEST';
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024 BLOCKS TABLESPACE_NAME EXTENTS
--------------- ---------- ------------------------------ ----------
IDX_TEST
11 1408 USERS 26
SQL>
删除了101562行数据，再重新添加101562行数据，可索引块却增加了1408-1152=256个数据块，所以说半空块
索引并没有被重用。从下面的trace也可以看出
SQL> select object_id from dba_objects where object_name='IDX_TEST';
OBJECT_ID
----------
59545
得到tree的dump的命令如下
SQL> alter session set events 'immediate trace name treedump level 59545';
会话已更改。
然后查看对应的trace文件，如下所示：
branch: 0x100972c 16815916 (0: nrow: 3, level: 2)
branch: 0x1007fe5 16809957 (-1: nrow: 511, level: 1)
leaf: 0x100972d 16815917 (-1: nrow: 378 rrow: 378)
leaf: 0x100972e 16815918 (0: nrow: 378 rrow: 378)
.
.
.
leaf: 0x1007ff2 16809970 (14: nrow: 400 rrow: 400)
leaf: 0x1007ff6 16809974 (15: nrow: 400 rrow: 332)
leaf: 0x1007ffa 16809978 (16: nrow: 400 rrow: 200)
leaf: 0x1007ffe 16809982 (17: nrow: 400 rrow: 200)----------------- Half empty blocks
leaf: 0x1008002 16809986 (18: nrow: 400 rrow: 200)
.
.
.
leaf: 0x1009f86 16818054 (19: nrow: 400 rrow: 200)
leaf: 0x1009f4b 16817995 (20: nrow: 400 rrow: 400)
.
.
.
leaf: 0x1009f4f 16817999 (21: nrow: 400 rrow: 400)
leaf: 0x100a15f 16818527 (274: nrow: 56 rrow: 56)
leaf: 0x1007ffe 16809982 (17: nrow: 400 rrow: 200)
解释： leaf block包含400行，这个块已经删除了200行的键值
识别索引是否有碎片
获得关于索引的信息，用下面的命令
analyze index index_name validate structure 或validate index index_name
analyze index index1 validate structure：是用来分析索引的数据块是否有坏块，以及根据分析得到的
数据（存放在index_stats）來判断索引是否需要重新建立。
运行命令后，然后在视图 index_stats查询，这个视图记录索引的一些信息，这个视图只有一个记录，所以在同一时间
只能分析一个索引。
1.删除的行数如占总的行数的30%，即del_lf_rows / lf_rows > 0.3，那就考虑索引碎片整理
2.如果”hight“大于4，可以考虑碎片整理
3.如果索引的行数（LF_rows）远远小于‘LF_BLKS’ ，那就说明有了一个大的删除动作，需要整理碎片
索引碎片整理方法
1. recreate index
2. alter index skate.idx_test rebuild nologging parallel 4 online ;
3. 如果叶块是half-empty or near empty，可以运行“alter index coalesce”来合并
