Oracle性能分析9：重建索引

32e2

当索引出现问题时，会导致严重的性能问题，索引问题包括索引不可用、索引碎片导致性能下降，我们需要一些手段在检测索引的问题，并解决这些问题。这一篇将为你讲述怎么定位索引问题，并提供了解决的办法。
索引不可用

索引不可用的原因有很多，包括：
1）索引空间耗尽，导致SQL*Loader更新索引失败；
2）创建索引的过程中实例失败；
3）唯一键有重复值；
4）某个索引的顺序与sorted indexes子句中指定的顺序不同；
5）移动表或表分区（alter table move和alter table move partition）；
6）对表执行在线重定义；
7）截断表分区（alter table truncate partition）；
8）导入分区；
9）删除表分区；
10）拆分表的分区或子分区（alter table split partition）；
11）分区索引的维护操作（alter index split partition）。
除了上述的这些原因之外，你还可以手动的将索引标注为不可用，这样可以使批量加载速度更快，下面是把索引的状态改变为不可用的方法：


    alter index IDX_HISTORYALARM_HOUR$01 unusable  

如果你的索引为分区索引，这个操作将导致所有分区的索引都不可用，你也可以指定某个分区的索引不可用：


    alter index IDX_HISTORYALARM$02 modify partition HISTORYALARM20140731 unusable  

通过下面的方法可以查看索引的状态：


    select ind.INDEX_NAME,ind.status,ind.PARTITIONED from user_indexes ind where index_name like '%HISTORYALARM%'  
      
    INDEX_NAME          STATUS      PARTITIONED  
    ---------------------------------------------------------------------  
    IDX_HISTORYALARM_HOUR$01        UNUSABLE        NO  
    IDX_HISTORYALARM$02         N/A     YES  

可以看到，全局索引的状态已经变为UNUSABLE，但本地索引的状态标识为N/A，通过下面的方法可以查看本地索引在每一个分区中的索引状态：


    INDEX_NAME          PARTITION_NAME          STATUS  
    ------------------------------------------------------------------------------------------  
    IDX_HISTORYALARM$02         HISTORYALARM20140731        UNUSABLE  
    IDX_HISTORYALARM$02         HISTORYALARM20140801        USABLE  
    IDX_HISTORYALARM$02         HISTORYALARM20140802        USABLE  
    IDX_HISTORYALARM$02         HISTORYALARM20140803        USABLE  
    IDX_HISTORYALARM$02         HISTORYALARM20140804        USABLE  
    IDX_HISTORYALARM$02         HISTORYALARM20140805        USABLE  
    ......  

可以看到分区HISTORYALARM20140731的索引已经标注为UNUSABLE。
当索引被标注为不可用后，优化器就会忽略这些索引，Oracle在DML更改表时也不再维护这些索引，如果希望优化器再次使用它，就必须先重建（rebuild）索引。
索引碎片

随着时间的推移，由于大量的删除操作，索引可能会产生碎片。Oracle文档（Performance Tuning Manual for Oracle DataBase 11.2）建议运行"analyze...validate"语句来识别需要重建的索引，这个操作会将索引的统计数据放到INDEX_STATS视图中，下面是该视图中的关键列：
1）高度（HEIGHT）：索引的高度，从1开始，1代表只有根的索引；
2）块数（BLOCKS）：分配给索引的块数；
3）叶行数（LF_ROWS）：叶行数（包括已删除的行）；
4）已删除的叶行数（DEL_LF_ROWS）：已删除尚未清理的叶行条目数；
5）已用空间（USED_SPACE）：索引内使用的总空间（包括已删除的条目）；
6）已用百分比（PCT_USED）：索引内使用空间的百分比（包括已删除的条目）。。计算公式：(USED_SPACE / BTREE_SPACE) * 100;
7）B树空间（BTREE_SPACE）：索引的总大小（包括已删除的条目）。
下面通过一个例子来学习该视图的使用。
先创建一个测试表格，并在上面创建索引：


    create table test as select rownum id,'Test' text from dual connect by level <= 100000;  
    create index idx_test on test(id);  

然后执行索引分析语句：


    analyze index idx_test validate structure;  

注意在执行分析语句之前INDEX_STATS视图是空的，现在查询该视图来检查被删除的叶行数：


    select lf_rows,lf_blks,del_lf_rows from index_stats;  
      
    LF_ROWS     LF_BLKS     DEL_LF_ROWS  
    --------------------------------------------------------------------------------  
    100000      222     0  

这里可以看出删除的叶行数为0，接下来我们删除表中大量的行，再次运行分析语句：


    delete test where id <= 99999;  
    commit;  
    analyze index idx_test validate structure;  

然后查询被删除的叶行数：


    select lf_rows,lf_blks,del_lf_rows from index_stats;  
      
    LF_ROWS     LF_BLKS     DEL_LF_ROWS  
    -----------------------------------------------------------  
    100000      222     99999  

为了让Oracle能够得到正确的执行计划，我们先收集表和索引的统计信息：


    begin  
      dbms_stats.gather_table_stats(ownname   => user,  
                                    tabname => 'TEST',  
                                    cascade   => TRUE);  
    end;  

然后执行一个索引范围扫描的查询：
[plain] view plaincopy在CODE上查看代码片派生到我的代码片

    select * from test where id > 10;  
      
    call     count       cpu    elapsed       disk      query    current        rows  
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------  
    Parse        1      0.00       0.27          0          0          0           0  
    Execute      1      0.00       0.00          0          0          0           0  
    Fetch        1      0.01       1.53         56        224          0           1  
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------  
    total        3      0.01       1.81         56        224          0           1  
      
    Misses in library cache during parse: 1  
    Optimizer mode: ALL_ROWS  
    Parsing user id: 5   
      
    Rows     Row Source Operation  
    -------  ---------------------------------------------------  
          1  TABLE ACCESS BY INDEX ROWID TEST (cr=224 pr=56 pw=0 time=1538212 us)  
          1   INDEX RANGE SCAN IDX_TEST (cr=223 pr=56 pw=0 time=1527442 us)(object id 58594)  
      
      
    Elapsed times include waiting on following events:  
      Event waited on                             Times   Max. Wait  Total Waited  
      ----------------------------------------   Waited  ----------  ------------  
      SQL*Net message to client                       2        0.00          0.00  
      SQL*Net message from client                     2        0.79          0.83  
      db file sequential read                        56        0.03          0.41  
    ********************************************************************************  

查询使用了索引范围扫描，虽然整个表只有一条数据，但由于索引中的索引碎片，导致索引扫描任然读取了56个数据块。下面重建该索引：


    alter index IDX_TEST rebuild;  

然后按上面的方法生成并查看索引信息：
[plain] view plaincopy在CODE上查看代码片派生到我的代码片

    LF_ROWS LF_BLKS DEL_LF_ROWS  
    -----------------------------------  
    1   1   0  

可以看出索引碎片消失。

注意事项：

1）执行索引分析会锁定表，直到索引分析完毕才解除锁定；
2）在大多数情况下，Oracle会尽可能的重用索引中已删除条目的空间。
为什么重建索引

实际上，重建（rebuild）索引就是重新创建索引，但它比删除原索引再重新创建索引的做法要好，因为在重建索引时存储空间已分配给索引，而不需要再指定索引创建语句。
关于索引重建有很多依据，但其中有一些并不准确，下面列举了一些：
1）Oracle的B树索引随着时间的推移变得不平衡
由于B树索引的根块和所有的叶块之间的高度始终是一致的，所以这不正确。
2）索引中被删除的空间无法重用
实际上Oracle会重用被删除的空间。
3）达到一定层数的索引是低效的
索引的层数取决于索引有多少条目，重建不能解决问题。
4）具有糟糕的聚蔟因子的索引，可以通过重建修复
重建索引并不能改变表中数据行或索引的顺序，因此聚蔟因子（见使用索引的聚蔟因子）完全不受索引重建影响。如果想改进聚蔟因子，实际上需要重建表。
那么具体为什么重建索引呢？

当索引不可用时，应该重建索引，但我们是否应该重建索引来消除索引碎片呢？
当你的查询大部分都是通过索引访问读取单个行，那么重建索引对性能影响很小。但对于范围查询，由于大量的索引碎片会导致查询增加大量的无效IO，因此重建索引是有意义的，即使Oracle会重用索引碎片，但重建索引也可以使索引变得更加紧凑，从而提高查询的效率。
重建索引

上面已经使用到重建索引的方法：


    alter index IDX_TEST rebuild;  

但重建索引的过程中会对表加锁，阻止其他对表的操作，直到索引重建完成。从Oracle 10g开始，Oracle提供了在线重建索引的方法：


    <pre class="sql" name="code">alter index IDX_TEST rebuild online;  

在线重建索引不会再导致索引锁定。

重建分区索引则需要带上分区信息：


    alter index IDX_TEST rebuild partition partition_name online;  

Oracle也为创佳和重建索引提供了一些参数，用于提高创建和重建索引的效率。
并行创建或重建索引

为了创建索引，数据库需要进行全表扫描，并行创建可以加快索引的创建速度，速度的提升由并行度和CPU数量决定：


    create index IDX_TEST on test(id) parallel 4 online;  

也可以用于重建索引：


    alter index IDX_TEST rebuild parallel 4 online;  

需要注意的是这个操作将使索引的并行度（见使用索引）永远变为这个值，如下：


    select degree from user_indexes where index_name = 'IDX_TEST';  
      
    DEGREE  
    --------------------  
    4  

如果打算让数据库在处理你的索引时使用并行机制，则正好，否则，你需要在执行了并行创建和重建操作后禁用并行：


    alter index IDX_TEST noparallel;  

如果忘记禁用并行，可能会导致严重的性能问题。
在索引创建或重建时避免生成重做信息

不把创建或者重建的索引项写入重做日志，可以大大缩短索引创建或重建的时间：


    create index IDX_TEST on test(id) nologging online;  

也可以在重建索引时使用：


    alter index IDX_TEST rebuild nologging online;  

nologging不仅可以极大地提高性能，而且不填充多个重做日志文件，节省空间。
压缩索引

在非唯一索引中使用压缩，可以减少重复键占用的空间：
[plain] view plaincopy在CODE上查看代码片派生到我的代码片

    compress <数字，并小于等于索引包括的字段值>  

一个实例如下：


    create index IDX_TEST on test(id) compress 1 online;  

同样可以用于重建索引：


    alter index IDX_TEST rebuild compress 1 online;