系统优化案例----索引数量过多

系统优化案例----索引数量过多

 

----http://blog.sina.com.cn/s/blog_61cd89f60102eew4.html

    马上到年底了，对自己负责的系统从AIX主机、ORACLE数据库、WEBSPHERE中间件做了一次全面的性能评估，其中数据库里面有一个业务表索引数量竟然有27个，当然27个相对于其它系统可能是个正常数据，但是对我的系统来说，显然从数量和创建索引的质量来说都明显有问题，肯定有大量的无用索引，造成数据库维护无用索引导致数据库负载加大，也可能导致数据库优化器选择错误执行计划，现在需要删除无用索引，但是作为专职DBA对于那个索引是必要的那个是没有必要的，说实话我还真不知道，但是可以通过索引监控删除不必要的索引。

    把业务表中所有索引开启索引监控显然不太合适，因为一方面我们开启所有索引监控对性能有一定的影响，另一方面需要监控多久要跟据业务特性，有个别业务有可能只在月底或者半年才查询一次用到某个索引，索引监控时间开的过短，会造成误删除索引，正确的方法应该是先排除在用所引，然后对无法确认索引开启监控。

第一步：排除在用索引

    跟据AWR报告中相关视图，查询当前在用的索引

select DISTINCT 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 exists (select 'X'
          from dba_indexes
         where table_name = 'T_CONTRACT_CONTENT'
           and index_name = b.object_name)

第二步：对AWR报告中没有用到的索引开启监控

1.开启索引监控

   alter index monitoring usage;

2.关闭索引监控

   alter index nomonitoring usage;

3.批量打开索引监控

  begin
   for rx in (select *
               from dba_indexes
              where owner = 'CUECM'
                and table_name = 'T_CONTRACT_CONTENT') loop
    execute immediate 'alter index ' || rx.owner || '.' || rx.index_name ||
                      ' monitoring usage';
   end loop;
  end;
4.批量关闭索引监控

  begin
   for rx in (select *
               from dba_indexes
              where table_name = 'T_CONTRACT_CONTENT') loop
    execute immediate 'alter index ' || rx.owner || '.' || rx.index_name ||
                      ' nomonitoring usage';
   end loop;
 end;
第三步：查询索引使用情况

select * from v$object_usage;
INDEX_NAME       TABLE_NAME     MONITORING USED   START_MONITORING    END_MONITORING
-----------      ----------     ---------- ----   ----

IDX_WXW          DBA_OBJECT        YES      YES  05/22/2007 14:02:51
第四步：删除在监控时间段时没有索引的索引

drop index index_name;

注意，索引监控要跟据业务情况确定索引监控的时间，监控时间太短容易误删除有用索引，切记！