透過使用hint unnest調優sql語句

dbhelper發表於2015-02-24
生產環境中有一條sql語句透過sql_monitor看到執行的時間實在是太驚人了,竟然達到了13個小時,而且還沒有執行完。

Session APPC (20015:7013)
SQL ID 74pzzzjddkyd4
SQL Execution ID 16777242
Execution Started 2/2/2015 10:52
First Refresh Time 2/2/2015 10:52
Last Refresh Time 2/3/2015 0:05
Duration 47669s
Module/Action bfi@ccbdbpr1 (TNS V1-V3)/-
Service XXXXX
Program bfi@xxx (TNS V1-V3)
sql語句如下:
SELECT NVL(SUM(CUST.WEIGHT), 0) TOTAL_WEIGHT
  FROM BL1_CUSTOMER CUST, BL1_CYCLE_CUSTOMERS CYC_CUST
 WHERE CYC_CUST.PERIOD_KEY = :periodKey
   AND CYC_CUST.CYCLE_SEQ_NO = :cycleSeqNo
   AND CYC_CUST.CUSTOMER_NO = CUST.CUSTOMER_ID
   AND (CYC_CUST.UNDO_REQ_TYPE = 'N' OR CYC_CUST.UNDO_REQ_TYPE IS NULL)
   AND EXISTS
 (SELECT 1
          FROM BL1_CYC_PAYER_POP PAYER, BL1_DOCUMENT DOC
         WHERE PAYER.PERIOD_KEY = :periodKey
           AND PAYER.CYCLE_SEQ_NO = :cycleSeqNo
           AND PAYER.CYCLE_SEQ_RUN = :cycleSeqRun
           AND PAYER.CUSTOMER_NO = CYC_CUST.CUSTOMER_NO
           AND PAYER.DB_STATUS = 'BL'
           AND (PAYER.UNDO_REQ_TYPE = 'N' OR PAYER.UNDO_REQ_TYPE IS NULL)
           AND PAYER.FORMAT_EXT_DATE IS NULL
           AND DOC.PERIOD_KEY = :periodKey
           AND DOC.CYCLE_SEQ_NO = :cycleSeqNo
           AND DOC.CYCLE_SEQ_RUN = :cycleSeqRun
           AND PAYER.BA_NO = DOC.BA_NO
           AND doc.DOC_PRODUCE_IND IN ('Y', 'E'))

檢視執行計劃沒有發現很嚴重的資源消耗。但是實際的執行情況怎麼和執行計劃相差甚遠。預計8分鐘,實際上十多個小時還沒有執行完。
Plan hash value: 3506320481
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                       |     1 |    24 | 42048   (1)| 00:08:25 |       |       |
|   1 |  SORT AGGREGATE                        |                       |     1 |    24 |            |          |       |       |
|*  2 |   FILTER                               |                       |       |       |            |          |       |       |
|   3 |    NESTED LOOPS                        |                       |       |       |            |          |       |       |
|   4 |     NESTED LOOPS                       |                       |     1 |    24 | 42046   (1)| 00:08:25 |       |       |
|   5 |      PARTITION RANGE SINGLE            |                       |     1 |    15 | 42045   (1)| 00:08:25 |   171 |   171 |
|*  6 |       TABLE ACCESS FULL                | BL1_CYCLE_CUSTOMERS   |     1 |    15 | 42045   (1)| 00:08:25 |   171 |   171 |
|*  7 |      INDEX UNIQUE SCAN                 | BL1_CUSTOMER_PK       |     1 |       |     1   (0)| 00:00:01 |       |       |
|   8 |     TABLE ACCESS BY INDEX ROWID        | BL1_CUSTOMER          |     1 |     9 |     1   (0)| 00:00:01 |       |       |
|   9 |    NESTED LOOPS                        |                       |       |       |            |          |       |       |
|  10 |     NESTED LOOPS                       |                       |     1 |    52 |     2   (0)| 00:00:01 |       |       |
|  11 |      PARTITION RANGE SINGLE            |                       |     1 |    34 |     1   (0)| 00:00:01 |   171 |   171 |
|* 12 |       TABLE ACCESS BY LOCAL INDEX ROWID| BL1_CYC_PAYER_POP     |     1 |    34 |     1   (0)| 00:00:01 |   171 |   171 |
|* 13 |        INDEX RANGE SCAN                | BL1_CYC_PAYER_POP_1IX |     3 |       |     1   (0)| 00:00:01 |   171 |   171 |
|  14 |      PARTITION RANGE SINGLE            |                       |     7 |       |     1   (0)| 00:00:01 |   171 |   171 |
|* 15 |       INDEX RANGE SCAN                 | BL1_DOCUMENT_1IX      |     7 |       |     1   (0)| 00:00:01 |   171 |   171 |
|* 16 |     TABLE ACCESS BY LOCAL INDEX ROWID  | BL1_DOCUMENT          |     1 |    18 |     1   (0)| 00:00:01 |   171 |   171 |
--------------------------------------------------------------------------------------------------------------------------------

這個時候可以透過sql monitor得到一個相對比較準確的資源使用情況。
Buffer Gets IO Requests Database Time Wait Activity
.
96M
.
21M
.
48518s
.
100%

一看IO請求達21M次,約等於160.9G左右的資料量。
從sql語句的執行計劃可以看出,語句可以分為兩大部分,一部分是exist字句上面的部分,兩個大表做了關聯,得到了相關的customer_no然後在exists字句中繼續關聯。
大量的IO請求都消耗在BL1_CUSTOMER,其實這個表實際上資料量近千萬,還沒有80G多G,但是傳送的IO請求累計的資料量卻已經超過了80G,佔到了整個IO請求數的一半以上。消耗的CPU資源也在73%以上
Id Operation Name Estimated Cost Execs Rows IO Requests CPU Activity
Rows
. 0 SELECT STATEMENT . . . 1 .     .
-> 1 . SORT AGGREGATE . 1 . 1 0     .
   
-> 2 .. FILTER . . . 1 403K     .
   
-> 3 ... NESTED LOOPS . . . 1 562K     .
   
-> 4 .... NESTED LOOPS . 1682 45038 1 562K     .
   
-> 5 ..... PARTITION RANGE ITERATOR . 1682 44869 1 562K     .
   
. 6 ...... TABLE ACCESS FULL BL1_CYCLE_CUSTOMERS 1682 44869 1 562K 7736 (<0.1%)      
       
. 7 ..... INDEX UNIQUE SCAN BL1_CUSTOMER_PK 1 1 949K 562K
.
673K (3.3%) ###  
   
-> 8 .... TABLE ACCESS BY INDEX ROWID BL1_CUSTOMER 1 1 990K 562K
.
11M (52%)
.
73%
-> 9 ... NESTED LOOPS . . . 562K 403K     .
   
-> 10 .... NESTED LOOPS . 1 74 562K 6M     .
   
-> 11 ..... PARTITION RANGE ITERATOR . 1 37 562K 497K     .
   
. 12 ...... TABLE ACCESS BY LOCAL INDEX ROWID BL1_CYC_PAYER_POP 1 37 562K 497K
.
774K (3.8%)
.
0.99%
   
. 13 ....... INDEX RANGE SCAN BL1_CYC_PAYER_POP_1IX 3 36 562K 4M
.
864K (4.2%)
.
1.90%
-> 14 ..... PARTITION RANGE ITERATOR . 14 36 497K 6M     .
   
. 15 ...... INDEX RANGE SCAN BL1_DOCUMENT_1IX 14 36 497K 6M
.
4M (20%)
.
21%
. 16 .... TABLE ACCESS BY LOCAL INDEX ROWID BL1_DOCUMENT 1 37 6M 403K
.
3M (15%)
.
1.20%

可以透過禁用子查詢解巢狀來做為一種調優思路,優先從子查詢中先輸出資料來。
而BL1_CYCLE_PAYER_POP表作為一個重要的關聯表。子查詢中的條件AND PAYER.CUSTOMER_NO = CYC_CUST.CUSTOMER_NO和外部查詢相關聯。
可以優先查詢這個表,考慮到執行的頻率和效能,新增了並行hint。
這樣sql語句就變為
SELECT NVL(SUM(CUST.WEIGHT), 0) TOTAL_WEIGHT
  FROM BL1_CUSTOMER CUST, BL1_CYCLE_CUSTOMERS CYC_CUST
 WHERE CYC_CUST.PERIOD_KEY = :periodKey
   AND CYC_CUST.CYCLE_SEQ_NO = :cycleSeqNo
   AND CYC_CUST.CUSTOMER_NO = CUST.CUSTOMER_ID
   AND (CYC_CUST.UNDO_REQ_TYPE = 'N' OR CYC_CUST.UNDO_REQ_TYPE IS NULL)
   AND EXISTS
 (SELECT /*+unnest full(payer) parallel(payer 4)*/1
          FROM BL1_CYC_PAYER_POP PAYER, BL1_DOCUMENT DOC
         WHERE PAYER.PERIOD_KEY = :periodKey
           AND PAYER.CYCLE_SEQ_NO = :cycleSeqNo
           AND PAYER.CYCLE_SEQ_RUN = :cycleSeqRun
           AND PAYER.CUSTOMER_NO = CYC_CUST.CUSTOMER_NO
           AND PAYER.DB_STATUS = 'BL'
           AND (PAYER.UNDO_REQ_TYPE = 'N' OR PAYER.UNDO_REQ_TYPE IS NULL)
           AND PAYER.FORMAT_EXT_DATE IS NULL
           AND DOC.PERIOD_KEY = :periodKey
           AND DOC.CYCLE_SEQ_NO = :cycleSeqNo
           AND DOC.CYCLE_SEQ_RUN = :cycleSeqRun
           AND PAYER.BA_NO = DOC.BA_NO
           AND doc.DOC_PRODUCE_IND IN ('Y', 'E'))
最佳化後的執行計劃如下:
Plan hash value: 227985194
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                        |     1 |    37 | 13688   (1)| 00:02:45 |       |       |        |      |            |
|   1 |  SORT AGGREGATE                                |                        |     1 |    37 |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR                               |                        |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)                         | :TQ10001               |     1 |    37 |            |          |       |       |  Q1,01 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE                             |                        |     1 |    37 |            |          |       |       |  Q1,01 | PCWP |            |
|   5 |      NESTED LOOPS                              |                        |       |       |            |          |       |       |  Q1,01 | PCWP |            |
|   6 |       NESTED LOOPS                             |                        |     1 |    37 | 13688   (1)| 00:02:45 |       |       |  Q1,01 | PCWP |            |
|   7 |        NESTED LOOPS                            |                        |     1 |    28 | 13688   (1)| 00:02:45 |       |       |  Q1,01 | PCWP |            |
|   8 |         VIEW                                   | VW_SQ_1                |     1 |    13 | 13686   (1)| 00:02:45 |       |       |  Q1,01 | PCWP |            |
|   9 |          HASH UNIQUE                           |                        |     1 |    52 |            |          |       |       |  Q1,01 | PCWP |            |
|  10 |           PX RECEIVE                           |                        |     1 |    52 |            |          |       |       |  Q1,01 | PCWP |            |
|  11 |            PX SEND HASH                        | :TQ10000               |     1 |    52 |            |          |       |       |  Q1,00 | P->P | HASH       |
|  12 |             HASH UNIQUE                        |                        |     1 |    52 |            |          |       |       |  Q1,00 | PCWP |            |
|  13 |              NESTED LOOPS                      |                        |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|  14 |               NESTED LOOPS                     |                        |     1 |    52 | 13686   (1)| 00:02:45 |       |       |  Q1,00 | PCWP |            |
|  15 |                PX BLOCK ITERATOR               |                        |     1 |    34 | 13686   (1)| 00:02:45 |   171 |   171 |  Q1,00 | PCWC |            |
|* 16 |                 TABLE ACCESS FULL              | BL1_CYC_PAYER_POP      |     1 |    34 | 13686   (1)| 00:02:45 |   171 |   171 |  Q1,00 | PCWP |            |
|  17 |                PARTITION RANGE SINGLE          |                        |     7 |       |     1   (0)| 00:00:01 |   171 |   171 |  Q1,00 | PCWP |            |
|* 18 |                 INDEX RANGE SCAN               | BL1_DOCUMENT_1IX       |     7 |       |     1   (0)| 00:00:01 |   171 |   171 |  Q1,00 | PCWP |            |
|* 19 |               TABLE ACCESS BY LOCAL INDEX ROWID| BL1_DOCUMENT           |     1 |    18 |     1   (0)| 00:00:01 |   171 |   171 |  Q1,00 | PCWP |            |
|  20 |         PARTITION RANGE SINGLE                 |                        |     1 |    15 |     1   (0)| 00:00:01 |   171 |   171 |  Q1,01 | PCWP |            |
|* 21 |          TABLE ACCESS BY LOCAL INDEX ROWID     | BL1_CYCLE_CUSTOMERS    |     1 |    15 |     1   (0)| 00:00:01 |   171 |   171 |  Q1,01 | PCWP |            |
|* 22 |           INDEX RANGE SCAN                     | BL1_CYCLE_CUSTOMERS_PK |     1 |       |     1   (0)| 00:00:01 |   171 |   171 |  Q1,01 | PCWP |            |
|* 23 |        INDEX UNIQUE SCAN                       | BL1_CUSTOMER_PK        |     1 |       |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|  24 |       TABLE ACCESS BY INDEX ROWID              | BL1_CUSTOMER           |     1 |     9 |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  16 - filter("PAYER"."FORMAT_EXT_DATE" IS NULL AND "PAYER"."CYCLE_SEQ_NO"=4105 AND "PAYER"."PERIOD_KEY"=61 AND ("PAYER"."UNDO_REQ_TYPE"='N' OR
              "PAYER"."UNDO_REQ_TYPE" IS NULL) AND "PAYER"."DB_STATUS"='BL' AND "PAYER"."CYCLE_SEQ_RUN"=0)
  18 - access("PAYER"."BA_NO"="DOC"."BA_NO")
  19 - filter("DOC"."CYCLE_SEQ_NO"=4105 AND "DOC"."PERIOD_KEY"=61 AND "DOC"."CYCLE_SEQ_RUN"=0 AND ("DOC"."DOC_PRODUCE_IND"='E' OR
              "DOC"."DOC_PRODUCE_IND"='Y'))
  21 - filter("CYC_CUST"."UNDO_REQ_TYPE"='N' OR "CYC_CUST"."UNDO_REQ_TYPE" IS NULL)
  22 - access("ITEM_0"="CYC_CUST"."CUSTOMER_NO" AND "CYC_CUST"."CYCLE_SEQ_NO"=4105 AND "CYC_CUST"."PERIOD_KEY"=61)
  23 - access("CYC_CUST"."CUSTOMER_NO"="CUST"."CUSTOMER_ID")


最後得到的反饋是,原本執行近20個小時的查詢,在新增這個Hint之後,執行時間縮短到了1個小時以內。效能的提升還是相當的可觀的。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8494287/viewspace-1440457/,如需轉載,請註明出處,否則將追究法律責任。

相關文章