oracle 索引字段包含date类型，使用spring jdbc更新时不走索引，而是走table access full的问题

最新推荐文章于 2024-06-28 12:30:10 发布

可口可乐的围脖

最新推荐文章于 2024-06-28 12:30:10 发布

阅读量8.7k

点赞数

分类专栏： Oracel性能优化分析

Oracel性能优化分析专栏收录该内容

5 篇文章 0 订阅

订阅专栏

转载地址：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 条件包含

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

-- -----------------------------------------------------------------------------------

-- -----------------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | | | 35061 ( 100 ) | |

|* 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

-- ---------------------------------------------------------------------------------------

-- ---------------------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | | | 3 ( 100 ) | |

|* 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~

可口可乐的围脖

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录