开发人员提出：需要对之前的索引监控 然后找出没用的索引 找时间删除 从而减少未用索引占用空间 提高DML效率
此文只是 监控索引 对监控结果 需要进行统计分析 然后可以考虑先unusable 索引 观察一段时间 再drop index

索引监控Procedure

创建日志表

create table tt (tname varchar2(32),vsql varchar2(500),vdate date);

创建监控信息结果表

create table TMP_OBJECT_USAGE  as

select trunc(sysdate) vdate,t.*from V$OBJECT_USAGE t where 0=1;

创建存储过程

create or replace procedure P_IDX_MONITORING (V_SCHEMA varchar2,IS_ON number)

as

    -- Create by yujx 2014/12/25

    /* V_SCHEMA 需要监控索引的schema

        IS_ON         1：开启索引监控   2：关闭索引监控

    -- LOG表

    drop table tt purge;

    create table tt (tname varchar2(32),vsql varchar2(500),vdate date);

    -- 索引监控结果表

    create table TMP_OBJECT_USAGE  as

    select trunc(sysdate) vdate,t.*from V$OBJECT_USAGE t where 0=1;

    */

  V_SQL varchar2(1000);

  v_error clob;

Begin

  delete tt;

  -- 监控索引

  IF IS_ON=1 THEN

  for i in (SELECT table_name,'ALTER INDEX ' || owner || '.' || index_name ||

                       ' MONITORING USAGE' vtext

                  FROM dba_indexes where index_type<>'LOB' and owner in (V_SCHEMA)) loop

    v_sql :=i.vtext;

    execute immediate v_sql;

   insert into tt values(i.table_name,v_sql,sysdate);

  End loop;

  END IF;

   /* 查看索引使用情况

  select * from V$OBJECT_USAGE;*/

  --关闭监控

  IF IS_ON=2 THEN

     for i in (SELECT table_name,

                       'ALTER INDEX ' || owner || '.' || index_name ||

                       ' NOMONITORING USAGE' vtext

                    FROM dba_indexes where index_type<>'LOB' and owner in (V_SCHEMA)) loop

      v_sql :=i.vtext;

      execute immediate v_sql;

      insert into tt values(i.table_name,v_sql,sysdate);

  End loop;

  -- 记录本次监控信息到结果表

  insert into TMP_OBJECT_USAGE select trunc(sysdate),t.* from V$OBJECT_USAGE t;

  END IF;

  commit;

EXCEPTION

  WHEN OTHERS THEN

     v_error := SQLERRM;

     .....此处根据实际情况处理异常（发邮件、插入异常日志表等）

end;

使用说明

P_IDX_MONITORING(‘需要监控的schema’,’1或者2’);

--1表示开启监控  2表示关闭监控

如

--对TEST开启索引监控功能

BEGIN

   P_IDX_MONITORING('TEST',1);

END;

--关闭索引监控功能

BEGIN

   P_IDX_MONITORING('TEST',2);

END;

日志查看

索引监控JOB

开启index监控

--每天晚上22点开启索引监控

variable job number;

begin

sys.dbms_job.submit(job => :job,

                      what => ' P_IDX_MONITORING(''TEST'',1);',

                      next_date => sysdate,

                      interval => 'TRUNC(sysdate) + 1 +22/24');

commit;

end;

/

关闭index监控

--每天早上8点关闭索引监控

variable job number;

begin

sys.dbms_job.submit(job => :job,

                      what => ' P_IDX_MONITORING(''TEST'',2);',

                      next_date => sysdate,

                      interval => 'TRUNC(sysdate) + 1 +8/24');

commit;

end;

/

查看监控结果

select * from TMP_OBJECT_USAGE;

--可以查询到每天监控时间段内的使用情况

--USED为yes表示监控期间 对应的索引被使用

基于上面的监控结果 可以进行统计分析 从而评估索引有没有被使用