隨著時間的累積，在沒有很好的規劃的情況下，資料庫中也許會存在大量長期不被使用的索引，如果快速的定位這些索引以便清理便擺在案頭。我們可以使用“alter index ××× monitoring usage;”命令將索引至於監控狀態下，經過一定的監控週期，那些不被使用到的索引便會在具體Schema下的v$object_usage檢視中得以體現。展示一下這個過程，供參考。
友情提示：生產資料庫中的索引新增和刪除一定要慎重，需要做好充分的測試。

1.環境準備
1）建立表T
sec@ora10g> create table t (x int);

Table created.

2）初始化一條資料
sec@ora10g> insert into t values (1);

1 row created.

sec@ora10g> select * from t;

         X
----------
         1

3）在表T的X欄位上建立索引
sec@ora10g> create index i_t on t(x);

Index created.

2.將索引I_T置於監控狀態下
sec@ora10g> alter index I_T monitoring usage;

Index altered.

3.檢視v$object_usage檢視中記錄的資訊
sec@ora10g> col INDEX_NAME for a10
sec@ora10g> col TABLE_NAME a10
sec@ora10g> col START_MONITORING for a20
sec@ora10g> col END_MONITORING for a20
sec@ora10g> select * from v$object_usage;

INDEX_NAME TABLE_NAME MONITORING USED      START_MONITORING     END_MONITORING
---------- ---------- ---------- --------- -------------------- -----------------
I_T        T          YES        NO        07/17/2010 22:27:13

此時MONITORING欄位內容為“YES”，表示I_T已經處於被監控狀態。USED欄位內容為“NO”表示該索引還未被使用過。

4.模擬索引被使用
sec@ora10g> set autot on
sec@ora10g> select * from t where x = 1;

         X
----------
         1

Execution Plan
----------------------------------------------------------
Plan hash value: 2616361825

-------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    13 |     1   (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| I_T |     1 |    13 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("X"=1)

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          1 consistent gets
          0 physical reads
          0 redo size
        508 bytes sent via SQL*Net to client
        492 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

從執行計劃上可以看出，該查詢使用到了索引I_T。

5.再次檢視v$object_usage檢視中記錄的資訊
sec@ora10g> set autot off
sec@ora10g> select * from v$object_usage;

INDEX_NAME TABLE_NAME MONITORIN USED      START_MONITORING     END_MONITORING
---------- ---------- --------- --------- -------------------- -----------------
I_T        T          YES       YES       07/17/2010 22:27:13

此時USED欄位內容變為“YES”，表示I_T索引在監控的這段時間內被使用過。
如果在一個較科學的監控週期下USED欄位一直處於“NO”的狀態，則可以考慮將此類索引刪掉。

6.停止對索引的監控，觀察v$object_usage狀態變化
sec@ora10g> alter index I_T nomonitoring usage;

Index altered.

sec@ora10g> select * from v$object_usage;

INDEX_NAME TABLE_NAME MONITORIN USED      START_MONITORING     END_MONITORING
---------- ---------- --------- --------- -------------------- -------------------
I_T        T          NO        YES       07/17/2010 22:27:13 07/17/2010 22:32:27

此時MONITORIN欄位內容為“NO”，表示已經停止對索引I_T的監控。

7.再次啟用索引監控，觀察v$object_usage狀態變化
sec@ora10g> alter index I_T monitoring usage;

Index altered.

sec@ora10g> select * from v$object_usage;

INDEX_NAME TABLE_NAME MONITORIN USED      START_MONITORING     END_MONITORING
---------- ---------- --------- --------- -------------------- ------------------
I_T        T          YES       NO        07/17/2010 22:36:40

MONITORIN欄位內容為“YES”，表示索引I_T處於被監控中；USED欄位為“NO”，表示再次啟用監控後的這段時間內該索引沒有被使用過。
停起對索引的監控的過程相當於索引監控重置的過程。

8.一次性生成當前使用者下所有索引的監控語句
可以使用SQL生成SQL指令碼的方法來完成。
以對SECOOLER使用者下所有索引生成監控語句為例
sys@ora10g> select 'alter index '||owner||'.'||index_name||' monitoring usage;' as "Monitor Indices Script" from dba_indexes where owner in ('SECOOLER');

Monitor Indices Script
---------------------------------------------------------------
alter index SECOOLER.I_T monitoring usage;
…… 省略 ……

如果您對PL/SQL熟悉的話，可以更方便的完成批次將索引置為被監控狀態。
sys@ora10g> conn secooler/secooler
secooler@ora10g> begin
2 for rec in (select index_name from user_indexes)
3    LOOP
4        dbms_output.put_line(rec.index_name);
5        EXECUTE IMMEDIATE 'alter index '||rec.index_name||' monitoring usage';
6    end loop;
7 end;
8 /

I_T
…… 省略其他索引名字 ……

PL/SQL procedure successfully completed.

9.小結
一般生產資料庫很少使用這種方法（前提是做好規劃），多見於測試資料庫。測試資料庫中出於對各種索引組合的測試需求，可能建立眾多的索引，使用這種方法可以比較便捷的確認那些不被用到的索引。

Good luck.

secooler
10.07.17

-- The End --

【INDEX】使用“alter index ××× monitoring usage;”語句監控索引使用與否

相關文章