合 Oracle索引的监控
前言部分
导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，\~O(∩_∩)O\~：
① 掌握oracle中索引的监控方法
② sys.col_usage\$的初步了解
相关知识点扫盲(摘自网络)
合理的为数据库表上创建战略性索引，可以极大程度的提高查询性能。但事实上日常中我们所创建的索引并非战略性索引，恰恰是大量冗余或是根本没有用到的索引耗用了大量的存储空间，导致DML性能低下。 应用程序在开发时，可能会建立众多索引，但是这些索引的使用到底怎么样，是否有些索引一直都没有用到过，这需要我们对这些索引进行监控，以便确定他们的使用情况，并为是否可以清除它们给出依据。
冗余索引的弊端：
大量冗余和无用的索引导致整个数据库性能低下，耗用了大量的CPU与I/O开销，具体表现如下:
a、浪费大量的存储空间，尤其是大表的索引，浪费的存储空间尤其可观(索引段的维护与管理)
b、增加了DML 操作（UPDATE、INSERT、DELETE）的开销
c、耗用大量统计信息(索引)收集的时间
d、结构性验证时间
f、增加了恢复所需的时间
本文介绍两种方式：
第一：开启监控功能；
第二：查看历史的执行计划，进行分析；
索引监控的方法
方法一：开启监控功能
1、单个索引监控
a、对于单个索引的监控，可以使用下面的命令来完成
alter index \<INDEX_NAME> monitoring usage;
b、关闭索引监控
alter index \<INDEX_NAME> nomonitoring usage;
c、观察监控结果(查询v\$object_usage视图)
select * from v\$object_usage;
2、schema级别索引监控
如果我们想在系统中监控所有的索引，那么我们可以通过下面脚本实现监控数据库所有的索引。注意我们要排除一些系统表的索引、以及LOB indexes。原因有下面两个：
1：LOB indexes不能修改，否则会报ORA-22864错误（ORA-22864: cannot ALTER or DROP LOB indexes）。
2：ORA-00701: object necessary for warmstarting database cannot be altered
ORA-00701: object necessary for warmstarting database cannot be altered
00701. 00000 - "object necessary for warmstarting database cannot be altered"
*Cause: Attempt to alter or drop a database object (table, cluster, or
index) which are needed for warmstarting the database.
*Action: None.
直接执行脚本来开启索引监控，当然监控索引时长非常重要，太短的话有可能导致查询出来的数据有问题，一般建议监控一周后即可，OLAP系统则需要适当延长监控的时间。
SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' MONITORING USAGE;' enable_monitor,
'ALTER INDEX ' || owner || '.' || index_name ||
' NOMONITORING USAGE;' disable_monitor
FROM dba_indexes
WHERE INDEX_TYPE != 'LOB'
and owner IN
(SELECT username FROM dba_users WHERE account_status = 'OPEN')
AND owner NOT IN ('SYS',
'SYSTEM',
'PERFSTAT',
'MGMT_VIEW',
'MONITOR',
'SYSMAN',
'DBSNMP')
AND owner not like '%SYS%';
监控一个月就大概可以知道那些是无用的索引了。
虽然v\$object_usage表能记录索引监控和使用的状态，但它不能统计索引被使用的次数和频率，只记录了在开启索引监控的时间段索引是否被使用过，这一点要值的注意。
另外需要注意的2点：
① 10g在收集统计信息时会导致索引被监控、这并非SQL语句产生、而在11g则不会出现这种情况了
② 外键索引不会因为主表的DML操作而被监控到、不要因为该索引没用而将它给删了
个人实验
新建1个表TB_LHR_20160622，并创建2个索引：
SYS@raclhr2> select * from v\$version;
BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SYS@raclhr2> Create Table TB_LHR_20160622 nologging As select * from dba_objects;
Table created.
SYS@raclhr2> create index ind_TB_LHR_20160622_id on TB_LHR_20160622(object_id);
Index created.
SYS@raclhr2> create index ind_TB_LHR_20160622_name on TB_LHR_20160622(object_name);
Index created.
查询v\$object_usage视图，收集统计信息：
SYS@raclhr2> select * from v\$object_usage;
no rows selected
SYS@raclhr2> BEGIN
2 dbms_stats.gather_table_stats(USER,
3 'TB_LHR_20160622',
4 cascade => TRUE,
5 degree => 8);
6 END;
7 /
PL/SQL procedure successfully completed.
SYS@raclhr2> select * from v\$object_usage;
no rows selected
开启索引的监控：
SYS@raclhr2> alter index ind_TB_LHR_20160622_id monitoring usage;
Index altered.
SYS@raclhr2> COL INDEX_NAME FOR A25
SYS@raclhr2> COL TABLE_NAME FOR A20
SYS@raclhr2> COL MONITORING FOR A10
