深入理解Oracle索引(8)：如何进行索引监控分析和优化

在生产环境、我们会发现：

① 索引表空间 I/O 非常高
② "db file sequential read" 等待事件也比较高
这种迹象表明、整个数据库系统、索引的读写操作比较多、已经成为系统的主要瓶颈

一般的原因、大抵如下：
① 大量SQL均采用索引
② DML操作导致索引维护工作量暴增
③ 频繁DML导致很多索引碎片、增加I/O开销
④ 索引建立策略失误、走索引如同全表扫

如果、一张表字段30个、但索引竟有 50个！？
作为 DBA、你可能不了解业务逻辑、不敢删、也不知该删哪些、怎么办？

建议综合采用如下两种策略：
⑴ 根据原理去判断

这种情况肯定存在很多复合索引！依据复合索引的前缀性和可选性两大原理
分析这 50 个索引的具体字段的分布情况、自己做出合并、整合的判断

⑵ 利用 Oracle 索引监控特性

在典型业务周期开始之前、执行：
ora@node1> alter index <索引名> monitoring usage；

在典型业务周期结束之后、执行：
ora@node1> alter index <索引名> nomonitoring usage；

然后、查询：
ora@node1> select * from v$object_usage;

这样子、你就知道在这个典型周期之内、这个索引到底有木有用了
即便是这样了、可是、友情提示两下、上面的结论不一定正确、因为：
① 10g在收集统计信息时会导致索引被监控、这并非SQL语句产生、而在11g则不会出现这种情况了
② 外键索引不会因为主表的DML操作而被监控到、不要因为该索引没用而将它给删了

不过、这里、毕竟还会有个问题、对于一个复杂系统来说，索引的数量可能是庞大的
那么、我们该如何选择嫌疑对象、减少监控范围呢？
以下介绍两种方法：
① 利用 library cache 数据

在library cache中，存储了系统中游标的查询计划(并非全部，受library cache大小的限制)

通过视图v$sql_plan，我们可以查询到这些数据。利用这些数据，我们可以排除那些出现在查询计划中的索引：

select a.object_owner, a.object_name
  from v$sql_plan a, v$sqlarea b
 where a.sql_id = b.sql_id and a.object_type='INDEX' and 
       b.last_load_time > <START_AUDIT_DATE>

② 利用 AWR 数据

10g以后，我们可以借助 AWR 分析哪些索引被使用到了

select b.object_owner, b.object_name
  from dba_hist_snapshot a, dba_hist_sql_plan b, dba_hist_sqlstat c
 where a.snap_id = c.snap_id and
       b.sql_id=c.sql_id and 
       b.object_type = 'INDEX' and 
       a.startup_time > <START_AUDIT_DATE>

利用上述方法，过滤掉大部分肯定被使用的index后，再综合应用，选择可疑索引进行监控，找出并删除无用索引，为数据库减肥

频繁对索引字段进行 DML操作、会对索引造成大量碎片、从而极大影响索引的使用效率、并造成索引I/O的增加
那么如何进行索引碎片的分析和整理呢？

执行如下语句可监测索引的碎片情况：

analyze index <索引名> validate structure online;

select name,（del_lf_rows_len/lf_rows_len)*100 from index_stats;

其中、索引碎片率(%)=（del_lf_rows_len/lf_rows_len)*100
如果索引碎片率超过20%、则Oracle认为索引碎片已经非常严重

建议DBA编写一个检测所有索引碎片率的脚本、定期运行、保持对索引碎片率的监测
因为、进行索引碎片分析和整理是DBA日常维护的工作之一

Oracle 进行索引碎片的处理包括两种策略：
① 重建索引

alter index <索引名> rebuild;

② 压缩索引

alter index <索引名> coalesce；

不过、Rocky 建议大家采取定期索引重建的策略、例如可在每个周末或者每天夜晚对挥发表的索引进行在线重建