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

索引不可用

索引不可用的原因有很多，包括：
 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;

然后执行一个索引范围扫描的查询：

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;

然后按上面的方法生成并查看索引信息：

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提供了在线重建索引的方法：

alter index IDX_TEST rebuild 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不仅可以极大地提高性能，而且不填充多个重做日志文件，节省空间。

压缩索引

在非唯一索引中使用压缩，可以减少重复键占用的空间：

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

一个实例如下：

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

同样可以用于重建索引：

alter index IDX_TEST rebuild compress 1 online;

原文：http://blog.csdn.net/tomato__/article/details/39474783