【DBAplus】深入Oracle優化器：一條詭異執行計劃的解決之道

1. 深入Oracle優化器：一條詭異執行計劃的解決之道

   DBAplus社群 | 2016-05-05 19:51

   CBO計算成本並選擇最佳執行計劃的至關重要輸入物就是表和索引的統計資訊，過舊或錯誤的統計資訊則可能導致一個效能極差的執行計劃被錯誤地選中。本文將以一個案例展示詭異的統計資訊如何影響執行計劃的生成。

   1案例介紹

   這是一個簡單的sql，近兩個月來對於告警明細表（分割槽）做月度彙總查詢時，總是出現了異常緩慢的情況。

   測試SQL：

   

   欄位NEALARM_TIME是固定條件，欄位RELATED_EMS_CUID是不固定的（這些不固定條件的選擇性都不強），分割槽裁剪到的分割槽有著1~3月份的資料。

   關於HISTORY_ALARM表， 存放20150301至今的資料，每天大約150w資料，有按1天1分割槽、1個月1分割槽，第41個分割槽比較特殊，這是一個有著2016年1月~3月份資料的分割槽；相信各位瞭解到這個sql的資料分割槽情況，第一聯絡到的訪問路徑就是分割槽全表掃描或訪問複合索引，畢竟訪問的資料佔據著1/3個分割槽的資料。

   下面是執行計劃：

   

   該表最新收集了表和索引的統計資訊，取樣比為auto，沒有收集直方圖，請看執行計劃可以注意到其中一些奇怪的細節：

   1.索引HIS_ALARM_INDEX1預估基數比父節點回表的基數還小，而且小很多；

   2.索引HIS_ALARM_INDEX1是一個複合索引(NEALARM_TIME,NEEND_TIME)，訪問的欄位只是日期（NEALARM_TIME）

   正常情況下，索引選擇率>=單表選擇率，通過rowid回表後filter所返回的行數要小於索引掃描返回的行數；而如果訪問索引只是單純靠日期（NEALARM_TIME）過濾資料，還要再回表，對於1/3分割槽資料多達1500w行，其成本代價是遠高於分割槽全表掃描的，這也難怪查詢如此緩慢。

   從執行計劃上可以看到問題入手點：即id 3的索引預估返回值遠小於id 4單表預估返回值，這是不合理的；再者即便要訪問索引，為什麼選擇了複合索引，而不是前導列同樣為NEALARM_TIME的單欄位索引？

   210053看問題

   為了弄清楚上一步分析後的疑問，我們收集10053 trace幫助解析CBO是如何根據統計資訊選擇執行計劃。

   1. 首先計算單表基數

   

   分割槽裁剪為Part#:40，統計資訊來自分割槽統計資訊

   單表選擇率，沒有直方圖：

   選擇率

   

   CBO計算得到的單表選擇率，我們可以逆推出來：

   

   以上證明單表選擇率是相對準確的。

   2.訪問路徑及成本計算

   

   

   

   從上述成本計算看到，CBO確實選擇了表面上成本更小的索引範圍掃描Index: HIS_ALARM_INDEX1，可看出了一些有用資訊：

   1.單欄位索引HISALARM_PART_IDX_0（NEALARM_TIME）在分割槽Part#:40選擇率1/3是正確的

   2.訪問本地分割槽索引HISALARM_PART_IDX_0（NEALARM_TIME）的成本確實比全表掃描高，13537358>2982164，最佳的訪問路徑應該是分割槽全表掃描

   3.理論上，訪問單欄位的本地分割槽索引HISALARM_PART_IDX_0的成本應小於多欄位的全域性索引 HIS_ALARM_INDEX1，而10053中與之相反

   4.訪問全域性索引HIS_ALARM_INDEX1（NEALARM_TIME,NEEND_TIME），從執行計劃"Predicate Information"中可以看到訪問欄位只有NEALARM_TIME，其選擇率出奇的低僅有0.000041

   HIS_ALARM_INDEX1索引選擇率ix_sel: 0.000041出奇的低，也導致了基數估算過小，COST僅有5562.83；一切表明著，全域性索引HIS_ALARM_INDEX1使用的全域性統計資訊很可能有問題：

   推算索引選擇率

   

   推算的索引選擇率和10053中顯示的索引選擇率想去甚遠，下面從檢視dba_tab_col_statistics檢查欄位NEALARM_TIME的最大最小值：

   

   全域性統計資訊的欄位NEALARM_TIME最小值居然是"91890122"，而不是真實的20150301，導致全域性統計資訊的最小值遠不同於真實日期（之後查明表中存在極其少量異常資料），這使得對NEALARM_TIME進行範圍匹配時選擇率偏低。上一步疑問已經找到原因：即便是sql語句寫法已經使用了分割槽裁剪（使用分割槽統計資訊），但CBO考慮訪問全域性索引是要根據全域性統計資訊進行成本計算的，而全域性統計資訊有誤才出現了執行計劃中一系列不合理的地方。

   3小結與解決方法

   CBO在對訪問路徑及其成本計算時會考慮所有可訪問的索引，而進行全域性索引訪問方式的成本計算時使用的是全域性統計資訊。案例中執行計劃的單表返回行數是根據分割槽統計資訊估算（855K 準確），而索引返回行數是根據全域性統計資訊估算（5964 遠遠偏小），這是因為全域性統計資訊的欄位最小值異常，導致進行範圍匹配時選擇率估算嚴重偏小，返回行數估算偏小，這也就是為什麼執行計劃中的索引返回行數遠小於單表返回行數。

   問題的根本原因是使用了錯誤的全域性統計資訊，解決方法就是糾正或不使用全域性統計資訊。

   解決方法：找出異常資料刪之，並重新收集統計資訊。

   workround：

   1.drop該全域性索引或重建為本地分割槽索引

   2.將該全域性索引設定為不可見"invisible" ，這樣就不會使用到全域性統計資訊 <<後面介紹這種方法

   3.對該欄位收集直方圖

   下面是將問題索引"XZ_ALARM.HIS_ALARM_INDEX1"設定為 invisible後的優化效果，可以看到最佳執行計劃已經不再考慮訪問索引，因為訪問本地分割槽索引HISALARM_PART_IDX_0（NEALARM_TIME）的成本比全表掃描高，13537358>2982164，在新的10053中是可以看到這一點的。

   

   作者介紹 王培中

   新炬網路資深DBA

   
   

   About Me

   
   

   ...............................................................................................................................................................................

   本文來自於微信公眾號轉載文章，若有侵權，請聯絡小麥苗及時刪除

   ITPUB BLOG：http://blog.itpub.net/26736162

   QQ：642808185 若加QQ請註明您所正在讀的文章標題

   【版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任】

   ...............................................................................................................................................................................

    

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