一次索引导致查询缓慢问题

来源：互联网发布：测试工程师知乎编辑：程序博客网时间：2024/05/16 09:00

今天据项目上反应有一条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_cupidSS 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) */效果会更加。