原来系统中运行2、3分钟的sql，突然2天跑不完
sql如下：
SELECT info.*,keyinfo.KEYSORT AS keysort
FROM (SELECT rep.SrhTime AS rq,
bus.providerid AS cq,
bus.srhpc AS pc,
cor.srhzs AS zs,
act.actname AS th,
cor.county_seat AS ct,
cor.cyd_type AS ib,
cor.ten_medal AS me,
cor.srhas AS sa,
bus.srhbz AS bz,
cor.srhjy AS jy,
cor.srhve AS ve,
rep.enddate AS et,
bus.go_type AS hd,
bus.go_name AS bq,
bus.name_318 AS bd,
rep.srhcc AS cc,
bus.srhcm AS cm,
bus.srhcy AS cy,
cor.srhcz AS cz,
bus.srhcd AS cd,
cor.deposit_state AS gd,
weisite AS ss,
cor.srhveunit AS vu,
cor.longitude AS lo,
cor.latitude AS lt,
bus.advance_payment AS ors,
'' AS pr,
bus.sales_amount AS ta,
bus.sales_volume AS tc,
cor.qq AS qq,
'' AS tf,
'' AS mt,
'' AS kn,
'' AS pn,
cor.telephone AS hb,
cor.fax AS he,
cor.SALES_VOLUME AS tv,
bus.sales_count AS tu,
'' AS ca,
cor.ishighquality AS fs,
bus.supply_count AS gy,
cor.userid AS ud,
usrcount.states AS yp,
bus.enquiry_status AS xp,
cor.mobile_telephone AS ml,
TRUNC (
( TO_NUMBER (
cor.authendate
- TO_DATE ('1970-01-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
* 86400
- 8 * 3600)
/ 30)
AS tb,
bus.pubtype AS pt,
cor.srhbu AS bu,
bus.srhtp AS tp,
bus.srhco AS co,
bus.srhcb AS cb,
bus.srhla AS la,
cor.groupid AS gn,
cor.shopname AS sn,
bus.is_support_trade AS zf,
bus.mm AS mm,
rep.srhmi AS mi,
REPLACE (bus.srhtitle,CHR (26),'') AS bt,
REPLACE (bus.srhcontent,'') AS jj,
REPLACE (cor.srhintro,'') AS intro,
bus.Yview360 || ',' || bus.srhim AS im,
'M' || rep.bc_id AS id,
cor.srhtitle AS qy,
cor.username AS un,
cor.srhvn AS vn,
cor.srhtm AS tm,
bus.srhup AS UP,
bus.srhba AS ba,
bus.srhpm AS pm,
bus.srhbb || '|' || bus.actid AS bb,
bus.bc_id AS infoid,
cor.mainpro AS zy,
'' AS pa,
bus.srhqu AS qu,
cor.srhsl AS sl,
'' AS ag,
'' AS ty,
bus.srhmo AS mo,
TRUNC (
( TO_NUMBER (
cor.srhsu
- TO_DATE ('1970-01-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
* 86400
- 8 * 3600)
/ 30)
AS su,
cor.user_level AS ul,
bus.evaluate_count AS ec,
TRUNC (
( TO_NUMBER (
bus.searchdate
- TO_DATE ('1970-01-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
* 86400
- 8 * 3600)
/ 30)
AS pq,
cor.contacter
FROM (SELECT /*+ index(aa IDX_BUSIN_HOT_BITMAP_SEARCHS IDX_BUSIN_HOT_BITMAP_OPERS) */ *
FROM busin_hot aa
WHERE srhcc = '001'
AND searchstate = '1'
AND operstate = '1'
AND loadstates = 'H'
AND searchdate <=
TO_DATE ('20150901 23:59:59',
'yyyymmdd hh24:mi:ss')
AND searchdate >=
TO_DATE ('20150725 00:00:00',
'yyyymmdd hh24:mi:ss')) rep
LEFT JOIN busin_srh bus ON rep.bc_id = bus.bc_id
LEFT JOIN provider_srh cor ON bus.providerid = cor.providerid
LEFT JOIN busin_activity act ON bus.actid = act.actid
LEFT JOIN user_averagequalitylog usrcount
ON (cor.providerid = usrcount.providerid)) info
LEFT JOIN keyword_info_srh keyinfo
ON (info.infoid = keyinfo.infoid AND keyinfo.infotype = '0')
执行计划：
Plan hash value: 1031842883 ---------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 65385 (100)| | | | | 1 | NESTED LOOPS OUTER | | 50 | 235K| 65385 (2)| 00:13:05 | | | |* 2 | HASH JOIN RIGHT OUTER | | 50 | 232K| 65234 (2)| 00:13:03 | | | | 3 | MAT_VIEW ACCESS FULL | BUSIN_ACTIVITY | 24 | 336 | 3 (0)| 00:00:01 | | | | 4 | NESTED LOOPS OUTER | | 10 | 23760 | 65231 (2)| 00:13:03 | | | | 5 | NESTED LOOPS OUTER | | 10 | 23660 | 21806 (1)| 00:04:22 | | | | 6 | NESTED LOOPS OUTER | | 10 | 21080 | 21786 (1)| 00:04:22 | | | | 7 | PARTITION RANGE ITERATOR | | 10 | 890 | 21746 (1)| 00:04:21 | 140 | 142 | |* 8 | TABLE ACCESS BY LOCAL INDEX ROWID| BUSIN_HOT | 10 | 890 | 21746 (1)| 00:04:21 | 140 | 142 | | 9 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |* 10 | BITMAP INDEX SINGLE VALUE | IDX_BUSIN_HOT_BITMAP_SEARCHS | | | | | 140 | 142 | | 11 | TABLE ACCESS BY GLOBAL INDEX ROWID| BUSIN_SRH | 1 | 2019 | 4 (0)| 00:00:01 | ROW L | ROW L | |* 12 | INDEX RANGE SCAN | ID_BUSIN_SRH_BC_ID1 | 1 | | 3 (0)| 00:00:01 | | | | 13 | TABLE ACCESS BY GLOBAL INDEX ROWID | PROVIDER_SRH | 1 | 258 | 2 (0)| 00:00:01 | ROW L | ROW L | |* 14 | INDEX UNIQUE SCAN | PK166_1_1 | 1 | | 1 (0)| 00:00:01 | | | |* 15 | TABLE ACCESS FULL | USER_AVERAGEQUALITYLOG | 1 | 10 | 4342 (2)| 00:00:53 | | | |* 16 | TABLE ACCESS BY INDEX ROWID | KEYWORD_INFO_SRH | 1 | 50 | 3 (0)| 00:00:01 | | | |* 17 | INDEX RANGE SCAN | I_KEYINFOSRH_TONUM_INFOID | 1 | | 2 (0)| 00:00:01 | | | ---------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("BUS"."ACTID"="ACT"."ACTID") 8 - filter(("SEARCHDATE">=TO_DATE(' 2015-07-25 00:00:00','syyyy-mm-dd hh24:mi:ss') AND "LOADSTATES"='H' AND "SEARCHDATE"<=TO_DATE(' 2015-09-01 23:59:59','syyyy-mm-dd hh24:mi:ss') AND "SRHCC"='001' AND "OPERSTATE"='1')) 10 - access("SEARCHSTATE"='1') 12 - access("BUSIN_HOT"."BC_ID"="BUS"."BC_ID") 14 - access("BUS"."PROVIDERID"="COR"."PROVIDERID") 15 - filter("COR"."PROVIDERID"="USRCOUNT"."PROVIDERID") 16 - filter("KEYINFO"."INFOTYPE"='0') 17 - access("BUS"."BC_ID"="KEYINFO"."SYS_NC00006$")
hint不要走位图索引的执行计划：
SELECT /*+ no_index(aa IDX_BUSIN_HOT_BITMAP_SEARCHS) */ * ------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 73655 (100)| | | | | 1 | NESTED LOOPS OUTER | | 50 | 235K| 73655 (2)| 00:14:44 | | | |* 2 | HASH JOIN RIGHT OUTER | | 50 | 232K| 73505 (2)| 00:14:43 | | | | 3 | MAT_VIEW ACCESS FULL | BUSIN_ACTIVITY | 24 | 336 | 3 (0)| 00:00:01 | | | | 4 | NESTED LOOPS OUTER | | 10 | 23760 | 73502 (2)| 00:14:43 | | | | 5 | NESTED LOOPS OUTER | | 10 | 23660 | 30077 (1)| 00:06:01 | | | | 6 | NESTED LOOPS OUTER | | 10 | 21080 | 30057 (1)| 00:06:01 | | | | 7 | PARTITION RANGE ITERATOR | | 10 | 890 | 30017 (1)| 00:06:01 | 140 | 142 | |* 8 | TABLE ACCESS FULL | BUSIN_HOT | 10 | 890 | 30017 (1)| 00:06:01 | 140 | 142 | | 9 | TABLE ACCESS BY GLOBAL INDEX ROWID| BUSIN_SRH | 1 | 2019 | 4 (0)| 00:00:01 | ROW L | ROW L | |* 10 | INDEX RANGE SCAN | ID_BUSIN_SRH_BC_ID1 | 1 | | 3 (0)| 00:00:01 | | | | 11 | TABLE ACCESS BY GLOBAL INDEX ROWID | PROVIDER_SRH | 1 | 258 | 2 (0)| 00:00:01 | ROW L | ROW L | |* 12 | INDEX UNIQUE SCAN | PK166_1_1 | 1 | | 1 (0)| 00:00:01 | | | |* 13 | TABLE ACCESS FULL | USER_AVERAGEQUALITYLOG | 1 | 10 | 4342 (2)| 00:00:53 | | | |* 14 | TABLE ACCESS BY INDEX ROWID | KEYWORD_INFO_SRH | 1 | 50 | 3 (0)| 00:00:01 | | | |* 15 | INDEX RANGE SCAN | I_KEYINFOSRH_TONUM_INFOID | 1 | | 2 (0)| 00:00:01 | | | ------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("BUS"."ACTID"="ACT"."ACTID") 8 - filter(("SEARCHDATE">=TO_DATE(' 2015-07-25 00:00:00','syyyy-mm-dd hh24:mi:ss') AND "LOADSTATES"='H' AND "SEARCHSTATE"='1' AND "SEARCHDATE"<=TO_DATE(' 2015-09-01 23:59:59','syyyy-mm-dd hh24:mi:ss') AND "SRHCC"='001' AND "OPERSTATE"='1')) 10 - access("AA"."BC_ID"="BUS"."BC_ID") 12 - access("BUS"."PROVIDERID"="COR"."PROVIDERID") 13 - filter("COR"."PROVIDERID"="USRCOUNT"."PROVIDERID") 14 - filter("KEYINFO"."INFOTYPE"='0') 15 - access("BUS"."BC_ID"="KEYINFO"."SYS_NC00006$")3分钟
此表还有一个位图索引，配合使用的执行计划：
SELECT /*+ index(aa IDX_BUSIN_HOT_BITMAP_SEARCHS IDX_BUSIN_HOT_BITMAP_OPERS) */ * ---------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 65393 (100)| | | | | 1 | NESTED LOOPS OUTER | | 50 | 235K| 65393 (2)| 00:13:05 | | | |* 2 | HASH JOIN RIGHT OUTER | | 50 | 232K| 65243 (2)| 00:13:03 | | | | 3 | MAT_VIEW ACCESS FULL | BUSIN_ACTIVITY | 24 | 336 | 3 (0)| 00:00:01 | | | | 4 | NESTED LOOPS OUTER | | 10 | 23760 | 65239 (2)| 00:13:03 | | | | 5 | NESTED LOOPS OUTER | | 10 | 23660 | 21814 (1)| 00:04:22 | | | | 6 | NESTED LOOPS OUTER | | 10 | 21080 | 21794 (1)| 00:04:22 | | | | 7 | PARTITION RANGE ITERATOR | | 10 | 890 | 21754 (1)| 00:04:22 | 140 | 142 | |* 8 | TABLE ACCESS BY LOCAL INDEX ROWID| BUSIN_HOT | 10 | 890 | 21754 (1)| 00:04:22 | 140 | 142 | | 9 | BITMAP CONVERSION TO ROWIDS | | | | | | | | | 10 | BITMAP AND | | | | | | | | |* 11 | BITMAP INDEX SINGLE VALUE | IDX_BUSIN_HOT_BITMAP_SEARCHS | | | | | 140 | 142 | |* 12 | BITMAP INDEX SINGLE VALUE | IDX_BUSIN_HOT_BITMAP_OPERS | | | | | 140 | 142 | | 13 | TABLE ACCESS BY GLOBAL INDEX ROWID| BUSIN_SRH | 1 | 2019 | 4 (0)| 00:00:01 | ROW L | ROW L | |* 14 | INDEX RANGE SCAN | ID_BUSIN_SRH_BC_ID1 | 1 | | 3 (0)| 00:00:01 | | | | 15 | TABLE ACCESS BY GLOBAL INDEX ROWID | PROVIDER_SRH | 1 | 258 | 2 (0)| 00:00:01 | ROW L | ROW L | |* 16 | INDEX UNIQUE SCAN | PK166_1_1 | 1 | | 1 (0)| 00:00:01 | | | |* 17 | TABLE ACCESS FULL | USER_AVERAGEQUALITYLOG | 1 | 10 | 4342 (2)| 00:00:53 | | | |* 18 | TABLE ACCESS BY INDEX ROWID | KEYWORD_INFO_SRH | 1 | 50 | 3 (0)| 00:00:01 | | | |* 19 | INDEX RANGE SCAN | I_KEYINFOSRH_TONUM_INFOID | 1 | | 2 (0)| 00:00:01 | | | ---------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("BUS"."ACTID"="ACT"."ACTID") 8 - filter(("SEARCHDATE">=TO_DATE(' 2015-07-25 00:00:00','syyyy-mm-dd hh24:mi:ss') AND "SRHCC"='001')) 11 - access("SEARCHSTATE"='1') 12 - access("OPERSTATE"='1') 14 - access("AA"."BC_ID"="BUS"."BC_ID") 16 - access("BUS"."PROVIDERID"="COR"."PROVIDERID") 17 - filter("COR"."PROVIDERID"="USRCOUNT"."PROVIDERID") 18 - filter("KEYINFO"."INFOTYPE"='0') 19 - access("BUS"."BC_ID"="KEYINFO"."SYS_NC00006$")比上面不走位图索引略快
生产上使用位图索引要慎重！！！