環(huán)境：
spring jdbc 3.2.3.RELEASE
oracle 11.2.0.1.0 - Production
生產(chǎn)環(huán)境存在大表（3000萬數(shù)據(jù)）：WR_MP_HOURW_R，包含復(fù)合主鍵：
mp_cp char(13),
DT date
對應(yīng)索引名稱：WR_MP_HOURW_R_PRI。
問題:針對此表的更新很慢，每條update大概需要2秒。更新的時候使用的where 條件包含
mp_cp =? and DT =?
問題分析：
通過AWR 查找到針對此表的更新有大量的物理讀，因此判定更新時的執(zhí)行計劃有問題。通過查找對應(yīng)sql的執(zhí)行計劃，發(fā)現(xiàn)沒有走索引。具體查找過程如下：
1）找到對應(yīng)的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） 根據(jù)sql_id查找計劃：select * from table(dbms_xplan.display_cursor('sql_id',null,'advanced'));
果然發(fā)現(xiàn)sql沒有走索引，而是走的 table access full。正常應(yīng)該走 INDEX UNIQUE SCAN。具體計劃如下：
SQL_ID 16gzsf0ccjjwg, child number 0
-------------------------------------
update sl_szy_mwr_intra .WR_MP_HOURW_R set MP_CD = :1 ,SPE_REG_DATA =
:2 ,HOUR_W = :3 ,DT = :4 where MP_CD = :5 and DT = :6
Plan hash value: 3498191616
-------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 35061 (100)| |
| 1 | UPDATE | WR_MP_HOURW_R | | | | |
|* 2 | TABLE ACCESS FULL| WR_MP_HOURW_R | 21 | 504 | 35061 (1)| 00:07:01 |
-------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - UPD$1
2 - UPD$1 / WR_MP_HOURW_R@UPD$1
Outline Data
-------------
/**//*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"UPD$1")
FULL(@"UPD$1" "WR_MP_HOURW_R"@"UPD$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
5 - (VARCHAR2(30), CSID=852): '2108811006101'
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("MP_CD"=:5 AND INTERNAL_FUNCTION("DT")=:6))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - (upd=2,3,4,5; cmp=2,3; cpy=2,3) "WR_MP_HOURW_R".ROWID[ROWID,10],
"MP_CD"[CHARACTER,13], "DT"[DATE,7], "HOUR_W"[NUMBER,22],
"SPE_REG_DATA"[CHARACTER,1]
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
===
SQL_ID 62mars8u2ysj1, child number 0
-------------------------------------
update sl_szy_mwr_intra .WR_MP_HOURW_R set MP_CD =
'2201050002001', SPE_REG_DATA = '0', HOUR_W = 1368.0,
DT = to_date('06-05-2015 06:00:00', 'dd-mm-yyyy hh24:mi:ss')
where MP_CD = '2201050002001' and DT = to_date('06-05-2015
06:00:00', 'dd-mm-yyyy hh24:mi:ss')
Plan hash value: 234794540
首先考慮oracle是否沒有搜集表的統(tǒng)計信息。查看屬性發(fā)現(xiàn)表和索引的統(tǒng)計都較新（oracle 基本上一個小時會收集一次，以保證執(zhí)行計劃是最優(yōu)的）。
通過hint 處理強制走索引：/*+ INDEX(WR_MP_HOURW_R WR_MP_HOURW_R_PRI)*/ 。發(fā)現(xiàn)走了INDEX SKIP SCAN。
此時說明復(fù)合索引有一個字段oracle認為不在條件中或者是經(jīng)過了轉(zhuǎn)換。使得oracle只走了復(fù)合索引的其中一個字段。
由于我們的update條件是復(fù)合索引的兩個字段都在where條件里面，所以很大可能是發(fā)生了字段類型轉(zhuǎn)換。
其實從上文件指出計劃中也可以發(fā)現(xiàn)，出現(xiàn)類型轉(zhuǎn)換 INTERNAL_FUNCTION("DT") 。
如果oracle在索引字段發(fā)現(xiàn)有類型轉(zhuǎn)換（如數(shù)據(jù)庫是date，但是傳入的是timestemp）oralce將不走索引。
當(dāng)然如果是復(fù)合索引，oracle有可能會走 INDEX FAST FULL SCAN或者INDEX SKIP SCAN。然是如果數(shù)據(jù)量很大，索引全部掃描也很費時間。必須要走INDEX UNIQUE SCAN才能保證效率。
在發(fā)現(xiàn)了發(fā)生數(shù)據(jù)類型轉(zhuǎn)換后，就只能從程序下手找問題，看是否傳入的值有問題。
通過查詢spring源碼，發(fā)現(xiàn)：
類：org.springframework.jdbc.core.StatementCreatorUtils的271行開始為最終調(diào)用jdbc驅(qū)動來通過PreparedStatement設(shè)置值的地方：
第346行，如果我們在傳參數(shù)的時候，沒有指定對應(yīng)在數(shù)據(jù)庫要映射什么類型是，spring幫我們做了處理，如下
//這里說明我們沒有指定要映射到數(shù)據(jù)庫的什么類型
else if (sqlType == SqlTypeValue.TYPE_UNKNOWN) 
{
if (isStringValue(inValue.getClass())) {
ps.setString(paramIndex, inValue.toString());
}
//看這里，所有java.util.Date，java.util.Date，java.sql.Date，java.sql.Timestamp都被用了ps.setTimestamp處理了，這就是根本原因
else if (isDateValue(inValue.getClass())) {
ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime()));
}
else if (inValue instanceof Calendar) {
Calendar cal = (Calendar) inValue;
ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal);
}
else {
// Fall back to generic setObject call without SQL type specified.
ps.setObject(paramIndex, inValue);
}
}
private static boolean isDateValue(Class inValueType) {
return (java.util.Date.class.isAssignableFrom(inValueType) &&
!(java.sql.Date.class.isAssignableFrom(inValueType) ||
java.util.Date.class.isAssignableFrom(inValueType) ||
java.sql.Timestamp.class.isAssignableFrom(inValueType)));
}
找到問題之后如何解決：
spring為我們提供了SqlParameterValue或者SqlParameter供我們包裝：
如果字段是date類型，我們傳入java.util.date 此時需要封裝成new SqlParameterValue(Types.TIME, value);
當(dāng)然這里不能用Types.DATE 因為如果用Types.DATE最終會被轉(zhuǎn)換后為java.sql.date，將會丟失時分秒。
如果字段是timestemp類型，我們傳入java.util.date， 此時需要封裝成new SqlParameterValue(Types.TIMESTAMP, value);
這樣最終oracle就不會出現(xiàn)數(shù)據(jù)類型轉(zhuǎn)換。
修改之后再查詢執(zhí)行計劃，oracle 順利的走了INDEX UNIQUE SCAN。效率立即從更新一條好幾秒變?yōu)樗矔r。具體計劃如下：
SQL_ID 62mars8u2ysj1, child number 0
-------------------------------------
update sl_szy_mwr_intra .WR_MP_HOURW_R set MP_CD =
'2201050002001', SPE_REG_DATA = '0', HOUR_W = 1368.0,
DT = to_date('06-05-2015 06:00:00', 'dd-mm-yyyy hh24:mi:ss')
where MP_CD = '2201050002001' and DT = to_date('06-05-2015
06:00:00', 'dd-mm-yyyy hh24:mi:ss')
Plan hash value: 234794540
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 3 (100)| |
| 1 | UPDATE | WR_MP_HOURW_R | | | | |
|* 2 | INDEX UNIQUE SCAN| WR_MP_HOURW_R_PRI | 1 | 24 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - UPD$1
2 - UPD$1 / WR_MP_HOURW_R@UPD$1
Outline Data
-------------
/**//*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"UPD$1")
INDEX(@"UPD$1" "WR_MP_HOURW_R"@"UPD$1" ("WR_MP_HOURW_R"."DT"
"WR_MP_HOURW_R"."MP_CD"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DT"=TO_DATE(' 2015-05-06 06:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"MP_CD"='2201050002001')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - (upd=2,3,4,5; cmp=2,3; cpy=2,3) "WR_MP_HOURW_R".ROWID[ROWID,10],
"MP_CD"[CHARACTER,13], "DT"[DATE,7], "HOUR_W"[NUMBER,22],
"SPE_REG_DATA"[CHARACTER,1]
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
~end~