今天做鍵上線，得空總結一下。
前兩天和同事一塊看一個夜維程序執行異常慢的問題，是一個比較典型的問題，同時也是一個比較頭疼的問題。
背景描述：
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. 要對問題的排查過程有一個清晰的認識，根據什麼信息，推斷可能是什麼原因，用什麼方法論證推論，根據現象找到根本原因，再根據若干解決方案作比對，選擇最優的方案。說起來容易做起來難，只能靠積累、思考，慢慢熟練起來。
