開發人員提出：需要對之前的索引監控 然後找出沒用的索引 找時間刪除 從而減少未用索引佔用空間 提高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表示監控期間 對應的索引被使用

基於上面的監控結果 可以進行統計分析 從而評估索引有沒有被使用