冰刀(skate)

善于从不同角度看同一问题----这样你会站的更高、看的更远

oracle索引碎片

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

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

-

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

 

 

 


参考文档:

http://oracleact.com/papers/index_leaf_block_frag.html
http://www.devx.com/gethelpon/10MinuteSolution/16596/0/page/2

 

 

 

 

----end----

 

 

 

 

 

 

 

 

阅读更多
个人分类: Oracle Optimization
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭