ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sql调优一例---索引排序hint

sql调优一例---索引排序hint

原创 Linux操作系统 作者:skuary 时间:2012-04-23 17:34:26 0 删除 编辑

刚刚酒窖开发人员发了封邮件给我,说酒窖测试库现在的一个查询出奇的慢,一直在执行状态,让我这里给看看,具体的sql语句如下:

SELECT b.*
FROM (SELECT a.*, ROWNUM num
FROM (SELECT /*+ index ( a IND_M_SNS_STATUS_IDX3)*/
A.*,
B.NICK_NAME,
(SELECT COUNT(MGI.GOODS_ID)
FROM  M_GOODS_INFO MGI
WHERE a.GOODS_ID = MGI.GOODS_ID) AS GOODS_PJNUM,
NVL(FUN_SNS_WINE_SCORE(A.GOODS_ID), 0) AS Fraction,
B.HEAD_IMG_URL,
B.RANK_FLAG,
B.FAMOUS_FLAG,
ROUND(C.TOTAL_GOODS_RANK / C.RANK_TIMES) AS AVGRANK,
'' AS CITY_PROMOTION_NAME,
'' AS GROUP_NAME,
(SELECT GOODS_SUB_CLASS
FROM M_GOODS_INFO GI
WHERE GI.GOODS_ID = A.GOODS_ID) AS WINE_TYPE_ID
FROM M_SNS_STATUS A
LEFT JOIN M_SNS_USER B
ON A.USER_ID = B.ID
LEFT JOIN M_SNS_GOODS_RANK C
ON A.GOODS_ID = C.GOODS_ID
WHERE A.GOODS_ID IS NOT NULL
AND B.ID NOT IN (47220)
ORDER BY A.POST_TIME DESC) a
where ROWNUM <= 4) b
WHERE num > 0 ;

具体执行计划和统计资料如下:

Execution Plan
----------------------------------------------------------
Plan hash value: 3789311727

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                       |     4 |  6452 |       | 16682   (1)| 00:03:21 |
|   1 |  SORT AGGREGATE                   |                       |     1 |     6 |       |            |          |
|*  2 |   INDEX UNIQUE SCAN               | PK_M_GOODS_INFO2      |     1 |     6 |       |     1   (0)| 00:00:01 |
|   3 |  TABLE ACCESS BY INDEX ROWID      | M_GOODS_INFO          |     1 |     9 |       |     2   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN               | PK_M_GOODS_INFO2      |     1 |       |       |     1   (0)| 00:00:01 |
|*  5 |  VIEW                             |                       |     4 |  6452 |       | 16682   (1)| 00:03:21 |
|*  6 |   COUNT STOPKEY                   |                       |       |       |       |            |          |
|   7 |    VIEW                           |                       | 15366 |    23M|       | 16682   (1)| 00:03:21 |
|*  8 |     SORT ORDER BY STOPKEY         |                       | 15366 |  4756K|  5136K| 16682   (1)| 00:03:21 |
|*  9 |      HASH JOIN                    |                       | 15366 |  4756K|       | 15634   (1)| 00:03:08 |
|* 10 |       TABLE ACCESS FULL           | M_SNS_USER            | 63904 |  1248K|       |   368   (3)| 00:00:05 |
|* 11 |       HASH JOIN RIGHT OUTER       |                       | 15366 |  4456K|       | 15264   (1)| 00:03:04 |
|  12 |        TABLE ACCESS FULL          | M_SNS_GOODS_RANK      |   119 |  1071 |       |     2   (0)| 00:00:01 |
|* 13 |        TABLE ACCESS BY INDEX ROWID| M_SNS_STATUS          | 15366 |  4321K|       | 15262   (1)| 00:03:04 |
|* 14 |         INDEX FULL SCAN           | IND_M_SNS_STATUS_IDX3 | 15366 |       |       |    36   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("MGI"."GOODS_ID"=:B1)
   4 - access("GI"."GOODS_ID"=:B1)
   5 - filter("NUM">0)
   6 - filter(ROWNUM<=4)
   8 - filter(ROWNUM<=4)
   9 - access("A"."USER_ID"="B"."ID")
  10 - filter("B"."ID"<>47220)
  11 - access("A"."GOODS_ID"="C"."GOODS_ID"(+))
  13 - filter("A"."USER_ID"<>47220)
  14 - filter("A"."GOODS_ID" IS NOT NULL)


Statistics
----------------------------------------------------------
      15388  recursive calls
          0  db block gets
     853019  consistent gets
         27  physical reads
        116  redo size
       4775  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          4  rows processed

从以上执行计划不难看出,SORT ORDER BY STOPKEY排序操作导致一连串的执行计划都发生了变化,有2张表走了全表扫描,强制索引虽然走了索引,但是效果很不理想,最终的结果就是逻辑读非常高,达到了85w之多,为什么会这样?根本原因还在于查询语句当中使用了排序,就是上面语句当中标注为绿色的部分,如何进行调整?以下是我调整后的语句:

SELECT b.*
FROM (SELECT a.*, ROWNUM num
FROM (SELECT /*+ index_desc( a M_SNS_STATUS_IDX7)*/
A.*,
B.NICK_NAME,
(SELECT COUNT(MGI.GOODS_ID)
FROM  M_GOODS_INFO MGI
WHERE a.GOODS_ID = MGI.GOODS_ID) AS GOODS_PJNUM,
NVL(FUN_SNS_WINE_SCORE(A.GOODS_ID), 0) AS Fraction,
B.HEAD_IMG_URL,
B.RANK_FLAG,
B.FAMOUS_FLAG,
ROUND(C.TOTAL_GOODS_RANK / C.RANK_TIMES) AS AVGRANK,
'' AS CITY_PROMOTION_NAME,
'' AS GROUP_NAME,
(SELECT GOODS_SUB_CLASS
FROM M_GOODS_INFO GI
WHERE GI.GOODS_ID = A.GOODS_ID) AS WINE_TYPE_ID
FROM M_SNS_STATUS A
LEFT JOIN M_SNS_USER B
ON A.USER_ID = B.ID
LEFT JOIN M_SNS_GOODS_RANK C
ON A.GOODS_ID = C.GOODS_ID
WHERE A.GOODS_ID IS NOT NULL
AND B.ID NOT IN (47220)
ORDER BY A.POST_TIME DESC) a
where ROWNUM <= 4) b
WHERE num > 0 ;

其中强制走索引排序,该索引正是sql语句中排序列上的索引,其执行计划如下:

Execution Plan
----------------------------------------------------------
Plan hash value: 1327187650

--------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                     |     4 |  6452 |   199   (0)| 00:00:03 |
|   1 |  SORT AGGREGATE                  |                     |     1 |     6 |            |          |
|*  2 |   INDEX UNIQUE SCAN              | PK_M_GOODS_INFO2    |     1 |     6 |     1   (0)| 00:00:01 |
|   3 |  TABLE ACCESS BY INDEX ROWID     | M_GOODS_INFO        |     1 |     9 |     2   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN              | PK_M_GOODS_INFO2    |     1 |       |     1   (0)| 00:00:01 |
|*  5 |  VIEW                            |                     |     4 |  6452 |   199   (0)| 00:00:03 |
|*  6 |   COUNT STOPKEY                  |                     |       |       |            |          |
|   7 |    VIEW                          |                     |     5 |  8000 |   199   (0)| 00:00:03 |
|   8 |     NESTED LOOPS                 |                     |     5 |  1585 |   199   (0)| 00:00:03 |
|   9 |      NESTED LOOPS OUTER          |                     |     6 |  1782 |   193   (0)| 00:00:03 |
|* 10 |       TABLE ACCESS BY INDEX ROWID| M_SNS_STATUS        | 15366 |  4321K|   192   (0)| 00:00:03 |
|  11 |        INDEX FULL SCAN DESCENDING| M_SNS_STATUS_IDX7   |   605K|       |     3   (0)| 00:00:01 |
|  12 |       TABLE ACCESS BY INDEX ROWID| M_SNS_GOODS_RANK    |     1 |     9 |     1   (0)| 00:00:01 |
|* 13 |        INDEX UNIQUE SCAN         | PK_M_SNS_GOODS_RANK |     1 |       |     0   (0)| 00:00:01 |
|  14 |      TABLE ACCESS BY INDEX ROWID | M_SNS_USER          |     1 |    20 |     1   (0)| 00:00:01 |
|* 15 |       INDEX UNIQUE SCAN          | PRI_M_SNS_USER_ID   |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("MGI"."GOODS_ID"=:B1)
   4 - access("GI"."GOODS_ID"=:B1)
   5 - filter("NUM">0)
   6 - filter(ROWNUM<=4)
  10 - filter("A"."GOODS_ID" IS NOT NULL AND "A"."USER_ID"<>47220)
  13 - access("A"."GOODS_ID"="C"."GOODS_ID"(+))
  15 - access("A"."USER_ID"="B"."ID")
       filter("B"."ID"<>47220)


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
       1594  consistent gets
          1  physical reads
          0  redo size
       4775  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

可以看出执行计划完全变了,逻辑读惊人的降到了1千多,实际执行时间不到1秒钟,可见使用错hint对sql语句的影响有多大。

记录一下~~

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25618347/viewspace-722055/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-31

  • 博文量
    88
  • 访问量
    317147