隨著Oracle對CBO的進一步增強和改進, 對表進行分析已經成為一種常用的調優的手段,
當發現某個表的相關SQL語句的執行計劃有問題時, 首先會想是不是統計信息過舊的問題, 如果的確是過舊, 則對這個表進行分析,
以讓Oracle重新選擇準確的最優執行計劃, 以達到調優的目標. 用不合適的方式對表進分析, 則會造成十分嚴重的后果,
如對一個用戶下的所有表進行分析, 或一下子分析多個表. 看到網友的
一篇貼子, 讓我想起二年多前幫別人處理過的一個案例, 有位DBA對某用戶下的所有表進行了分析.
基本情況是, IBM P570, 16CPU, 32GB內存, 24GB的SGA,
支持不了一個50G的9i數據庫(OLTP類型), 二百個以內的會話. 在分析之前CPU利用率是50-70%左右, 在分析后則一直是100%.
面臨這樣的情況后, 由于沒有做統計信息(Statistics)的備份, 因此無法恢復以前的情況, 首先做的是刪除所有非分區表的統計信息,
對分區表做更精確的統計信息分析, 使之運行于RULE方式, 情況稍有好轉, 但用戶還是不能接受, 相信Oracle的CBO沒有那么差,
因此性能問題的關鍵并不在于統計信息了. 在處理了以下幾個問題之后, 成功地降底了CPU的利用率.
1, 發現繁煩的并行進程, 發現一個幾有幾十MB大小的表, 它的并行度不為1, 因此導致了這個表上的一個執行比較繁煩SQL采用了并行處理, 將并行度改成1后, CPU利用率降低了20%, 基本上恢復到了分析前的水準.
2, 發現一個后臺發票打印程序的SQL有太多的邏輯讀, 對發票打印模式進行分析后,
建議將執行繁率從每10秒執行一次降底到1分鐘執行一次, 因為從售貨臺到打印發票的地方取發票需要走1分鐘左右. 再檢查其SQL,
發現其中的日期條件默認是兩年, 也就是要查最近兩年的銷售記錄, 再找出沒有打印過的記錄進行處理,
通過詢問銷售人員有沒有人會來要求打印兩周以前的發票, 當然是很少的了, 后來將這個默認的日期條件改成了最近一個月. 做了這個改動后,
系統的CPU利用率下降到了50%.
3, 接下來處理了一個庫存查詢有關的SQL, 對這個SQL本身沒有作任何修改,
因為用戶可以自定義條件(動態構造WHERE條件)進行查詢, 發現很多用戶只選了最少的條件進行過濾, 導致了這個SQL運行效率極低,
解決辦法就是培訓前臺用戶, 讓他們使用這個功能時, 盡可能地提供比較準確的查詢條件. 這樣一來, CPU利用率下降到了35%左右.
4, 調整了一些SQL后, 新的SQL又出現了, 這一次的問題是, 看這個SQL的執行計劃,
居然用了INDEX合并(Combine), 在where條件中用到了兩個列, 開發人員在這兩個列上分別建了索引, 但從單個列的角度來看,
效率不高, 但一組合效率則很高, 因此用復合索引解決此事. CPU利用率再次小降5%.
5, 后面又調了幾個SQL, 這次是創建了幾個新的索引, CPU的利用率已經下降到了20-25%, 目標完成.
總地來說, 分析表從來都不是優先考慮的調優手段, 從個人角度來看, 只有發現Oracle在某個表上選擇了錯誤的執行計劃后, 才會對單個表進行分析(分析之前先做備份, 除非很確定), 然后觀察, 再分析下一個表.
原文地址：
http://www.anysql.net/dba/after_analyze_whole_schema.html