生產環境sql語句調優實戰第九篇

dbhelper發表於2014-11-26
SQL
生產環境中有一些sql語句是不定時炸彈，不聲不響的執行著，可能相關的表很大，執行時間達數小時甚至數天.
上週在生產環境中發現一條sql語句，執行時間幾乎是按照天來計算的。看到這種sql語句，就希望能夠馬上能夠找到立竿見影的效果，不過欲速則不達，需要做不少工作。一定要反覆驗證可行。
sql語句的執行情況如下:
SQL Execution ID : 16777217 
Execution Started : 10/18/2014 11:46:30 
First Refresh Time : 10/18/2014 11:46:34 
Last Refresh Time : 10/20/2014 00:00:01 
Duration : 130450s 
Module/Action : SQL*Plus/- 
Program : sqlplus@xxxxxx(TNS V1-V3) 
根據執行情況從18號就開始執行了，一直到20號凌晨還沒有輸出結果。根據統計資訊來看，這個sql語句執行需要持續了大概3天。
首先來看看這條sql語句。
SELECT 
            chg.cycle_seq_no, 
            pay.PAYMENT_CATEGORY, 
            acc.L9_COMPANY_CODE, 
            acc.L9_CONVERGENCE_CODE, 
            cc.REVENUE_CODE, 
            csb.L3_BILL_FORMAT, 
            csb.L9_CONS_BILL_IND, 
            chg.CHARGE_TYPE, 
            SYSDATE, 
            NULL, 
            NULL, 
            NULL, 
            NULL, 
            NULL, 
           sum( rel.AMOUNT), 
            sum(tax.TOTAL_TAX_AMT)
       FROM CHARGE chg,   --核心大表，資料量2億多
            CHARGE_CODE cc,  --資料量小，幾千條
            TAX tax,             --核心大表，資料量5千多萬
            CHARGE_REL rel,  --核心大表，資料量2億多
            ACCOUNT acc,   --資料量將近千萬
            BEN csb,  --資料量將近千萬
            PAY_CHANNEL pay,  --資料量將近千萬
            CYC_PAYER_POP,  ----資料量千萬
            CYCLE_CONTROL   --資料量很小，幾千條
      WHERE     CYC_PAYER_POP.PERIOD_KEY = rel.PERIOD_KEY 
            AND CYC_PAYER_POP.CUSTOMER_KEY = rel.CUSTOMER_KEY 
            AND CYC_PAYER_POP.BA_NO = rel.BA_NO 
            AND CYC_PAYER_POP.CYCLE_SEQ_NO = rel.CYCLE_SEQ_NO 
            AND CYC_PAYER_POP.CYCLE_SEQ_RUN = rel.CYCLE_SEQ_RUN 
            AND chg.PERIOD_KEY = rel.PERIOD_KEY 
            AND chg.CUSTOMER_KEY = rel.CUSTOMER_KEY 
            AND chg.CYCLE_SEQ_NO = rel.CYCLE_SEQ_NO 
            AND chg.CYCLE_SEQ_RUN = rel.CYCLE_SEQ_RUN 
            AND chg.CHARGE_SEQ_NO = rel.CHARGE_SEQ_NO 
            AND chg.CHARGE_CORRECTION_SEQ_NO = rel.CHARGE_CORRECTION_SEQ_NO 
            AND chg.CHARGE_CODE = cc.CHARGE_CODE 
            AND chg.BA_NO = rel.BA_NO 
            AND tax.PERIOD_KEY = rel.PERIOD_KEY 
            AND tax.CUSTOMER_KEY = rel.CUSTOMER_KEY 
            AND tax.CYCLE_SEQ_NO = rel.CYCLE_SEQ_NO 
            AND tax.CYCLE_SEQ_RUN = rel.CYCLE_SEQ_RUN 
            AND tax.BA_NO = rel.BA_NO 
            AND tax.TAX_SEQ_NO = rel.TAX_SEQ_NO 
            AND pay.BA_NO = CYC_PAYER_POP.BA_NO 
            AND csb.ben = CYC_PAYER_POP.BA_NO 
            AND acc.ban = csb.ban 
            AND CYCLE_CONTROL.CYCLE_SEQ_NO = 3325 
            AND CYC_PAYER_POP.PERIOD_KEY = CYCLE_CONTROL.PERIOD_KEY 
            AND CYC_PAYER_POP.CYCLE_SEQ_NO = CYCLE_CONTROL.CYCLE_SEQ_NO 
            AND CYC_PAYER_POP.CYCLE_SEQ_RUN = 
                   CYCLE_CONTROL.CYCLE_SEQ_RUN 
            AND CYC_PAYER_POP.REJECT_IND IS NULL 
            AND CYC_PAYER_POP.DB_STATUS IS NOT NULL 
   GROUP BY chg.cycle_seq_no, 
            pay.PAYMENT_CATEGORY, 
            acc.L9_COMPANY_CODE, 
            acc.L9_CONVERGENCE_CODE, 
            cc.REVENUE_CODE, 
            csb.L3_BILL_FORMAT, 
            csb.L9_CONS_BILL_IND, 
            chg.CHARGE_TYPE 

sql語句不復雜，根據之前的執行情況得知，輸出結果只有幾十條，但是牽扯的表都是很大的核心表。
如果輸出資料量幾百幾千條，但是執行時間較長，肯定是有效能瓶頸，可能在IO，可能在CPU資源上。
透過sql_monitor得到的報告中，可以看到很明顯的資料，這個IO Requests中傳送的IO請求道148M次，是將近1T的資料量。
Buffer Gets IO Requests Database Time Wait Activity

.

735M

.

148M

.

.

136587s

.

100% 




而且其中90%以上的IO都在 CHARGE_REL表上。實際上這個表只有幾十個G。所以很明視訊記憶體在sql語句的問題。
Id Operation Name Estimated
rows 		Cost Execs Rows Memory IO Requests CPU Activity Wait Activity Progress
  0 INSERT STATEMENT       1              
  1  LOAD TABLE CONVENTIONAL       1              
-> 2  HASH GROUP BY   1 51018 1 0 13MB          
-> 3  NESTED LOOPS       1 1M            
-> 4  NESTED LOOPS   1 51017 1 1M            
-> 5  NESTED LOOPS   1 51016 1 1M            
-> 6  NESTED LOOPS   1 51015 1 1M            
-> 7  NESTED LOOPS   1 51014 1 1M            
-> 8  NESTED LOOPS   1 51013 1 1M            
-> 9  NESTED LOOPS   1 51012 1 1M            
-> 10  NESTED LOOPS   1 51011 1 1M            
  11  HASH JOIN   3326 45355 1 137K 4310KB     5.00%      
  12  TABLE ACCESS BY INDEX ROWID CYCLE_CONTROL 1 1 1 1            
   
  13 INDEX UNIQUE SCAN CYCLE_CONTROL_PK 1 1 1 1            
   
-> 14 PARTITION RANGE ALL   120K 45353 1 137K            
  15 TABLE ACCESS FULL CYC_PAYER_POP 120K 45353 171 137K   170K (1%)   5.00% 26.00%  
94511s
-> 16 PARTITION RANGE ITERATOR   1 2 137K 1M            
  17  TABLE ACCESS BY LOCAL INDEX ROWID CHARGE_REL 1 2 137K 1M     5M (37%)        
-> 18 INDEX RANGE SCAN CHARGE_REL_3IX 45 1 137K 9M     139M (93%)        
-> 19 PARTITION RANGE ITERATOR   1 1 1M 1M            
  20  TABLE ACCESS BY LOCAL INDEX ROWID CHARGE 1 1 1M 1M   163K (1%)   1.00% 24.00%    
  21  INDEX UNIQUE SCAN CHARGE_PK 1 1 1M 1M   402K (3%)   3.00% 29.00%    
  22 PARTITION RANGE ITERATOR   1 1 1M 1M       5.00%      
  23  TABLE ACCESS BY LOCAL INDEX ROWID BL1_TAX 1 1 1M 1M   30328 (<01%)   1.00% 9.00%    
  24  INDEX UNIQUE SCAN TAX_PK 1 1 1M 1M   263K (2%)   25.00% 22.00%    
  25 MAT_VIEW ACCESS BY INDEX ROWID CHARGE_CODE 1 1 1M 1M   581 (<01%)   1.00%      
-> 26  INDEX UNIQUE SCAN CHARGE_CODE_PK 1 1 1M 1M   1 (<01%)        
  27  TABLE ACCESS BY INDEX ROWID PAY_CHANNEL 1 1 1M 1M   2M (11%)          
  28  INDEX RANGE SCAN PAY_CHANNEL_1IX 1 1 1M 1M   120K (<01%)     4.00%    
  29  TABLE ACCESS BY INDEX ROWID BEN 1 1 1M 1M   230K (2%)   2.00% 18.00%    
  30  INDEX UNIQUE SCAN BEN_PK 1 1 1M 1M   104K (<01%)     2.00%    
  31  INDEX UNIQUE SCAN ACCOUNT_PK 1 1 1M 1M   73993 (<01%)   5.00% 3.00%    
  32  TABLE ACCESS BY INDEX ROWID ACCOUNT 1 1 1M 1M   744K (5%)   89.00% 71.00%    

所以要調優這條sql語句，還得結合業務入手。要不盲目加hint，加並行不是長久之計。
按照之前的執行計劃，是先訪問CYC_PAYER_POP,CYCLE_CONTROL ，然後直接關聯核心大表CHARGE_REL。CHARGE_REL再關聯幾個千萬的表。然後關聯核心大表CHARGE和TAX
按照資料的分佈和輸出結果，得到的資料結果流如下.
CYC_PAYER_POP,CYCLE_CONTROL --&gt得到50萬左右的資料--&gt和CHARGE_REL關聯--&gt得到500萬左右的資料--&gt和幾個千萬大表關聯--&gt還是輸出500萬左右的資料--&gt然後關聯核心大表CHARGE和TAX--&gt結果集還是500多萬
--&gt最後做GROUP BY彙總資料--&gt40~100條資料。

資料的過濾是越過濾越少比較合理，按照上面的流程是越過濾越多，而且後面的幾個大表關聯，得到的結果集都是500萬左右。還是存在一定的問題。
首先CYC_PAYER_POP,CYCLE_CONTROL --&gt得到50萬左右的資料這個流程沒有問題，這個結果集提供了基礎的條件，在和大表關聯的時候能從上億條記錄中過濾得到500多萬的記錄。
但是從業務角度來說。如果是這樣的流程
CYC_PAYER_POP,CYCLE_CONTROL --&gt得到50萬左右的資料--&gt和幾個千萬大表關聯--&gt輸出結果還是50萬
這樣就遮蔽了很多大表大表之間的冗餘關聯。

至於和CHARGE_REL關聯--&gt得到500萬左右的資料--&gt然後關聯核心大表CHARGE和TAX--&gt結果集還是500多萬
這個實在是沒有辦法了，因為確實需要這些明細的資料作為彙總，可以先放放。

我先列出以下幾個核心大表的索引情況。
INDEX_NAME                     TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST                    TABLE_TYPE STATUS   NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
CHARGE_REL_1IX                    NORMAL     NONUNIQUE YES INVOICE_SEQ_NO                 TABLE      N/A     297048430 23-OCT-14 N
CHARGE_REL_2IX                    NORMAL     NONUNIQUE YES STATEMENT_SEQ_NO               TABLE      N/A     187957804 23-OCT-14 N
CHARGE_REL_3IX                    NORMAL     NONUNIQUE YES BA_NO                          TABLE      N/A     297048430 23-OCT-14 N
CHARGE_REL_4IX                    NORMAL     NONUNIQUE YES TAX_SEQ_NO                     TABLE      N/A     297048424 23-OCT-14 N
CHARGE_REL_PK                     NORMAL     UNIQUE    YES CHARGE_SEQ_NO,CHARGE_CORRECTIO TABLE      N/A     297048430 23-OCT-14 N
                                                                   N_SEQ_NO,PERIOD_KEY,CUSTOMER_KEY

INDEX_NAME                     TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST                    TABLE_TYPE STATUS   NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
CHARGE_1IX                            NORMAL     NONUNIQUE YES RECEIVER_CUSTOMER,SERVICE_RECE TABLE      N/A     297189081 23-OCT-14 N
                                                                   IVER_TYPE,EFFECTIVE_DATE
CHARGE_2IX                            NORMAL     NONUNIQUE YES BA_NO                          TABLE      N/A     297189081 23-OCT-14 N
CHARGE_3IX                            NORMAL     NONUNIQUE YES SERVICE_RECEIVER_ID,SERVICE_RE TABLE      N/A     297189081 23-OCT-14 N
                                                                   CEIVER_TYPE
CHARGE_PK                             NORMAL     UNIQUE    YES CHARGE_SEQ_NO,CHARGE_CORRECTIO TABLE      N/A     297189081 23-OCT-14 N
                                                               N_SEQ_NO,PERIOD_KEY,CUSTOMER_KEY
    
INDEX_NAME                     TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST                    TABLE_TYPE STATUS   NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
TAX_1IX                               NORMAL     NONUNIQUE YES BA_NO                              TABLE      N/A     297148329 23-OCT-14 N
TAX_PK                                NORMAL     UNIQUE    YES TAX_SEQ_NO,PERIOD_KEY,CUSTOMER_KEY TABLE      N/A     297148329 23-OCT-14 N
    

我專門列出索引的資訊是想說明，其實在sql語句中有一些過濾條件是不必要的。如果我們已經透過主鍵做了匹配，再加上額外的過濾條件就顯得有些多餘了。
下面標黃的部分是我需要刪除的額外條件，因為已經透過主鍵關聯到了。再加上額外的條件，要麼是索引發生變化要不就是資料過濾的效能受到影響。
就跟我要查詢一個人的身份證號。其實根據身份證號就能夠得到身份證的發證身份，我就不需要再額外限定是某個某個省的。

WHERE     CYC_PAYER_POP.PERIOD_KEY = rel.PERIOD_KEY 
            AND CYC_PAYER_POP.CUSTOMER_KEY = rel.CUSTOMER_KEY 
            AND CYC_PAYER_POP.BA_NO = rel.BA_NO 
            AND CYC_PAYER_POP.CYCLE_SEQ_NO = rel.CYCLE_SEQ_NO 
            AND CYC_PAYER_POP.CYCLE_SEQ_RUN = rel.CYCLE_SEQ_RUN 
            AND chg.PERIOD_KEY = rel.PERIOD_KEY 
            AND chg.CUSTOMER_KEY = rel.CUSTOMER_KEY 
          --  AND chg.CYCLE_SEQ_NO = rel.CYCLE_SEQ_NO 
          --  AND chg.CYCLE_SEQ_RUN = rel.CYCLE_SEQ_RUN 
            AND chg.CHARGE_SEQ_NO = rel.CHARGE_SEQ_NO 
            AND chg.CHARGE_CORRECTION_SEQ_NO = rel.CHARGE_CORRECTION_SEQ_NO 
            AND chg.CHARGE_CODE = cc.CHARGE_CODE 
           -- AND chg.BA_NO = rel.BA_NO 
            AND tax.PERIOD_KEY = rel.PERIOD_KEY 
            AND tax.CUSTOMER_KEY = rel.CUSTOMER_KEY 
          --  AND tax.CYCLE_SEQ_NO = rel.CYCLE_SEQ_NO 
          --  AND tax.CYCLE_SEQ_RUN = rel.CYCLE_SEQ_RUN 
         --   AND tax.BA_NO = rel.BA_NO 
            AND tax.TAX_SEQ_NO = rel.TAX_SEQ_NO

做了上面的兩個改動，效能的瓶頸問題就基本消除了，剩下的就是額外的最佳化了。
一個是穩定執行計劃，使得每次訪問都需要按照指定的順序來過濾資料。
一個是提高處理速度，可以考慮加入並行。可以使用下面的hint來實現。
/*+ leading(CYCLE_CONTROL CYC_PAYER_POP pay csb csm_acc ) parallel(CYC_PAYER_POP 8) full(CYC_PAYER_POP)   */


改動後的執行計劃如下所示。
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                        |     1 |   174 |  8466   (1)| 00:01:42 |       |       |        |      |            |
|   1 |  PX COORDINATOR                              |                        |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)                        | :TQ10002               |     1 |   174 |  8466   (1)| 00:01:42 |       |       |  Q1,02 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY                             |                        |     1 |   174 |  8466   (1)| 00:01:42 |       |       |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                               |                        |       |       |            |          |       |       |  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH                            | :TQ10001               |       |       |            |          |       |       |  Q1,01 | P->P | HASH       |
|   6 |       NESTED LOOPS                           |                        |       |       |            |          |       |       |  Q1,01 | PCWP |            |
|   7 |        NESTED LOOPS                          |                        |     1 |   174 |  8466   (1)| 00:01:42 |       |       |  Q1,01 | PCWP |            |
|   8 |         NESTED LOOPS                         |                        |     1 |   158 |  8465   (1)| 00:01:42 |       |       |  Q1,01 | PCWP |            |
|   9 |          NESTED LOOPS                        |                        |     1 |   144 |  8465   (1)| 00:01:42 |       |       |  Q1,01 | PCWP |            |
|  10 |           NESTED LOOPS                       |                        |     1 |   109 |  8465   (1)| 00:01:42 |       |       |  Q1,01 | PCWP |            |
|  11 |            NESTED LOOPS                      |                        |  4277 |   296K|  7455   (1)| 00:01:30 |       |       |  Q1,01 | PCWP |            |
|  12 |             NESTED LOOPS                     |                        |  4277 |   250K|  7396   (1)| 00:01:29 |       |       |  Q1,01 | PCWP |            |
|  13 |              NESTED LOOPS                    |                        |  4277 |   183K|  7336   (1)| 00:01:29 |       |       |  Q1,01 | PCWP |            |
|* 14 |               HASH JOIN                      |                        |  4277 |   137K|  7277   (1)| 00:01:28 |       |       |  Q1,01 | PCWP |            |
|  15 |                BUFFER SORT                   |                        |       |       |            |          |       |       |  Q1,01 | PCWC |            |
|  16 |                 PX RECEIVE                   |                        |     1 |    10 |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|  17 |                  PX SEND BROADCAST           | :TQ10000               |     1 |    10 |     1   (0)| 00:00:01 |       |       |        | S->P | BROADCAST  |
|  18 |                   TABLE ACCESS BY INDEX ROWID|     CYCLE_CONTROL      |     1 |    10 |     1   (0)| 00:00:01 |       |       |        |      |            |
|* 19 |                    INDEX UNIQUE SCAN         |     CYCLE_CONTROL_PK   |     1 |       |     1   (0)| 00:00:01 |       |       |        |      |            |
|  20 |                PX BLOCK ITERATOR             |                        |   153K|  3457K|  7275   (1)| 00:01:28 |     1 |   181 |  Q1,01 | PCWC |            |
|* 21 |                 TABLE ACCESS FULL            |     CYC_PAYER_POP      |   153K|  3457K|  7275   (1)| 00:01:28 |     1 |   181 |  Q1,01 | PCWP |            |
|  22 |               TABLE ACCESS BY INDEX ROWID    |     PAY_CHANNEL        |     1 |    11 |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|* 23 |                INDEX RANGE SCAN              |     PAY_CHANNEL_1IX    |     1 |       |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|  24 |              TABLE ACCESS BY INDEX ROWID     |     BEN                |     1 |    16 |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|* 25 |               INDEX UNIQUE SCAN              |     BEN_PK             |     1 |       |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|  26 |             TABLE ACCESS BY INDEX ROWID      |     ACCOUNT            |     1 |    11 |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|* 27 |              INDEX UNIQUE SCAN               |     ACCOUNT_PK         |     1 |       |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|  28 |            PARTITION RANGE ITERATOR          |                        |     1 |    38 |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,01 | PCWP |            |
|* 29 |             TABLE ACCESS BY LOCAL INDEX ROWID|         CHARGE_REL     |     1 |    38 |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,01 | PCWP |            |
|* 30 |              INDEX RANGE SCAN                |         CHARGE_REL_3IX |    46 |       |     1   (0)| 00:00:01 |   KEY |   KEY |  Q1,01 | PCWP |            |
|  31 |           PARTITION RANGE ITERATOR           |                        |     1 |    35 |     1   (0)| 00:00:01 |   KEY |   KEY |  Q1,01 | PCWP |            |
|  32 |            TABLE ACCESS BY LOCAL INDEX ROWID |     CHARGE             |     1 |    35 |     1   (0)| 00:00:01 |   KEY |   KEY |  Q1,01 | PCWP |            |
|* 33 |             INDEX UNIQUE SCAN                |     CHARGE_PK          |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |  Q1,01 | PCWP |            |
|  34 |          MAT_VIEW ACCESS BY INDEX ROWID      |     CHARGE_CODE        |     1 |    14 |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|* 35 |           INDEX UNIQUE SCAN                  |     CHARGE_CODE_PK     |     1 |       |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|  36 |         PARTITION RANGE ITERATOR             |                        |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |  Q1,01 | PCWP |            |
|* 37 |          INDEX UNIQUE SCAN                   |     TAX_PK             |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |  Q1,01 | PCWP |            |
|  38 |        TABLE ACCESS BY LOCAL INDEX ROWID     |     TAX                |     1 |    16 |     1   (0)| 00:00:01 |     1 |     1 |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 看起來還不錯的樣子。
最後在備份庫上的執行時間在半個小時左右，生產環境略微更快一些。
40 rows selected.
Elapsed: 00:30:27.10

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

相關文章