夜維執行慢的原因探究，執行探究

夜維執行慢的原因探究，執行探究

今天做鍵上線，得空總結一下。

前兩天和同事一塊看一個夜維程式執行異常慢的問題，是一個比較典型的問題，同時也是一個比較頭疼的問題。

背景描述：

1. 當天上線了一個夜維程式，邏輯很簡單，就是執行類似delete from table where rownum<=10000 and r_date>='2015-06-01' and r_date<='2015-06-02';，每次刪除10000條某天的到期資料，table表資料量大約2000萬，每天刪除量大約是幾十萬，r_date欄位類型是VARCHAR2，但含義是日期，實際這用的是字串比較，代替了日期的比較。(11g的庫)

2. 現象是程式開始執行後，大約15分鐘，都沒有結束，上線人員擔心有問題，就人工終止了改程式。

3. 臨時改了下程式，另外一個k_date欄位代替r_date，同樣的SQL，很快就執行完了。(從邏輯上，可用k_date代替r_date)

問題排查：

1. 從現象看，15分鐘，一個帶where條件的delete語句沒有執行完，換了一個欄位後，很快就執行完成，說明很有可能前者沒有使用或者用了錯誤的索引，導致執行時間超長，究竟是不是，需要證據來論證。

2. 首先通過user_indexes和user_ind_columns視圖可以得到索引及對應的欄位列資訊，這裡看到：

(1) 包含r_date有兩個索引

索引1：r_date, a, b, c，這裡是4個欄位組成的一個複合主鍵，預設包含一個複合索引，r_date是前置列。

索引2：orgid，r_date，複合索引，r_date是第二列。

(2) 包含k_date有一個索引

索引3：k_date的單索引值B*Tree索引。

3. 查看執行計畫(這裡用PLSQL Developer的F5鍵)

執行快的SQL：delete from table where rownum<=10000 and k_date>='2015-06-01' and k_date<='2015-06-02'，用的是“索引3”的INDEX RANGE SCAN。

執行慢的SQL：delete from table where rownum<=10000 and r_date>='2015-06-01' and r_date<='2015-06-02'，用的是“索引2”的INDEX SKIP SCAN。

單從這看就已經可以定位到原因了，執行快的SQL用了INDEX RANGE SCAN，從索引的選擇率(selectivity)看，算是比較高效(幾十萬/2000萬)。執行慢的用了INDEX SKIP SCAN，我們知道索引跳躍掃描(9i以上)的使用是有前提條件的，這種掃描方式是為了讓查詢條件不是複合索引前置列的情況下，依舊能使用複合索引，但不是任何時候都是高效的，只有當這個複合索引的前置列distinct值較小的前提下，使用這種掃描方式才會相對有效，因為他的檢索方式相當於在索引(B*Tree)中遍曆所有前置列值的二叉樹，再定位非前置列的條件欄位，因此如果前置列distinct值較大，那麼其實花費的成本也會很大，可能只能認為INDEX SKIP SCAN的效能略好於FULL TABLE SCAN，但依舊可能是非常低效。

4. 這裡實際還有個問題，運行DBA的同事從緩衝中幸運的找到了慢SQL的SQLID，查看他的執行計畫是“索引2”的INDEX FULL SCAN，雖然這樣的結果和3的結果有些出入，但都可以一定程度說明索引選擇的不正確是造成SQL慢的原因。

之所以SQLID找到的執行計畫和F5得到的執行計畫不同，根本原因是F5得到的執行計畫實際是封裝了EXPLAIN PLAN命令，其未真正執行這條SQL，而SQLID是真正執行的SQL在緩衝中的ID，因此是真正執行了的SQL，執行計畫是否準確，就看是不是真正執行了這條SQL，正如@dbsnake所說，EXPLAIN PLAN方式的執行計畫有可能不準，因為其未真正執行這條SQL。

5. 無論是3還是4對於慢SQL的執行計畫，SQL慢的原因基本確定是由於選擇了“索引2”的INDEX SKIP SCAN或INDEX FULL SCAN，沒有選擇r_date作為前置列的主鍵索引，但為什麼CBO這樣選擇的呢？

CBO是基於COST的最佳化，根據表的資訊等統計資訊綜合SQL各種執行路徑的成本，選擇出來的成本最低的一個執行路徑，作為SQL的執行計畫，可以用10053看到SQL各種執行計畫的成本計算。

運行DBA同事發現這張表從4月份，統計資訊就沒有被更新過，雖然Oracle每晚都有固定的夜維視窗，執行統計資訊的自動採集，但他的採集也是有條件的，當表的資料量變化未達到一定的條件時，是不會觸發自動採集。

因此統計資訊不準，可以作為一個懷疑的理由。

6. 針對這種問題，可能有三種解決方案：

(1) 就用開發人員使用的k_date代替r_date，因為已經驗證可以使用正確的k_date索引，前提是邏輯上相同就行，相當於從業務上對SQL進行了改寫，針對此情境可用，並不通用。

(2) 仍舊使用r_date，首先要確定“索引1”的成本肯定要低於“索引2”，可以使用等價的select r_date from table where rownm<=10000 and r_date>='2015-06-01' and r_date<='2015-06-02'來在生產環境中執行，通過SQLID尋找對應的執行計畫，以確定最優的執行計畫是什麼，如果確定是“索引1”，可以使用HINT強制SQL使用“索引1”，只是這張表的資料量並不會有一個顯著的變化，因此才可以將HINT作為一種方法，使用HINT的副作用，就是無論環境有何變化，都會使用HINT中明確的索引，一旦環境的變化導致最優執行計畫有變，那麼HINT就比較危險了，而且HINT是需要程式修改的，因此這種方法是下下策。

(3) 手工採集統計資訊，更新該表的統計資訊，以讓CBO可以使用正確的統計資訊選擇正確的執行計畫，這是根本解決之道，且不需要程式修改，當然最好提前看下應用程式中是否使用了HINT等，避免因更新統計資料，造成錯誤的影響。

7. 運行DBA同學手工收集了統計資訊，確認SQL使用“索引1”的INDEX RANGE SCAN，執行時間也恢複了正常，這個問題基本已解。

總結：

1. EXPLAIN PLAN得到的執行計畫有可能不準，執行計畫是否準確主要看是否真正執行了SQL語句。

2. 要明白INDEX SKIP SCAN的適用條件，不是什麼時候帶有INDEX的執行計畫都是最好的，需要看情境。

3. 如果長期未自動採集統計資訊，手工採集前，至少我認為應該確認下應用自身沒有加HINT等可能因統計資訊變更產生影響的情況。

4. 有時候使用商務邏輯的替換，也可以實現想要的目的，這要根據實際具體看了。

5. 之所以開頭說這是一個頭疼的問題，其實這問題是有一定代表性的，測試的時候沒發現，主要原因還是因為測試環境和生產環境的差異性，有些問題測試中怎麼都沒事，一到生產就有問題，對於這種效能問題，如何在上線前發現，有些時候是不容易的，是否我們可以引入生產的資料量、統計資訊？

6. 要對問題的排查過程有一個清晰的認識，根據什麼資訊，推斷可能是什麼原因，用什麼方法論證推論，根據現象找到根本原因，再根據若干解決方案作比對，選擇最優的方案。說起來容易做起來難，只能靠積累、思考，慢慢熟練起來。