瘋狂

環境：
spring jdbc 3.2.3.RELEASE
oracle 11.2.0.1.0 - Production

生產環境存在大表（3000萬數據）：WR_MP_HOURW_R，包含復合主鍵：

   mp_cp char(13),
   DT  date
對應索引名稱：WR_MP_HOURW_R_PRI。

問題:針對此表的更新很慢，每條update大概需要2秒。更新的時候使用的where 條件包含

mp_cp =? and DT =?

問題分析：
通過AWR 查找到針對此表的更新有大量的物理讀，因此判定更新時的執行計劃有問題。通過查找對應sql的執行計劃，發現沒有走索引。具體查找過程如下：
1）找到對應的sql_id：通過select t.SQL_TEXT,t.SQL_ID,t.ADDRESS,t.HASH_VALUE from v$sql  t where t.SQL_TEXT   like '%WR_MP_HOURW_R%';
2） 根據sql_id查找計劃：select * from table(dbms_xplan.display_cursor('sql_id',null,'advanced'));

果然發現sql沒有走索引，而是走的 table access full。正常應該走 INDEX UNIQUE SCAN。具體計劃如下：

 

首先考慮oracle是否沒有搜集表的統計信息。查看屬性發現表和索引的統計都較新（oracle 基本上一個小時會收集一次，以保證執行計劃是最優的）。

通過hint 處理強制走索引：/*+ INDEX(WR_MP_HOURW_R WR_MP_HOURW_R_PRI)*/  。發現走了INDEX SKIP SCAN。

此時說明復合索引有一個字段oracle認為不在條件中或者是經過了轉換。使得oracle只走了復合索引的其中一個字段。

由于我們的update條件是復合索引的兩個字段都在where條件里面，所以很大可能是發生了字段類型轉換。

其實從上文件指出計劃中也可以發現，出現類型轉換  INTERNAL_FUNCTION("DT") 。

如果oracle在索引字段發現有類型轉換（如數據庫是date，但是傳入的是timestemp）oralce將不走索引。
當然如果是復合索引，oracle有可能會走 INDEX FAST FULL SCAN或者INDEX SKIP SCAN。然是如果數據量很大，索引全部掃描也很費時間。必須要走INDEX UNIQUE SCAN才能保證效率。

在發現了發生數據類型轉換后，就只能從程序下手找問題，看是否傳入的值有問題。
通過查詢spring源碼，發現：
類：org.springframework.jdbc.core.StatementCreatorUtils的271行開始為最終調用jdbc驅動來通過PreparedStatement設置值的地方：
第346行，如果我們在傳參數的時候，沒有指定對應在數據庫要映射什么類型是，spring幫我們做了處理，如下

 

找到問題之后如何解決：
spring為我們提供了SqlParameterValue或者SqlParameter供我們包裝：
如果字段是date類型，我們傳入java.util.date 此時需要封裝成new SqlParameterValue(Types.TIME, value);
當然這里不能用Types.DATE 因為如果用Types.DATE最終會被轉換后為java.sql.date，將會丟失時分秒。

如果字段是timestemp類型，我們傳入java.util.date， 此時需要封裝成new SqlParameterValue(Types.TIMESTAMP, value);

這樣最終oracle就不會出現數據類型轉換。

修改之后再查詢執行計劃，oracle 順利的走了INDEX UNIQUE SCAN。效率立即從更新一條好幾秒變為瞬時。具體計劃如下：

~end~