转载

垃圾索引监控与删除

很多数据库系统从上线到运营已经过长期的的维护、版本更新以及数据库性能优化的种种原因产生大量的索引，其中不乏大量的索引所占空间都已经远超数据所占空间，很多索引在初期设计时，可能对系统是有用的。但是经过系统的升级、对象的调整、应用的改变，很多索引不被使用，成为了垃圾索引。这些索引占据了大量的资源空间，不仅增加了维护量还可能会造成数据处理时的性能问题。因此计划在本次优化结束后，进行垃圾索引的清理工作。
一、 确定需要监控的索引范围
方法：利用 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 语句。

正文到此结束