如何監控Oracle索引的使用方式

一個系統，經過長期的運行、維護和版本更新後，可能會產生大量的索引，甚至索引所佔空間遠遠大於資料所佔的空間。很多索引，在初期設計時，對於系統來說是有用的。但是，經過系統的升級、資料表結構的調整、應用的改變，很多索引逐漸不被使用，成為了垃圾索引。這些索引佔據了大量資料空間，增加了系統的維護量，甚至會降低系統效能。因此，DBA應該根據系統的變化，找出垃圾索引，為系統減肥。

Oracle 9i後，可以通過設定對索引進行監控，來監視索引在系統中是否被使用到。文法如下：

alter index <INDEX_NAME> monitoring usage;

如果需要取消監控，可以使用以下語句：

alter index <INDEX_NAME> nomonitoring usage;

設定監控後，就可以查詢檢視v$object_usage來確認該索引是否被使用。

以下是一個DEMO示範：

SQL> select * from v$object_usage; INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING------------------------------ ------------------------------ ---------- ---- ------------------- ------------------- SQL> alter index QUEST_TEMPLATE_IDX monitoring usage; Index altered SQL> select count(*) from quest_template a  2  where minlevel >=38  3  and maxlevel <= 45;   COUNT(*)----------       165 SQL> select * from v$object_usage; INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING------------------------------ ------------------------------ ---------- ---- ------------------- -------------------QUEST_TEMPLATE_IDX             QUEST_TEMPLATE                 YES        YES  05/22/2007 14:02:51

但是，這個方法可能存在一個問題：對於一個複雜系統來說，索引的數量可能是龐大的，那麼我們如何來評鑑那些索引是值得懷疑的，應該被監控的呢？換句話說，我們如何減少監控範圍呢？這裡介紹幾個方法。

1、利用library cache資料

本欄目更多精彩內容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

在library cache中，儲存了系統中遊標的查詢計劃(並非全部，受library cache大小的限制)，通過視圖v$sql_plan，我們可以查詢到這些資料。利用這些資料，我們可以排除那些出現在查詢計劃中的索引：

select a.object_owner, a.object_namefrom v$sql_plan a, v$sqlarea bwhere a.sql_id = b.sql_idand a.object_type='INDEX'and b.last_load_time > <START_AUDIT_DATE>;

2、利用statspack表

Statspack建立以後，為了記錄快照的統計資料，會建立一系列的以stats$開頭的表。其中stats$sql_plan表記錄了每個快照中超過其閾值的語句的查詢計劃。因此我們可以將出現在該表中索引對象排除在監控範圍之外：

select a.object_owner, a.object_name

from stats$sql_plan a, stats$sql_plan_usage b

where a.plan_hash_value = b.plan_hash_value

and a.object_type='INDEX'

and b.last_active_time > <START_AUDIT_DATE>;

但是，這張表在預設情況下（snapshot level＝5）是不會記錄資料的，只有snapshot>=6才會有記錄。另外，該表在8i中是沒有的。

3、利用AWR資料

10g以後，oracle出現了比statspack更加強大的效能分析工具AWR，它也同樣記錄了系統中的統計資料以供分析。我們也同樣可以從其中分析出那些索引是被使用到的。

select b.object_owner, b.object_name

from dba_hist_snapshot a, dba_hist_sql_plan b, dba_hist_sqlstat c

where a.snap_id = c.snap_id

and b.sql_id=c.sql_id

and b.object_type = 'INDEX'

and a.startup_time > <START_AUDIT_DATE>;

利用上述方法，過濾掉大部分肯定被使用的index後，再綜合應用，選擇可疑索引進行監控，找出並刪除無用索引，為資料庫減肥。