在數據庫管理與維護中，我們總會遇到一個問題：我們創建的索引是否會被某些SQL語句使用呢？換個通俗表達方式：我創建的索引是否是未使用的索引（unused Indexes），是否有價值呢？如果創建的某個索引是Unused Indexes，尤其是沒有合理規劃索引的系統或那些管理控制不規范的系統。有可能建立了N個索引，其實有些索引都是沒有任何SQL會使用，那麼此時這些多余的索引其實會帶來兩個問題：1：浪費存儲空間，尤其是大表的索引，浪費的存儲空間尤其可觀； 2：加重DML操作（UPDATE、INSERT、DELETE）的開銷。
ORACLE其實提供了監控索引使用情況的功能。ALTER INDEX <index_name> MONITORING USAGE; 我們下面來測試驗證一下吧。
創建一個表TEST作為實驗測試驗證的樣例
CREATE TABLE TEST
(
ID NUMBER(10),
NAME VARCHAR2(32)
);
CREATE INDEX IDX_TEST_ID ON TEST(ID);
INSERT INTO TEST
SELECT 1001, 'Kerry' FROM DUAL UNION ALL
SELECT 1002, 'Ken' FROM DUAL UNION ALL
SELECT 1003, 'Jimmy' FROM DUAL UNION ALL
SELECT 1004, 'Jack' FROM DUAL;
COMMIT;
execute dbms_stats.gather_table_stats(ownname => 'ETL', tabname =>'TEST', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
啟用對索引IDX_TEST_ID的監控
ALTER INDEX IDX_TEST_ID MONITORING USAGE;
此時觀察V$OBJECT_USAGE表數據的變化，如下所示，MONITORIN字段值變為YES，表示索引IDX_TEST_ID已經被置於監控狀態。USED字段為NO表示暫時沒有SQL使用該索引
SQL> COL INDEX_NAME FOR A20
SQL> COL TABLE_NAME FOR A10
SQL> COL MONITORING FOR A10
SQL> COL USED FOR A10
SQL> COL START_MONITORING FOR A20
SQL> COL END_MONITORING FOR A20
SQL> SELECT * FROM V$OBJECT_USAGE;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---------- ---- -------------------- ----------------
IDX_TEST_ID TEST YES NO 11/28/2015 14:57:41
此時我們執行下面SQL，因為此時使用全表掃描，那麼索引IDX_TEST_ID依然沒有被使用，此時可以查看V$OBJECT_USAGE進行驗證。
SQL> SET AUTOTRACE ON;
SQL> SELECT * FROM TEST WHERE ID =1001;
ID NAME
---------- --------------------------------
1001 Kerry
Execution Plan
----------------------------------------------------------
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 2 |
| 1 | TABLE ACCESS FULL| TEST | 1 | 9 | 2 |
----------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
578 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
如下所示，此時索引IDX_TEST_ID依然沒有被使用。
我們使用索引提示強制下面SQL使用索引IDX_TEST_ID
SELECT /*+ INDEX(TEST IDX_TEST_ID) */* FROM TEST WHERE ID =1001;
此時你就會發現USED的值變為了YES了。
ALTER INDEX IDX_TEST_ID NOMONITORING USAGE;
執行上面命令後，在V$OBJECT_USAGE表中，就會更新表TEST記錄的END_MONITORING、MONITORING的值。
如果你又啟用監控索引使用情況，那麼系統會更新START_MONITORING、END_MONITORING字段的值（END_MONITORING的值更新為NULL）。如果刪除表
TEST，此時你會發現V$OBJECT_USAGE對象中關於表TEST的記錄也不見了。
注意：SELECT * FROM V$OBJECT_USAGE; 只能查看當前用戶下被監控的索引信息。即使sys、system用戶也不能查看其它用戶的信息。
在測試過程中有個小疑問，就是在准備測試環境時，如果不對表收集統計信息的話，那麼即使SQL走全表掃描，你依然發現V$OBJECT_USAGE中索引被標記使用了。如下所示
DROP TABLE TEST PURGE;
CREATE TABLE TEST
(
ID NUMBER(10),
NAME VARCHAR2(32)
);
CREATE INDEX IDX_TEST_ID ON TEST(ID);
INSERT INTO TEST
SELECT 1001, 'Kerry' FROM DUAL UNION ALL
SELECT 1002, 'Ken' FROM DUAL UNION ALL
SELECT 1003, 'Jimmy' FROM DUAL UNION ALL
SELECT 1004, 'Jack' FROM DUAL;
COMMIT;
ALTER INDEX IDX_TEST_ID MONITORING USAGE;
SQL> SET AUTOTRACE ON;
SQL> SELECT * FROM TEST WHERE ID =1001;
ID NAME
---------- --------------------------------
1001 Kerry
Execution Plan
----------------------------------------------------------
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 2 |
| 1 | TABLE ACCESS FULL| TEST | 1 | 31 | 2 |
----------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
578 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
SQL> SELECT * FROM V$OBJECT_USAGE;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---------- ------ ----------- -------------------
IDX_TEST_ID TEST YES YES 11/28/2015 15:11:46
那麼為什麼呢？ 猜測是在解析生成執行計劃時，用到了索引的一些信息，導致V$OBJECT_USAGE表中的字段USED被標記為YES。
如果我們想在系統中監控所有的索引，那麼我們可以通過下面腳本實現監控數據庫所有的索引。注意我們要排除一些系統表的索引、以及LOB indexes。原因有下面兩個：
1：LOB indexes不能修改，否則會報ORA-22864錯誤（ORA-22864: cannot ALTER or DROP LOB indexes）。
2：ORA-00701: object necessary for warmstarting database cannot be altered
ORA-00701: object necessary for warmstarting database cannot be altered
00701. 00000 - "object necessary for warmstarting database cannot be altered"
*Cause: Attempt to alter or drop a database object (table, cluster, or
index) which are needed for warmstarting the database.
*Action: None.
SET PAGES 999;
SET HEADING OFF;
SPOOL run_monitor.sql
SELECT
'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' MONITORING USAGE;'
FROM
DBA_INDEXES
WHERE
INDEX_TYPE != 'LOB' AND OWNER NOT IN ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN');
SPOOL OFF;
@run_monitor.sql
此時使用下面腳本就能查出那些索引是未使用索引，當然監控索引時長非常重要，太短的話有可能導致查詢出來的數據有問題，一般建議監控一周後即可，OLAP系統則需要適當延長監控的時間。
SELECT I.TABLE_OWNER,
T.TABLE_NAME,
I.INDEX_NAME,
U.USED,
U.START_MONITORING,
U.END_MONITORING
FROM USER_TABLES T
INNER JOIN USER_INDEXES I
ON T.TABLE_NAME = I.TABLE_NAME
INNER JOIN V$OBJECT_USAGE U
ON U.TABLE_NAME = I.TABLE_NAME
AND I.INDEX_NAME = U.INDEX_NAME
WHERE I.TABLE_OWNER=SYS_CONTEXT('USERENV','CURRENT_USER')
另外，博客Oracle - Find unused Indexes中介紹了一個查找沒有使用索引的SQL語句。如下所示statspack_unused_indexes.sql
col owner heading "Index Owner" format a30
col index_name heading "Index Name" format a30
set linesize 95 trimspool on pagesize 80
select *
from
(select
owner,
index_name
from
dba_indexes di
where
di.index_type != 'LOB'
and
owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')minus
select
index_owner owner,
index_name
from
dba_constraints dc
where
index_owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')minus
select
p.object_owner owner,
p.object_name index_name
from
stats$snapshot sn,
stats$sql_plan p,
stats$sql_summary st,
stats$sql_plan_usage spu
where
st.sql_id = spu.sql_id
and
spu.plan_hash_value = p.plan_hash_value
and
st.hash_value = p.plan_hash_value
and
sn.snap_id = st.snap_id
and
sn.dbid = st.dbid
and
sn.instance_number = st.instance_number
and
sn.snap_id = spu.snap_id
and
sn.dbid = spu.snap_id
and
sn.instance_number = spu.instance_number
and
sn.snap_id between &begin_snap and &end_snap
and
p.object_type = 'INDEX'
)
where owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')order by 1, 2
/
這裡是另一個腳本用來跟蹤未使用的索引並展示給所有索引的調用計數。最重要的是，這個腳本顯示了多列索引引用的列（這個腳本執行時間較長，資源開銷較大。）
col c1 heading 'Begin|Interval|time' format a20
col c2 heading 'Search Columns' format 999
col c3 heading 'Invocation|Count' format 99,999,999
break on c1 skip 2
accept idxname char prompt 'Enter Index Name: '
ttitle 'Invocation Counts for index|&idxname'
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
p.search_columns c2,
count(*) c3
from
dba_hist_snapshot sn,
dba_hist_sql_plan p,
dba_hist_sqlstat st
where
st.sql_id = p.sql_id
and
sn.snap_id = st.snap_id
and
p.object_name = '&idxname'
group by
begin_interval_time,search_columns;
參考資料：
http://www.dba-oracle.com/oracle_tips_unused_indexes.htm
