疯狂

STANDING ON THE SHOULDERS OF GIANTS
posts - 480, comments - 486, trackbacks - 0, articles - 1
  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

环境
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~


只有注册用户登录后才能发表评论。


网站导航: