共计 8173 个字符，预计需要花费 21 分钟才能阅读完成。
这篇文章主要介绍“oracle 索引页块碎片分析”，在日常操作中，相信很多人在 oracle 索引页块碎片分析问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”oracle 索引页块碎片分析”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！
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
SQL create table test_idx as select seq_test.nextval id,2000 syear, a.* from dba_objects a;
表已创建。
SQL insert into test_idx select seq_test.nextval id,2001 syear, a.* from dba_objects a;
已创建 50780 行。
SQL insert into test_idx select seq_test.nextval id,2002 syear, a.* from dba_objects a;
已创建 50780 行。
SQL commit;
提交完成。
SQL desc test_idx
SQL create unique index idx_test on test_idx(syear,id) ;
索引已创建。
SQL select segment_name , bytes/1024/1024 , blocks, tablespace_name , extents
from dba_segments
where segment_name = IDX_TEST
SQL select object_name, object_id, data_object_id
From dba_objects
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;
SQL
执行一次查询，让 oracle 缓存相应的索引 block
SQL set autot trace statis
SQL select syear,id from test_idx where syear =2000 and syear =2002;
已选择 152340 行。
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 From 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;
SQL commit;
清空数据缓存
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 from index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS BR_BLKS BR_ROWS DEL_LF_ROWS
———- ———- ———- ———- ———- ———- ———–
2 512 418 152340 1 417 50780
从结果来看，叶块和删除前一样 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 from 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 ,
blocks, tablespace_name , extents
from dba_segments
where segment_name = IDX_TEST
——————————————————————————–
SEGMENT_NAME BYTES/1024/1024 BLOCKS TABLESPACE_NAME EXTENTS
————— ———- —————————— ———-
IDX_TEST 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 ,
blocks, tablespace_name , extents
from dba_segments
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”来合并
到此，关于“oracle 索引页块碎片分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！
