sql執行計畫錯誤之cache buffers chain

分享個小案例：

今天某個庫出現了cache buffers chain，最近應用沒啥變更，怎麼會突然出現呢，當然latch:cache buffers chain的作用是db cache中Find data很重要的latch，不管邏輯讀，物理讀(也要經曆邏輯讀),如果link或者unlink一個buffer到不同的Hash Bucket，再或者pin，unpin一個buffer，都要獲得相關bucket上相關的cache buffers chain latch。所以，關聯到sql上，正如我們通常說的，調sql的一個目標就是減少資源的消耗，包括降低邏輯讀，如果某個sql的讀的塊很多，那麼和其它在訪問相同資料的session就會爭奪cache buffers chain latch(因為決定buffer被串連到那個bucket裡面是由block的資訊決定的,一個cache buffers chain latch會保護多個bucket，如果很多訪問一個bucket裡面的buffer，此時就會導致次latch的爭用，也就是我們說的熱塊)所以，應用最近沒啥變更，可以肯定是某些sql走錯了執行計畫。

我們收集統計資訊是按照segment_size大於150M，並且每天的變化量超過20%的對象才會收集統計資訊。所以對於有些對象沒達到這個

收集的條件，統計資訊可能是很久以前的或者是缺失,資料變化較大的時候，可能導致執行計畫錯誤。

查看下當時ASH資訊：

2. select sql_id,count(*)  
4. from dba_hist_active_sess_history  
5. where event='latch: cache buffers chains' and sample_time between  
6. to_date('2012-08-02:16:00:00',‘YYYY-MM-DD:HH24:MI:SS') and to_date('2012-08-02:16:10:00',‘YYYY-MM-DD:HH24:MI:SS')  
7. group by sql_id  
8. order by 2 desc;  
9.   
10.   SQL_ID    COUNT(*)  
11. 0a3zj3m5h72rb   6098  
12. 3xyq2hqdd3akj   24  
13. 9rt5b6s9ry50q   16  

很明顯，sql_id:0a3zj3m5h72rb嫌疑比較大,看看執行計畫：

當前的執行計畫： 【幫客之家 http://www.bkjia.com 】

1. SQL_ID 0a3zj3m5h72rb  
2. --------------------  
3. select M.LOG_ID,M.STATE,M.SESSION_ID,M.IP,M.LOGOUT_DATE,M.LOGIN_DATE,M.STAFF_COD  
4. MAC ,M.ROWID as MROWID___  from SEC_LOGIN_LOG_201208 M where M.SESSION_ID = :1  
5. order by  M.LOG_ID DESC  
6. Plan hash value: 177413507  
7. --------------------------------------------------------------------------------  
8. | Id  | Operation                   | Name                    | Rows  | Bytes |  
9. --------------------------------------------------------------------------------  
10. |   0 | SELECT STATEMENT            |                         |       |       |  
11. |   1 |  TABLE ACCESS BY INDEX ROWID| SEC_LOGIN_LOG_201208    |     1 |   146 |  
12. |   2 |   INDEX FULL SCAN DESCENDING| PK_SEC_LOGIN_LOG_201208 |     1 |       |  
13. --------------------------------------------------------------------------------  

當然這個sql很簡單，執行計畫也很簡單，我們先看下這個表上索引的資訊：

1. INDEX_NAME                      BLEVEL COLUMN_NAME     COL_POS DISTINCT_KEYS   NUM_ROWS     FACTOR PAR LAST_ANALYZE  
2. -------------------------   ---------- ----------------------- ------------- ---------- ---------- --- ------------  
3. IDX_SEC_LOGIN_LOG2_201208       2      LOGOUT_DATE           1        106719     393830     376028 NO  01-AUG-12  
4. IDX_SEC_LOGIN_LOG_201208_3      2      LOGIN_DATE            1        123557     496287     419906 NO  01-AUG-12  
5. IDX_SEC_LOGIN_LOG1_201208       2      SESSION_ID            1        384049     384049     383848 NO  01-AUG-12  
6. PK_SEC_LOGIN_LOG_201208         2      LOG_ID                1        496292     496292     451386 NO  01-AUG-12  

1 2 下一頁