环境：
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。具体计划如下：
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是否没有搜集表的统计信息。查看属性发现表和索引的统计都较新（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帮我们做了处理，如下
//这里说明我们没有指定要映射到数据库的什么类型
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);
当然这里不能用types.date 因为如果用types.date最终会被转换后为java.sql.date，将会丢失时分秒。
如果字段是timestemp类型，我们传入java.util.date， 此时需要封装成new sqlparametervalue(types.timestamp, value);
这样最终oracle就不会出现数据类型转换。
修改之后再查询执行计划，oracle 顺利的走了index unique scan。效率立即从更新一条好几秒变为瞬时。具体计划如下：
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~