垃圾索引监控与删除

      很多数据库系统从上线到运营已经过长期的的维护、版本更新以及数据库性能优化的种种原因产生大量的索引，其中不 乏大量的索引所占空间都已经远超数据所占空间，很多索引在初期设计时，可能 对系统是有用的。但是经过系统的升级、对象的调整、应用的改变，很多索引不 被使用，成为了垃圾索引。这些索引占据了大量的资源空间，不仅增加了维护量 还可能会造成数据处理时的性能问题。因此计划在本次优化结束后，进行垃圾索 引的清理工作。
一、 确定需要监控的索引范围
    方法：利用 Library cache 缓存的 SQL 查询计划可以查到被使用到的索引信息， 但是由于 Library cache 受大小限制，缓存的 SQL 并非全部保存，故计划 每半个小时进行一次查询(近乎无资源消耗)，将查询到的索引名称存储到表中。 再结合索引信息表可以缩小监控范围，减少索引监控所消耗的资源。 脚本：
while [ 1 ]
do
sqlplus query/query <<!!
insert into index_used---提前创建 用于存放索引记录
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 a.object_owner='CRM'
and a.object_name not in (select index_name from index_used);
!!
sleep 300
done
二、 索引监控
1、开启索引监控
Set pages 999;
set heading off;
spool run_monitor.sql
select 'alter index index_name||' monitoring usage;'
from dba_indexes
where owner in ('CRM')
and index_name not in
(select index_name from index_used);--排除在 index_used 中的索引，减少监控范围
spool off;
@run_monitor.sql
2、 获取结果
Select index_name,table_name,mon,used from v$object_usage; 在 v$object_usage 中 used 列的值为 No 的，即表示该索引在监控周期内没被使用。
三、 删除垃圾索引
Set pages 999;
set heading off;
spool drop_index.sql
select 'drop index '||index_name||';' from v$object_usage where used='NO';
spool off
@drop_index.sql
注：删除前备份索引 DDL 语句。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29335267/viewspace-2058634/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29335267/viewspace-2058634/