oracle索引的監控

最近的研究發現 Oracle 資料庫所使用的索引從來沒有達到過可用索引數的1/4，

或者其用法與其開始設計的意圖不相同。未用的索引浪費空間，而且還會降低 DML

的速度，尤其是 UPDATE 和 INSERT 語句;控資料庫索引的使用，釋放那些未被使用

的索引，從而節省維護索引的開銷，最佳化sql效能

在 Oracle9i 之前，監控索引使用的唯一方法是執行他們的程式庫緩中的所有SQL，

然後手工記下所有被使用的索引。

oracle9i中如何確定索引的使用方式

　在oracle9i中，情況會簡單的多，因為有一個新的字典視圖V$SQL_PLAN儲存了實際計劃，

  這些計劃用於執行共用SQL區中得語句。V$SQL_PLAN視圖很類似與計劃表，但V$SQL_PLAN
 
  使用ADDRESS和HASH_VALUE列 來識別語句， 而計劃表使用使用者提供得STATEMENT_ID來識
 
  別語句。下面的SQL顯示了在一個oracle9i資料庫中，由出現在共用SQL區中語句使用的所
 
  有索引

　select object_owner, object_name, options, count(*)
　　from　 v$sql_plan
  　where　operation='INDEX'
　　and　　object_owner!='SYS'
　　group　by　object_owner, object_name, operation, options
　　order　by count(*) desc;

　　
　　所有基於共用SQL區中的資訊來識別索引使用方式的方法， 都可能會收集到不完整的資訊。共用SQL區是
   
    一個動態結構，除非能對它進行足夠頻繁的採樣， 否則在有關索引使用的情況的資訊被收集之前，SQL語
   
    句可能就已經(因為老化)被移出緩衝了。

    Oracle9i提供一個簡單的方法來開啟和關閉索引使用跟蹤，那就是MONITORING USAGE 子句：

　　alter index cust_name_idx monitoring usage;
　　alter index cust_name_idx nomonitoring usage;

　　這個命令使用V$OBJECT_USAGE 視圖和 USAGE 欄位來判斷索引是否被訪問過。你可能期望 USAGE 欄位是一個數字值，

    這樣你就可以知道索引被使用的次數，但不幸的是，它的取值只為YES 或NO。但不管怎樣，如果你接手一個以前開發
   
    的資料庫，而且老資料庫在沒有考慮到 SQL 訪問表的情況下建立了索引，那麼這個工具對你是很有用的。INDEX MONITORING
   
    特性的開銷非常小，而對定位和丟棄不需要的索引很有協助

開啟整個方案的索引監控的簡單 SQL*Plus 指令碼

    set pages 999
　　set heading off
　　spool run_mon.sql
　　select
　　　 'alter index '||
　　　 index_name||
　　　 ' monitoring usage;'
　　from
　　　 dba_indexes
　　where
　　　 owner = 'HPO5';
　　spool off

　sql>　@run_mon

oracle索引監控周期的選擇，一般要覆蓋對資料庫所有的操作，避免漏掉

------end-----

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/wyzxg/archive/2009/08/21/4471038.aspx