生產環境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的資料量。
而且其中90%以上的IO都在 CHARGE_REL表上。實際上這個表只有幾十個G。所以很明視訊記憶體在sql語句的問題。
所以要調優這條sql語句，還得結合業務入手。要不盲目加hint，加並行不是長久之計。
按照之前的執行計劃，是先訪問CYC_PAYER_POP,CYCLE_CONTROL ，然後直接關聯核心大表CHARGE_REL。CHARGE_REL再關聯幾個千萬的表。然後關聯核心大表CHARGE和TAX
按照資料的分佈和輸出結果，得到的資料結果流如下.
CYC_PAYER_POP,CYCLE_CONTROL -->得到50萬左右的資料-->和CHARGE_REL關聯-->得到500萬左右的資料-->和幾個千萬大表關聯-->還是輸出500萬左右的資料-->然後關聯核心大表CHARGE和TAX-->結果集還是500多萬
-->最後做GROUP BY彙總資料-->40~100條資料。
資料的過濾是越過濾越少比較合理，按照上面的流程是越過濾越多，而且後面的幾個大表關聯，得到的結果集都是500萬左右。還是存在一定的問題。
首先CYC_PAYER_POP,CYCLE_CONTROL -->得到50萬左右的資料這個流程沒有問題，這個結果集提供了基礎的條件，在和大表關聯的時候能從上億條記錄中過濾得到500多萬的記錄。
但是從業務角度來說。如果是這樣的流程
CYC_PAYER_POP,CYCLE_CONTROL -->得到50萬左右的資料-->和幾個千萬大表關聯-->輸出結果還是50萬
這樣就遮蔽了很多大表大表之間的冗餘關聯。
至於和CHARGE_REL關聯-->得到500萬左右的資料-->然後關聯核心大表CHARGE和TAX-->結果集還是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
上週在生產環境中發現一條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 | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
|
|
|
而且其中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 -->得到50萬左右的資料-->和CHARGE_REL關聯-->得到500萬左右的資料-->和幾個千萬大表關聯-->還是輸出500萬左右的資料-->然後關聯核心大表CHARGE和TAX-->結果集還是500多萬
-->最後做GROUP BY彙總資料-->40~100條資料。
資料的過濾是越過濾越少比較合理，按照上面的流程是越過濾越多，而且後面的幾個大表關聯，得到的結果集都是500萬左右。還是存在一定的問題。
首先CYC_PAYER_POP,CYCLE_CONTROL -->得到50萬左右的資料這個流程沒有問題，這個結果集提供了基礎的條件，在和大表關聯的時候能從上億條記錄中過濾得到500多萬的記錄。
但是從業務角度來說。如果是這樣的流程
CYC_PAYER_POP,CYCLE_CONTROL -->得到50萬左右的資料-->和幾個千萬大表關聯-->輸出結果還是50萬
這樣就遮蔽了很多大表大表之間的冗餘關聯。
至於和CHARGE_REL關聯-->得到500萬左右的資料-->然後關聯核心大表CHARGE和TAX-->結果集還是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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 生產環境sql語句調優實戰第二篇SQL
- 生產環境sql語句調優實戰第三篇SQL
- 生產環境sql語句調優實戰第四篇SQL
- 生產環境sql語句調優實戰第五篇SQL
- 生產環境sql語句調優實戰第六篇SQL
- 生產環境sql語句調優實戰第八篇SQL
- 生產環境sql語句調優實戰第七篇SQL
- 生產環境sql語句調優實戰第十篇SQL
- 生產環境大型sql語句調優實戰第一篇（一）SQL
- 生產環境大型sql語句調優實戰第一篇（二）SQL
- kafka生產環境規劃-kafka 商業環境實戰Kafka
- 使用hint來調優sql語句SQL
- 實戰生產環境vCenter HA配置（VCSA6.5）
- kafka生產者Producer引數設定及引數調優建議-kafka 商業環境實戰Kafka
- 生產系統pl/sql調優案例SQL
- Eureka：生產環境優化總結。優化
- MySQL調優篇 | SQL調優實戰（5）MySql
- SQL 語句調優_減少或者避免笛卡爾乘積的發生SQL
- oracle 對比sql語句執行環境OracleSQL
- 生產sql調優之統計資訊分析SQL
- 透過使用hint unnest調優sql語句SQL
- 通過使用hint unnest調優sql語句SQL
- 生產SQL語句突然變慢問題定位SQL
- 一條sql語句的建議調優分析SQL
- redmine生產環境搭建
- Django生產環境搭建Django
- SQL語句優化SQL優化
- 使用USE_HASH Hint調優一個SQL語句SQL
- 使用SQL調整顧問進行語句優化SQL優化
- HBase 核心元件協調及RegionServer JVM引數調優-OLAP商業環境實戰元件ServerJVM
- Vue 移動端專案生產環境的優化Vue優化
- MYSQL SQL語句優化MySql優化
- sql語句效能優化SQL優化
- SQL語句的優化SQL優化
- 求助：SQL語句優化SQL優化
- Java生產環境效能監控與調優—基於JDK命令列工具的監控JavaJDK命令列
- SQL Server優化之SQL語句優化SQLServer優化
- soar-PHP - SQL 語句優化器和重寫器的 PHP 擴充套件包、 方便框架中 SQL 語句調優PHPSQL優化套件框架