还是时间惹的祸
還是時(shí)間惹的禍，今天看到玄爺?shù)膁b周報(bào)中一條sql的邏輯讀很高，執(zhí)行量每小時(shí)也有幾千次，瞟了一眼sql，發(fā)現(xiàn)了異常情況，那就是t.gmt_create >= :2 ? and t.gmt_create <= :3，此時(shí)我想這條sql應(yīng)該有救了。
先說(shuō)一下知識(shí)點(diǎn)吧，java.sql定義時(shí)間類型包括三個(gè)類，date, time, 和 timestamp，分別用來(lái)表示日期（無(wú)時(shí)間信息，eg: yyyy-mm-dd），時(shí)間（只處理時(shí)間，無(wú)日期部分, eg: hh:mm:ss）和時(shí)間戳（精確到納秒級(jí)別）。在它們都繼承自java.util.date。
而oracle與jdbc之間的類型映射
date ?java.sql.date
date ?java.sql.time
timestamp ? ?java.sql.timestamp
oracle數(shù)據(jù)庫(kù)字段類型主要有date、timestamp。
在9i以后、11g以前的oracle jdbc驅(qū)動(dòng)中存在一個(gè)會(huì)丟失date類型字段的時(shí)間信息的bug，原因是其jdbc驅(qū)動(dòng)將oracle的date類型處理為java.sql.date 類型，這就丟失了時(shí)間部分；事實(shí)上，如果是使用ibatis，pojo屬性的類型設(shè)置為java.util.date，確保 jdbctype不為 date或者time，則避免了這個(gè)bug。因?yàn)榇藭r(shí)ibatis會(huì)以java.sql.timestamp來(lái)處理該字段；
簡(jiǎn)而言之，oracle在處理ibatis傳入到數(shù)據(jù)庫(kù)的變量時(shí)候，解析為timestamp類型，如果數(shù)據(jù)庫(kù)中時(shí)間字段定義為date類型，由于timestamp類型的精度比date類型的精度高，所以oracle會(huì)做出隱身轉(zhuǎn)換，將date類型轉(zhuǎn)換為timestamp類型，那么創(chuàng)建在date類型上的索引將不會(huì)使用到；
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
——————————————————————————————
| operation ? ? ? ? ? ? ? ? ? ? ?| phv/object name ? ? ? ? ? ? ? | ?rows | bytes| ? cost |
——————————————————————————————
|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， 奇怪的是我們沒(méi)有在value_string中看到有g(shù)mt_create的值；從執(zhí)行計(jì)劃上我們看到filter，原表中g(shù)mt_create為date數(shù)據(jù)類型，這里可以斷定是由于隱式轉(zhuǎn)換導(dǎo)致了時(shí)間字段在索引中排序沒(méi)有作用了，所以還要過(guò)濾filter，在索引中的gmt_create字段沒(méi)有起到過(guò)濾的作用，從而導(dǎo)致了大量的邏輯讀；
驗(yàn)證如下：
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’—變量值傳入時(shí)間的字符串
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
—————————————————————————————–
| id ?| operation ? ? ? ? | name ? ? ? ? ? ? ? ?| rows ?| bytes | cost (%cpu)| time ? ? |
—————————————————————————————–
| ? 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轉(zhuǎn)換一下
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
—————————————————————————————–
| id ?| operation ? ? ? ? | name ? ? ? ? ? ? ? ?| rows ?| bytes | cost (%cpu)| time ? ? |
—————————————————————————————–
| ? 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’)–模仿沒(méi)有經(jīng)過(guò)處理的變量值，ibatis轉(zhuǎn)換為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
——————————————————————————————
| id ?| operation ? ? ? ? ?| name ? ? ? ? ? ? ? ?| rows ?| bytes | cost (%cpu)| time ? ? |
——————————————————————————————
| ? 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
可以看到當(dāng)我們采用to_timestatmp轉(zhuǎn)換gmt_create傳入的值后，其執(zhí)行計(jì)劃是和沒(méi)有做任何處理查詢變量的是一樣的，
所以接下來(lái)的兩種方案可以為：
使用cast(:2 as date)轉(zhuǎn)換一下；
變量定義為字符串，在采用to_date(:2,’yyyy-mm-dd hh24:mi:ss’);
程序中綁定為date：gmt_create<#2:date#；
結(jié)論：在處理時(shí)間類型的時(shí)候，我們需要特別的謹(jǐn)慎，避免隱式轉(zhuǎn)換的發(fā)生.
總結(jié)
- 上一篇： 设计模式之Composite模式(笔记)
- 下一篇： 公路商店app如何看文