環境描述：rac雙節點主庫加一個單節點的物理備庫（作查詢庫使用），白天開啟到只讀模式，晚上應用2小時的日誌並再開啟到只讀模式。

問題描述：開發人員反應點選前端應用查詢某個單子時半天不出結果，詢問開發人員獲取該模組對應的sql語句後在主庫上執行結果10秒內返回結果，但是在查詢庫上卻要20分鐘之久，接下來進行問題診斷。

診斷過程：

1.分別在主備庫上執行sql並獲取其執行計劃，此時發現兩個執行計劃確實不同，主要表現在同名表卻是用不同的索引

2.初步懷疑是表的資料量或統計資訊等因素導致執行計劃的不同，透過比對兩個庫的資料字典資訊後發現不是這個原因

3.按照主庫的執行計劃透過新增hint(/*+ index(t idx_t) */)然後再備庫上執行此sql，當修改到第二個表時效能突然一下子提高了，可以在10秒內返回結果。原因為好的執行計劃使用的是選擇性高的索引，而壞的則走的選擇性低的索引。到目前為止還沒有找到問題的根本原因，為什麼cbo會在備庫上選擇一個選擇性低的索引？

4.透過google發現對於主備庫上執行相同sql卻產生不同執行計劃很有可能是初始化引數不同引起的，於是在主備庫上透過show parameter optimizer命令發現主備庫上的optimizer_index_caching設定的值不同，主庫為0（預設值），備庫為100，於是在備庫上修改此引數後再次執行原始sql(沒新增hint)發現效能問題解決，此sql可以在10秒內返回結果

5.查詢官方文件關於optimizer_index_caching引數的解釋發現此引數設定的值越大，cbo越傾向於選擇in-list的索引和nl，再次比對執行計劃發現確實都選擇的是nl操作且備庫上還多了一個INLIST ITERATOR的操作，至此根本原因也知道了就是因為optimizer_index_caching引數值設定過大導致導致傾向於選擇in-list的索引而最終導致執行計劃選擇錯誤

6.至此也看出了基本概念的理解對於調優也是很有幫助的，如果對optimizer_index_caching引數瞭解夠多可能會更早定位這個問題。

記錄一個sql最佳化的全過程.txt

相關文章