本文分析了在Oracle数据库中针对含有复合主键的大表进行更新操作时出现性能瓶颈的原因，并给出了具体的解决方案。通过调整Spring框架中日期类型的处理方式，避免了类型转换，从而使更新操作能够正确使用复合索引。
转载地址：http://www.blogjava.net/freeman1984/archive/2015/11/15/428197.html
环境：
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 条件包含
问题分析：
通过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~
扫一扫
1550
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
