管好統計資訊，開啟SQL優化之門

轉載宣告： DBA+社群（dbaplus）。http://dbaplus.cn/news-10-108-1.html

在DBA的工作中，SQL優化的工作量佔工作很大的一塊，我們在平時工作中也是這樣，常常遇到一些執行效率低下的SQL語句，而這些執行效率低下的SQL，有的是業務系統剛剛上線的，有的是已經執行很久但因為執行環境變化而導致出現的。這裡給大家分享一個SQL的優化案例分析。

根據我們監控系統，發現線上OLTP的一個核心業務資料庫中有一條SQL執行效率較慢。慢到什麼程度？半小時執行278次，平均每次28秒,佔用整個DB資源的56%。OLTP系統中，實在太慢了。

這裡是SQL執行的相關資訊。

SQL優化中，有很多人總是第一要看的就是執行計劃，那麼我們就看看這條SQL的執行計劃。下面可以看到單次執行3秒左右，成本為2，consistent gets較高，執行計劃中也是走INDEX RANGE SCAN。

其實單看上面的執行計劃Cost還是比較低的，SQL優化中，有很多人總是第一要看的就是執行計劃，但是看執行計劃一定要結合結構資訊，這裡的結構資訊就是表，索引等結構資訊及資料分佈資訊。

我們先看SQL語句吧。以下SQL語句很簡單，且在cn欄位，c_date欄位上都建有索引。

表資料量約有3.6億資料。

在以上執行計劃的基礎上，根據對業務的理解，我的疑問是為什麼不走cn索引？

這裡其實可以根據謂詞條件，各自查詢 一下就能看到結果，根據cn查詢到3條，而c_date條件查詢出76w。走cn索引才對。

這裡也可以使用HINT強制走cn索引看一下效果，使用HINT強制走cn索引後執行時間變為毫秒級。

SQL優化除了瞭解結構資訊（表，索引），統計資訊的準確性也很關鍵。

這裡發現最後統計資訊分析時間是5月份，相差了3個多月，所以統計資訊是不正確的。

統計資訊不準確的原因？

最終發現Oracle在10g版本中預設的GATHER_STATS_JOB沒有啟動，這裡啟動預設的GATHER_STATS_JOB，並單獨收集一下表的統計資訊。

收集完統計資訊，這條SQL的執行時間下降到毫秒級別，執行計劃已經變為IDX_REC_LOG_CN索引的RANGE SACN，consistent gets從原來的19409  降低到了7。效果還是很明顯。

同樣的SQL又慢了，現在的執行計劃， 又開始走IDX_C_LOG_DATE索引了，而且執行時間又回到了2秒， consistent gets變為10404。以下為執行計劃：

同樣我們還是先檢查統計資訊是否正確，這裡可以看到了統計資訊又不正確了，但是我們發現GATHER_STATS_JOB每天都能執行成功。這是為什麼？

解決方法就是定義一個單表收集的JOB。

這也是為什麼大表都單獨定義收集統計資訊的原因,面試過很多的同學，基本上說出直接原因的沒有多少，都說是照著網上這麼做的。

這裡也可以看到相關的10053事件中的成本資訊，具體可以參考以下的地址瞭解每個型別的含義。

最終我們在業務維護時間建立了cn+c_date聯合索引後的執行計劃，至今再無類似的SQL效能問題。

DBA+社群是全中國最大的涵蓋各種資料庫、中介軟體及架構師線條的微信社群！有100+專家發起人，建有15大城市微信群，6大專業產品群，多達10000+跨界DBA加入隊伍。每天1個熱議話題，每週2次線上技術分享，不定期線下聚會與原創專家團乾貨分享，更多精彩，歡迎關注dbaplus微信訂閱號！

掃碼關注

DBAplus社群

超越DBA圈子，連線的不僅僅是DBA

轉載宣告： DBA+社群（dbaplus）。http://dbaplus.cn/news-10-108-1.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1848005/，如需轉載，請註明出處，否則將追究法律責任。