oracle索引碎片

￡神魔★判官ぃ 2022-07-12 11:00 226阅读 0赞

author：skate

time：2010-05-31

--------------------

**索引页块碎片（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

\--------------------------------------------------------------------------------

\--------------------------------------------------------------------------------

|   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”来合并**

参考文档：

[http://oracleact.com/papers/index\_leaf\_block\_frag.html][http_oracleact.com_papers_index_leaf_block_frag.html]  
[http://www.devx.com/gethelpon/10MinuteSolution/16596/0/page/2][http_www.devx.com_gethelpon_10MinuteSolution_16596_0_page_2]

\----end----

[http_oracleact.com_papers_index_leaf_block_frag.html]: http://oracleact.com/papers/index_leaf_block_frag.html
[http_www.devx.com_gethelpon_10MinuteSolution_16596_0_page_2]: http://www.devx.com/gethelpon/10MinuteSolution/16596/0/page/2