ORACLE INDEX的有效性监控和分析处理

1.背景介绍

随着业务的增长，生命周期管理单纯对表的管理，已经无法满足目前公司业务增长对存储的需求，因此，急需从其它方面对模型进行精简，而索引是占存储较大的对象，由于冗余索引、无效索引、无用索引等存在，导致索引浪费存储，需要制定一套策略进行管理。

索引监控与分析则是一套很好的方法，ORACLE提供索引monitor的方法(对系统资源使用影响小)，但是无法监控索引使用频次，另一种方法可以通过使用AWR视图DBA_HIST_SQL_PLAN，可以查询到索引的使用频次，当然，有些SQL可能进入不了AWR中，因此，我们采用综合的方法：

1）从AWR中（要求30天以上）查询出未被使用的索引。

2）对1）中的索引使用ORACLE INDEX MONITOR监控30天。

   如果最终查询出的索引仍未被使用，则认为此索引是多余索引，提交对应库负责人确认后予以清除。

 

2.方案和步骤

1.  从DBA_HIST_SQL_PLAN中查询未被使用的索引，放入临时表中

CREATE TABLE index_mon_tmp TABLESPACE TBS_PERSON AS
WITH Q AS
 (SELECT S.OWNER A_OWNER,
         TABLE_NAME A_TABLE_NAME,
         INDEX_NAME A_INDEX_NAME,
         INDEX_TYPE A_INDEX_TYPE,
         SUM(S.BYTES) / 1048576 A_MB
    FROM DBA_SEGMENTS S, DBA_INDEXES I
   WHERE S.OWNER = I.OWNER
     AND S.OWNER NOT IN ('SYS','OLAPSYS','SYSMAN','EXFSYS',
     'MDSYS','ORDSYS','XDB','CTXSYS','WMSYS',
     'DBSNMP','SYSTEM','OUTLN','DMSYS','TSMSYS','SPA')
     AND INDEX_NAME = SEGMENT_NAME
   GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE
  HAVING SUM(S.BYTES) > 1048576 * 300)
 SELECT OWNER,TABLE_NAME,INDEX_NAME,INDEX_TYPE,MB
  FROM (SELECT /*+ NO_QUERY_TRANSFORMATION(S) */
         A_OWNER OWNER,
         A_TABLE_NAME TABLE_NAME,
         A_INDEX_NAME INDEX_NAME,
         A_INDEX_TYPE INDEX_TYPE,
         A_MB MB,
         DECODE(OPTIONS, NULL, '       -', OPTIONS) INDEX_OPERATION,
         COUNT(OPERATION) NR_EXEC
          FROM Q, DBA_HIST_SQL_PLAN D
         WHERE D.OBJECT_OWNER(+) = Q.A_OWNER
           AND D.OBJECT_NAME(+) = Q.A_INDEX_NAME
         GROUP BY A_OWNER,
                  A_TABLE_NAME,
                  A_INDEX_NAME,
                  A_INDEX_TYPE,
                  A_MB,
                  DECODE(OPTIONS, NULL, '       -', OPTIONS)
        HAVING COUNT(OPERATION) = 0
        );

 

 

2.  删除属于约束的索引

DELETE FROM INDEX_MON_TMP a
WHERE EXISTS(SELECT 1
FROM DBA_CONSTRAINTS b
WHERE a.owner=b.index_owner
AND a.table_name=b.table_name
AND a.index_name=b.index_name
);
COMMIT;

 

可以查询INDEX_MON_TMP看看共有多少索引未被使用，对应的大小：

SELECT COUNT(*),SUM(mb) from index_mon_tmp;

 

3.  拼语句，进行索引monitoring监控

将下列脚本保存为mon_temp.sql，然后在sqlplus里@mon_temp.sql，会生成monitoring.sql,然后@monitoring.sql即可对索引进行监控。

set line 1000 pagesize 0 feedback off echo off trimout on trimspool on termout off timing off
spool monitoring.sql
select 'alter index '||owner||'."'||index_name||'" monitoring usage;'
from INDEX_MON_TMP;
spool off

 

注意执行如果报正在使用错误（ORA-00054: resource busy），不用管，说明此索引被使用。

 

4.  执行第3步对应脚本，并建立监控视图

create  view ALL_OBJECT_USAGE
           (OWNER,
           INDEX_NAME,
           TABLE_NAME,
           MONITORING,
           USED,
           START_MONITORING,
           END_MONITORING)
           as
             select u.name, io.name, t.name,
             decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
             decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
             ou.start_monitoring,
             ou.end_monitoring
           from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
           where i.obj# = ou.obj#
           and io.obj# = ou.obj#
           and t.obj# = i.bo#
           and u.user# = io.owner#;

 

可以查询下有多少索引被监控：

SELECT COUNT(*) ALL_COUNT,
       COUNT(DECODE(USED, 'YES', 1, NULL)) USED_COUNT,
       COUNT(DECODE(USED, 'NO', 1, NULL)) NO_USED_COUNT
  FROM SYS.ALL_OBJECT_USAGE
 WHERE END_MONITORING IS NULL;

5.  查询监控视图，30天后停止监控

SELECT start_monitoring FROM sys.ALL_OBJECT_USAGE WHERE END_MONITORING IS NULL and ROWNUM=1;--查询监控开始时间

停止监控，拼脚本如下：

将下列脚本保存为nomon_temp.sql，然后在sqlplus里@nomon_temp.sql，会生成nomonitoring.sql,然后@nomonitoring.sql即可对索引进行监控。

set line 1000 pagesize 0 feedback off echo off trimout on trimspool on termout off timing off
spool nomonitoring.sql
select 'alter index '||owner||'."'||index_name||'" nomonitoring usage;'
from sys.ALL_OBJECT_USAGE WHERE end_monitoring IS NULL;
spool off

    

注意执行过程中可能报ORA-00054错误，需要多次查询下列语句：

select count(*) from sys.ALL_OBJECT_USAGE WHERE end_monitoring IS NULL;

   

直到查询结果为0说明停止监控成功。

 

3.结果分析和处理

根据下列语句查询未被使用的索引及其大小，注意输入监控开始和结束时间,比如2020-2-10日开始监控的，2020-2-20日结束，可以输入20200210 00:00:00,20200220 00:00:00

SELECT /*+rule*/
 A.OWNER, A.TABLE_NAME, A.INDEX_NAME, SUM(B.BYTES) / 1024 / 1024 MB
  FROM SYS.ALL_OBJECT_USAGE A, DBA_SEGMENTS B
 WHERE A.OWNER = B.OWNER
   AND A.INDEX_NAME = B.SEGMENT_NAME
   AND A.USED = 'NO'
   AND to_date(a.start_monitoring,'mm/dd/yyyy hh24:mi:ss')>=to_date('&d1','yyyymmdd hh24:mi:ss')
   AND to_date(a.end_monitoring,'mm/dd/yyyy hh24:mi:ss')<=to_date('&d2','yyyymmdd hh24:mi:ss')
 GROUP BY A.OWNER, A.TABLE_NAME, A.INDEX_NAME
 ORDER BY  A.OWNER, A.TABLE_NAME, A.INDEX_NAME DESC;

 

对查询的结果提交给业务侧进行分析确认，哪些索引可以DROP掉，从而精简数据库的容量。