sql調優一例---索引排序hint

skuary發表於2012-04-23

剛剛酒窖開發人員發了封郵件給我,說酒窖測試庫現在的一個查詢出奇的慢,一直在執行狀態,讓我這裡給看看,具體的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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章