oracle如何進行索引監控分析和最佳化

標籤：

在生產環境、我們會發現：

     ① 索引資料表空間 I/O 非常高
     ② "db file sequential read" 等待事件也比較高
   這種跡象表明、整個資料庫系統、索引的讀寫操作比較多、已經成為系統的主要瓶頸
   
   一般的原因、大抵如下：
   ① 大量SQL均採用索引
   ② DML操作導致索引維護工作量暴增
   ③ 頻繁DML導致很多索引片段、增加I/O開銷
   ④ 索引建立策略失誤、走索引如同全表掃
   
   如果、一張表欄位30個、但索引竟有 50個！？
   作為 DBA、你可能不瞭解商務邏輯、不敢刪、也不知該刪哪些、怎麼辦？
   
   建議綜合採用如下兩種策略：
   ⑴ 根據原理去判斷
      
      這種情況肯定存在很多複合索引！依據複合索引的首碼性和可選性兩大原理
      分析這 50 個索引的具體欄位的分布情況、自己做出合并、整合的判斷
      
   ⑵ 利用 Oracle 索引監控特性
      
      在典型業務周期開始之前、執行：
      [email protected]> alter index <索引名> monitoring usage；
      
      在典型業務周期結束之後、執行：
      [email protected]> alter index <索引名> nomonitoring usage；
      
      然後、查詢：
      [email protected]> select * from v$object_usage;
      
      這樣子、你就知道在這個典型周期之內、這個索引到底有木有用了
      即便是這樣了、可是、友情提示兩下、上面的結論不一定正確、因為：
      ① 10g在收集統計資訊時會導致索引被監控、這並非SQL語句產生、而在11g則不會出現這種情況了
      ② 外鍵索引不會因為主表的DML操作而被監控到、不要因為該索引沒用而將它給刪了
   
   不過、這裡、畢竟還會有個問題、對於一個複雜系統來說，索引的數量可能是龐大的
   那麼、我們該如何選擇嫌疑對象、減少監控範圍呢？
   以下介紹兩種方法：
   ① 利用 library cache 資料

 
      在library cache中，儲存了系統中遊標的查詢計劃(並非全部，受library cache大小的限制)

      通過視圖v$sql_plan，我們可以查詢到這些資料。利用這些資料，我們可以排除那些出現在查詢計劃中的索引：

 

 

[plain] view plaincopyprint? 

1. select a.object_owner, a.object_name  
2.   from v$sql_plan a, v$sqlarea b  
3.  where a.sql_id = b.sql_id and a.object_type=‘INDEX‘ and   
4.        b.last_load_time > <START_AUDIT_DATE>  

 

   ② 利用 AWR 資料
      
      10g以後，我們可以藉助 AWR 分析哪些索引被使用到了

 

 

[plain] view plaincopyprint? 

1. select b.object_owner, b.object_name  
2.   from dba_hist_snapshot a, dba_hist_sql_plan b, dba_hist_sqlstat c  
3.  where a.snap_id = c.snap_id and  
4.        b.sql_id=c.sql_id and   
5.        b.object_type = ‘INDEX‘ and   
6.        a.startup_time > <START_AUDIT_DATE>  

 

   利用上述方法，過濾掉大部分肯定被使用的index後，再綜合應用，選擇可疑索引進行監控，找出並刪除無用索引，為資料庫減肥
   
   頻繁對索引欄位進行 DML操作、會對索引造成大量片段、從而極大影響索引的使用效率、並造成索引I/O的增加
   那麼如何進行索引片段的分析和整理呢？
   
   執行如下語句可監測索引的片段情況：
   
   analyze index <索引名> validate structure online;
   
   select name,（del_lf_rows_len/lf_rows_len)*100 from index_stats;
   
   其中、索引片段率(%)=（del_lf_rows_len/lf_rows_len)*100
   如果索引片段率超過20%、則Oracle認為索引片段已經非常嚴重
   
   建議DBA編寫一個檢測所有索引片段率的指令碼、定期運行、保持對索引片段率的監測
   因為、進行索引片段分析和整理是DBA日常維護的工作之一
   
   Oracle 進行索引片段的處理包括兩種策略：
   ① 重建索引
      
      alter index <索引名> rebuild;
      
   ② 壓縮索引
      
      alter index <索引名> coalesce；
      
   不過、Rocky 建議大家採取定期索引重建的策略、例如可在每個周末或者每天夜晚對揮發表的索引進行線上重建

本文轉自：http://blog.csdn.net/dba_waterbin/article/details/8805010

oracle如何進行索引監控分析和最佳化