今天据项目上反应有一条sql语句在查询77分钟后仍没有返回结果,sql如下:
SELECT T.*
FROM (SELECT 'GET' TYPE,
SUM(CASE
WHEN NVL(RESPONSE_TIME, 0) > NVL(SUSPEND_TIME, 0) AND
NVL(RESPONSE_TIME, 0) - NVL(SUSPEND_TIME, 0) -NVL(RESUME_TIME, 0) > 0 THEN
NVL(RESPONSE_TIME, 0) - NVL(SUSPEND_TIME, 0) -NVL(RESUME_TIME, 0)
ELSE RESPONSE_TIME
END) RESPONSE_TIME,
SUM(CONTENT_LENGTH) CONTENT_LENGTH,
ROUND(SUM(NVL(CONTENT_LENGTH, 0)) * 8000 / 1024 /
SUM(CASE
WHEN NVL(RESPONSE_TIME, 0) > NVL(SUSPEND_TIME, 0) AND
NVL(RESPONSE_TIME, 0) - NVL(SUSPEND_TIME, 0) -NVL(RESUME_TIME, 0) > 0 THEN
NVL(RESPONSE_TIME, 0) - NVL(SUSPEND_TIME, 0) -NVL(RESUME_TIME, 0)
ELSE RESPONSE_TIME
END),
3) SPEED
FROM CUPID T
WHERE (T.TTIME >= TIMESTAMP' 2012-02-06 00:00:00' AND T.TTIME < TIMESTAMP' 2012-02-06 23:59:59')
AND exists (SELECT * FROM s_cupid SS WHERE UPPER(T.URI_MAIN) like SS.VALUE)
AND CONTENT_LENGTH IS NOT NULL
AND ((NVL(RESPONSE_TIME, 0) > NVL(SUSPEND_TIME, 0) AND
NVL(RESPONSE_TIME, 0) - NVL(SUSPEND_TIME, 0) - NVL(RESUME_TIME, 0) > 0) OR RESPONSE_TIME IS NOT NULL)
AND PACKETS > 3
GROUP BY 'GET'
ORDER BY SPEED) T
WHERE ROWNUM <= 5000
sql有点长 但是还是坚持贴出来,看到sql第一眼就觉得是exists这个问题 因为在s_cupid 表中仅有1条数据,使用exists明显是不正确的。所以就改成了in,改后sql如下:
AND UPPER(T.URI_MAIN) in
(SELECT distinct UPPER(T.URI_MAIN) FROM S_HOST_380327B34 SS
WHERE UPPER(T.URI_MAIN) like SS.VALUE)
从新跑sql,结果跑了33分钟,这显然不对,因此对CUPID和s_cupid做分析,其中cupid表是个分区表,按ttime分区,及按小时分区,ttime上有索引,这张表每小时的数据量大约在5,000,000条左右,那么以T.TTIME >= TIMESTAMP' 2012-02-06 00:00:00' AND T.TTIME < TIMESTAMP' 2012-02-06 23:59:59'为时间段查询数据,也就是查询18个分区的数据,查看此sql的执行计划
1、set autotrace trace exp;
2、sql语句
3、
PARTITION RANGE ITERATOR | | 235 | 9870 | 348 (0)| 00:00:05 | 505 | 5
TABLE ACCESS BY LOCAL INDEX ROWID| cupid | 235 | 9870 | 348 (0)| 00:00:05 | 505 | 5
INDEX RANGE SCAN | GETTTIME | 1510 | | 7 (0)| 0
这个执行计划明显是走了ttime上的索引,因此选择禁掉索引,/*+ no_index(T,gettime)*/,在这里先前使用/*+ no_index(cupid,gettime)*/发现执行计划没变,也就说hint没起作用,后来想到表在有别名的时候必须在hint中使用别名,否则无效;
修改完后重新获得执行计划如下:
| FILTER | | | | | | | |
| 6 | PARTITION RANGE ITERATOR| | 235 | 9870 | 957K (1)| 03:11:31 | 505 | 528 |
|* 7 | TABLE ACCESS FULL | GB_GET | 235 | 9870 | 957K (1)| 03:11:31 | 505 | 528 |
没有选择索引,而是先扫描分区列表,找到对应分区,之后对分区进行全表的扫描,此sql3分多就计算出结构,问题解决,但是要删掉索引还需谨慎,你应该确保你的环境下在使用ttime做精确定位的语句很少的时候在drop掉,另外如果使用/*+ first_row(5000) */效果会更加。