还是时间惹的祸-CSDN博客

还是时间惹的祸，今天看到玄爷的db周报中一条sql的逻辑读很高，执行量每小时也有几千次，瞟了一眼sql，发现了异常情况，那就是t.gmt_create >= :2 and t.gmt_create <= :3，此时我想这条sql应该有救了。

先说一下知识点吧，java.sql定义时间类型包括三个类，date, time, 和 timestamp，分别用来表示日期（无时间信息，eg: yyyy-mm-dd），时间（只处理时间，无日期部分, eg: hh:mm:ss）和时间戳（精确到纳秒级别）。在它们都继承自java.util.date。

而oracle与jdbc之间的类型映射

date java.sql.date

date java.sql.time

timestamp java.sql.timestamp

oracle数据库字段类型主要有date、timestamp。

在9i以后、11g以前的oracle jdbc驱动中存在一个会丢失date类型字段的时间信息的bug，原因是其jdbc驱动将oracle的date类型处理为java.sql.date 类型，这就丢失了时间部分；事实上，如果是使用ibatis，pojo属性的类型设置为java.util.date，确保 jdbctype不为 date或者time，则避免了这个bug。因为此时ibatis会以java.sql.timestamp来处理该字段；

简而言之，oracle在处理ibatis传入到数据库的变量时候，解析为timestamp类型，如果数据库中时间字段定义为date类型，由于timestamp类型的精度比date类型的精度高，所以oracle会做出隐身转换，将date类型转换为timestamp类型，那么创建在date类型上的索引将不会使用到；

select count(*) from order t where (o_type = 2 or o_type = 3) and p_type = 1 and t.o_status = :1 and t.gmt_create >= :2 and t.gmt_create <= :3 and t.m_type = :4 and t.a_id = :5 and t.a_item_id = :6

——————————————————————————————

——————————————————————————————

|select statement |———- 1348628516 ———| | | 3 |

|sort aggregate | | 1 | 74 | |

| filter | | | | |

| index range scan |ind_o_artid | 1 | 74 | 3 |

——————————————————————————————

索引idx_artid(a_id, p_type, gmt_create, o_type, m_type, a_item_id, o_status)

跟踪一下绑定变量的值：

child_number position name datatype_string max_length last_captured value_string

5 1 :1 number 22 2011-04-25 16:30:07 3

5 2 :2 timestamp 11 2011-04-25 16:30:07

5 3 :3 timestamp 11 2011-04-25 16:30:07

5 4 :4 number 22 2011-04-25 16:30:07 6

5 5 :5 number 22 2011-04-25 16:30:07 940

5 6 :6 number 22 2011-04-25 16:30:07 1791

可以看到oracle将gmt_create绑定为timestamp，奇怪的是我们没有在value_string中看到有gmt_create的值；从执行计划上我们看到filter，原表中gmt_create为date数据类型，这里可以断定是由于隐式转换导致了时间字段在索引中排序没有作用了，所以还要过滤filter，在索引中的gmt_create字段没有起到过滤的作用，从而导致了大量的逻辑读；

验证如下：

11:24:02 bss2@ crm>select /*+index(t idx_artid)*/ count(*)

11:24:25 2 from order t

11:24:25 3 where (o_type = 2 or o_type = 3)

11:24:25 4 and p_type = 1

11:24:25 5 and t.o_status = 3

11:24:25 6 and t.gmt_create >= ‘2011-04-10 11:20:22’

11:24:25 7 and t.gmt_create <= ‘2011-04-13 11:20:22’—变量值传入时间的字符串

11:24:25 8 and t.m_type = 6

11:24:25 9 and t.a_id = 965

11:24:25 10 and t.a_item_id =1863;

1 row selected.

elapsed: 00:00:00.01

execution plan

———————————————————

plan hash value: 3276776702

—————————————————————————————–

—————————————————————————————–

| 0 | select statement | | 1 | 74 | 2 (0)| 00:00:01 |

| 1 | sort aggregate | | 1 | 74 | | |

|* 2 | index range scan| idx_artid | 1 | 74 | 2 (0)| 00:00:01 |

—————————————————————————————–

predicate information (identified by operation id):

—————————————————

2 – access(“t”.”a_id”=965 and “p_type”=1 and

“t”.”gmt_create”>=to_date(‘ 2011-04-10 11:20:22’, ‘yyyy-mm-dd hh24:mi:ss’) and

“t”.”m_type”=6 and “t”.”a_item_id”=1863 and “t”.”o_status”=3 and

“t”.”gmt_create”<=to_date(‘ 2011-04-13 11:20:22’, ‘yyyy-mm-dd hh24:mi:ss’))

filter((“o_type”=2 or “o_type”=3) and “t”.”m_type”=6 and

“t”.”a_item_id”=1863 and “t”.”o_status”=3)

statistics

———————————————————-

1 recursive calls

0 db block gets

6 consistent gets

0 physical reads

0 redo size

11:28:11 bss2@ crm>select /*+index(t idx_artid)*/ count(*)

11:28:12 2 from order t

11:28:12 3 where (o_type = 2 or o_type = 3)

11:28:12 4 and p_type = 1

11:28:12 5 and t.o_status = 3

11:28:12 6 and t.gmt_create >= to_date(‘2011-04-10 11:20:22′,’yyyy-mm-dd hh24:mi:ss’)

11:28:12 7 and t.gmt_create <= to_date(‘2011-04-13 11:20:22′,’yyyy-mm-dd hh24:mi:ss’)–用to_date转换一下

11:28:12 8 and t.m_type = 6

11:28:12 9 and t.a_id = 965

11:28:12 10 and t.a_item_id =1863;

1 row selected.

elapsed: 00:00:00.01

execution plan

———————————————————-

plan hash value: 3276776702

—————————————————————————————–

—————————————————————————————–

| 0 | select statement | | 1 | 74 | 2 (0)| 00:00:01 |

| 1 | sort aggregate | | 1 | 74 | | |

|* 2 | index range scan| idx_artid | 1 | 74 | 2 (0)| 00:00:01 |

—————————————————————————————–

predicate information (identified by operation id):

—————————————————

2 – access(“t”.”a_id”=965 and “p_type”=1 and

“t”.”gmt_create”>=to_date(‘ 2011-04-10 11:20:22’, ‘syyyy-mm-dd hh24:mi:ss’) and

“t”.”m_type”=6 and “t”.”a_item_id”=1863 and “t”.”o_status”=3 and

“t”.”gmt_create”<=to_date(‘ 2011-04-13 11:20:22’, ‘syyyy-mm-dd hh24:mi:ss’))

filter((“o_type”=2 or “o_type”=3) and “t”.”m_type”=6 and

“t”.”a_item_id”=1863 and “t”.”o_status”=3)

statistics

———————————————————-

0 recursive calls

0 db block gets

6 consistent gets

0 physical reads

0 redo size

11:24:26 bss2@ crm>select /*+index(t idx_artid)*/ count(*)

11:26:31 2 from order t

11:26:31 3 where (o_type = 2 or o_type = 3)

11:26:31 4 and p_type = 1

11:26:31 5 and t.o_status = 3

11:26:31 6 and t.gmt_create >= to_timestamp(‘2011-04-10 11:20:22′,’yyyy-mm-dd hh24:mi:ss’)

11:26:31 7 and t.gmt_create <= to_timestamp(‘2011-04-13 11:20:22′,’yyyy-mm-dd hh24:mi:ss’)–模仿没有经过处理的变量值，ibatis转换为timestamp

11:26:31 8 and t.m_type = 6

11:26:31 9 and t.a_id = 965

11:26:31 10 and t.a_item_id =1863;

1 row selected.

elapsed: 00:00:00.20

execution plan

———————————————————-

plan hash value: 2438882768

——————————————————————————————

——————————————————————————————

| 0 | select statement | | 1 | 74 | 3 (0)| 00:00:01 |

| 1 | sort aggregate | | 1 | 74 | | |

|* 2 | filter | | | | | |

|* 3 | index range scan| idx_artid | 1 | 74 | 3 (0)| 00:00:01 |

——————————————————————————————

predicate information (identified by operation id):

—————————————————

2 – filter(to_timestamp(‘2011-04-10 11:20:22′,’yyyy-mm-dd

hh24:mi:ss’)<=to_timestamp(‘2011-04-13 11:20:22′,’yyyy-mm-dd hh24:mi:ss’))

3 – access(“t”.”a_id”=965 and “p_type”=1 and “t”.”m_type”=6 and

“t”.”a_item_id”=1863 and “t”.”o_status”=3)

filter((“o_type”=2 or “o_type”=3) and “t”.”m_type”=6 and

“t”.”a_item_id”=1863 and internal_function(“t”.”gmt_create”)>=to_timestamp(‘

2011-04-10 11:20:22′,’yyyy-mm-dd hh24:mi:ss’) and

internal_function(“t”.”gmt_create”)<=to_timestamp(‘2011-04-13

11:20:22′,’yyyy-mm-dd hh24:mi:ss’) and “t”.”o_status”=3)

statistics

———————————————————-

1 recursive calls

0 db block gets

698 consistent gets

0 physical reads

0 redo size

可以看到当我们采用to_timestatmp转换gmt_create传入的值后，其执行计划是和没有做任何处理查询变量的是一样的，

所以接下来的两种方案可以为：

使用cast(:2 as date)转换一下；

变量定义为字符串，在采用to_date(:2,’yyyy-mm-dd hh24:mi:ss’);

程序中绑定为date：gmt_create<#2:date#；

结论：在处理时间类型的时候，我们需要特别的谨慎，避免隐式转换的发生.