執行計劃的偏差導致的效能問題
在生產環境中有一條sql語句，檢視執行計劃來看，效果還是可以接受的。
sql語句類似下面的樣子，可以看到裡面還使用了比較糾結的外連線。從執行計劃來說，預設是走nested loop join，資料的查取中會走索引，從oracle的分析來說這樣的效果要好一些。
SELECT /*+ leading(cyc_cust,cyc_pay) */
CUST.CUSTOMER_ID,
CUST.WEIGHT,
CYC_CUST.STATUS,
CYC_CUST.DB_STATUS,
CYC_CUST.UNDO_REQ_TYPE,
CYC_PAY.BA_NO AS BA_NO,
CYC_PAY.STATUS AS PAY_STATUS,
CYC_PAY.DB_STATUS AS PAY_DB_STATUS,
CYC_PAY.UNDO_REQ_TYPE AS PAY_UNDO_REQ_TYPE,
CYC_CUST.CYCLE_SEQ_RUN
FROM CUSTOMER CUST,
CYCLE_CUSTOMERS CYC_CUST,
CYC_PAYER_POP CYC_PAY
WHERE CYC_CUST.PERIOD_KEY = 57
AND CYC_CUST.CUSTOMER_KEY = 87
AND CYC_CUST.CYCLE_SEQ_NO =3325
AND CUST.CUSTOMER_ID = CYC_CUST.CUSTOMER_NO
AND CYC_PAY.PERIOD_KEY(+) = CYC_CUST.PERIOD_KEY
AND CYC_PAY.CUSTOMER_KEY(+) = CYC_CUST.CUSTOMER_KEY
AND CYC_PAY.CUSTOMER_NO(+) = CYC_CUST.CUSTOMER_NO
AND CYC_PAY.CYCLE_SEQ_NO(+) = CYC_CUST.
CYCLE_SEQ_NO
AND (CYC_PAY.UNDO_REQ_TYPE <> 'N' OR CYC_CUST.UNDO_REQ_TYPE <> 'N')
ORDER BY CUSTOMER_ID;
預計時間在1分半左右，在生產環境中單個執行的時候差不多控制在20~30秒左右。這樣看來實際效果比期望的要好。
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2152 | 138K| 8254 (1)| 00:01:40 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 2152 | 138K| 8254 (1)| 00:01:40 | | |
|* 3 | FILTER | | | | | | | |
| 4 | NESTED LOOPS OUTER | | 2152 | 119K| 8039 (1)| 00:01:37 | | |
| 5 | PARTITION RANGE SINGLE | | 4304 | 109K| 6317 (1)| 00:01:16 | KEY | KEY |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| CYCLE_CUSTOMERS | 4304 | 109K| 6317 (1)| 00:01:16 | KEY | KEY |
|* 7 | INDEX FULL SCAN | CYCLE_CUSTOMERS_PK | 24 | | 5944 (1)| 00:01:12 | KEY | KEY |
| 8 | PARTITION RANGE SINGLE | | 1 | 31 | 1 (0)| 00:00:01 | KEY | KEY |
|* 9 | TABLE ACCESS BY LOCAL INDEX ROWID| CYC_PAYER_POP | 1 | 31 | 1 (0)| 00:00:01 | KEY | KEY |
|* 10 | INDEX RANGE SCAN | CYC_PAYER_POP_1IX | 3 | | 1 (0)| 00:00:01 | KEY | KEY |
|* 11 | INDEX UNIQUE SCAN | CUSTOMER_PK | 1 | | 1 (0)| 00:00:01 | | |
| 12 | TABLE ACCESS BY INDEX ROWID | CUSTOMER | 1 | 9 | 1 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------
但是在生產環境中執行的時候，是透過一個程式來觸發的，會並行的執行多個這樣的查詢，結果效能是越來越糟糕，最後本來執行20秒的語句現在又變成了1分半了。
在嘗試加入4個並行之後，效果沒有任何的改觀。
在排查這個問題的時候有兩個方面的考慮，也是在調優中可能比較糾結的問題。
一個是nested loop join和hash join的取捨
這個問題按照理論來說是比較好判斷的，如果大表，小表關聯，是建議採用nested loop join的。
如果大表，大表關聯，是建議採用hash join的。
但是在一些sql的執行過程中，可能根據執行計劃走了索引，結果按照預期，過濾出的資料應該很少，在解析的時候就走了nested loop join，但是實際中可能資料分佈很不均勻，有時候過濾出的資料多，有時候過濾出的資料少。
這樣一來，如果過濾出的資料量大的時候，走了nested loop join 結果就會傳送大量的io請求，然後不停的去走關聯，瓶頸都消耗在io等待上了。
第二個問題是關於索引和全表掃描
這個問題，可能開發人員的普遍認識就是全表掃描要比索引要慢，大多數情況是，但是dba碰到的更多情況是希望走全表掃描，因為走索引的代價其實更大。如果表很小的情況下，走索引和全表掃描來說，全表掃描要更好一些，
如果表很大的情況下，但是透過索引過濾出的資料也比較多，那麼也就建議還是走全表吧。
現在生產環境中碰到的這個sql問題就有如上的兩種情況。
這幾張表都是上千萬的大表，有的表中的資料分佈確實不透均勻，結果就在處理某一批資料的時候，效能還可以接受，但是在處理另外一批資料的時候效能抖動就很明顯。
如果同時執行多個查詢的情況下。可能等待時間都在io上了。
在檢視awr報告中的一些指標之後，我嘗試修改表的訪問路徑，把原本的兩個表關聯由nested loop join變為hash join。
然後兩個表都堅持走全表掃描，當然了考慮到了執行的效率，加了4個並行。這樣來說就算比較籠統的平衡了資料不均勻的問題。
修改hint為/*+ use_hash(CYC_CUST,CYC_PAY) parallel(CYC_CUST 4) */
從執行計劃來看是有些得不償失的，cpu消耗要高很多。但是在實際執行的時候效果卻很好。平均執行時間為10~15秒，執行的很穩定。在做了多個session的併發查詢的情況下，速度還是很穩定。
Plan hash value: 4044834379
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 567 | 37422 | 45236 (1)| 00:09:03 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10003 | 567 | 37422 | 45236 (1)| 00:09:03 | | | Q1,03 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 567 | 37422 | 45236 (1)| 00:09:03 | | | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | | | | | | | Q1,03 | PCWP | |
| 5 | PX SEND RANGE | :TQ10002 | | | | | | | Q1,02 | P->P | RANGE |
| 6 | NESTED LOOPS | | | | | | | | Q1,02 | PCWP | |
| 7 | NESTED LOOPS | | 567 | 37422 | 45235 (1)| 00:09:03 | | | Q1,02 | PCWP | |
|* 8 | FILTER | | | | | | | | Q1,02 | PCWC | |
|* 9 | HASH JOIN OUTER | | 567 | 32319 | 45219 (1)| 00:09:03 | | | Q1,02 | PCWP | |
| 10 | PX RECEIVE | | 1134 | 29484 | 8563 (1)| 00:01:43 | | | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 1134 | 29484 | 8563 (1)| 00:01:43 | | | Q1,01 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 1134 | 29484 | 8563 (1)| 00:01:43 | 171 | 171 | Q1,01 | PCWC | |
|* 13 | TABLE ACCESS FULL | CYCLE_CUSTOMERS | 1134 | 29484 | 8563 (1)| 00:01:43 | 171 | 171 | Q1,01 | PCWP | |
| 14 | BUFFER SORT | | | | | | | | Q1,02 | PCWC | |
| 15 | PX RECEIVE | | 1355 | 42005 | 36656 (1)| 00:07:20 | | | Q1,02 | PCWP | |
| 16 | PX SEND HASH | :TQ10000 | 1355 | 42005 | 36656 (1)| 00:07:20 | | | | S->P | HASH |
| 17 | PARTITION RANGE SINGLE| | 1355 | 42005 | 36656 (1)| 00:07:20 | 171 | 171 | | | |
|* 18 | TABLE ACCESS FULL | CYC_PAYER_POP | 1355 | 42005 | 36656 (1)| 00:07:20 | 171 | 171 | | | |
|* 19 | INDEX UNIQUE SCAN | CUSTOMER_PK | 1 | | 1 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 20 | TABLE ACCESS BY INDEX ROWID | CUSTOMER | 1 | 9 | 1 (0)| 00:00:01 | | | Q1,02 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
所以執行計劃只能作為大家調優的一個參考而已，dba需要對於資料需要有更加深入的瞭解。自己做到心中有數。
最後分享一下兩種方案的執行效果。
採用nested loop join+索引掃描
採用hash join+全表掃描
sql語句類似下面的樣子，可以看到裡面還使用了比較糾結的外連線。從執行計劃來說，預設是走nested loop join，資料的查取中會走索引，從oracle的分析來說這樣的效果要好一些。
SELECT /*+ leading(cyc_cust,cyc_pay) */
CUST.CUSTOMER_ID,
CUST.WEIGHT,
CYC_CUST.STATUS,
CYC_CUST.DB_STATUS,
CYC_CUST.UNDO_REQ_TYPE,
CYC_PAY.BA_NO AS BA_NO,
CYC_PAY.STATUS AS PAY_STATUS,
CYC_PAY.DB_STATUS AS PAY_DB_STATUS,
CYC_PAY.UNDO_REQ_TYPE AS PAY_UNDO_REQ_TYPE,
CYC_CUST.CYCLE_SEQ_RUN
FROM CUSTOMER CUST,
CYCLE_CUSTOMERS CYC_CUST,
CYC_PAYER_POP CYC_PAY
WHERE CYC_CUST.PERIOD_KEY = 57
AND CYC_CUST.CUSTOMER_KEY = 87
AND CYC_CUST.CYCLE_SEQ_NO =3325
AND CUST.CUSTOMER_ID = CYC_CUST.CUSTOMER_NO
AND CYC_PAY.PERIOD_KEY(+) = CYC_CUST.PERIOD_KEY
AND CYC_PAY.CUSTOMER_KEY(+) = CYC_CUST.CUSTOMER_KEY
AND CYC_PAY.CUSTOMER_NO(+) = CYC_CUST.CUSTOMER_NO
AND CYC_PAY.CYCLE_SEQ_NO(+) = CYC_CUST.
CYCLE_SEQ_NO
AND (CYC_PAY.UNDO_REQ_TYPE <> 'N' OR CYC_CUST.UNDO_REQ_TYPE <> 'N')
ORDER BY CUSTOMER_ID;
預計時間在1分半左右，在生產環境中單個執行的時候差不多控制在20~30秒左右。這樣看來實際效果比期望的要好。
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2152 | 138K| 8254 (1)| 00:01:40 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 2152 | 138K| 8254 (1)| 00:01:40 | | |
|* 3 | FILTER | | | | | | | |
| 4 | NESTED LOOPS OUTER | | 2152 | 119K| 8039 (1)| 00:01:37 | | |
| 5 | PARTITION RANGE SINGLE | | 4304 | 109K| 6317 (1)| 00:01:16 | KEY | KEY |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| CYCLE_CUSTOMERS | 4304 | 109K| 6317 (1)| 00:01:16 | KEY | KEY |
|* 7 | INDEX FULL SCAN | CYCLE_CUSTOMERS_PK | 24 | | 5944 (1)| 00:01:12 | KEY | KEY |
| 8 | PARTITION RANGE SINGLE | | 1 | 31 | 1 (0)| 00:00:01 | KEY | KEY |
|* 9 | TABLE ACCESS BY LOCAL INDEX ROWID| CYC_PAYER_POP | 1 | 31 | 1 (0)| 00:00:01 | KEY | KEY |
|* 10 | INDEX RANGE SCAN | CYC_PAYER_POP_1IX | 3 | | 1 (0)| 00:00:01 | KEY | KEY |
|* 11 | INDEX UNIQUE SCAN | CUSTOMER_PK | 1 | | 1 (0)| 00:00:01 | | |
| 12 | TABLE ACCESS BY INDEX ROWID | CUSTOMER | 1 | 9 | 1 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------
但是在生產環境中執行的時候，是透過一個程式來觸發的，會並行的執行多個這樣的查詢，結果效能是越來越糟糕，最後本來執行20秒的語句現在又變成了1分半了。
在嘗試加入4個並行之後，效果沒有任何的改觀。
在排查這個問題的時候有兩個方面的考慮，也是在調優中可能比較糾結的問題。
一個是nested loop join和hash join的取捨
這個問題按照理論來說是比較好判斷的，如果大表，小表關聯，是建議採用nested loop join的。
如果大表，大表關聯，是建議採用hash join的。
但是在一些sql的執行過程中，可能根據執行計劃走了索引，結果按照預期，過濾出的資料應該很少，在解析的時候就走了nested loop join，但是實際中可能資料分佈很不均勻，有時候過濾出的資料多，有時候過濾出的資料少。
這樣一來，如果過濾出的資料量大的時候，走了nested loop join 結果就會傳送大量的io請求，然後不停的去走關聯，瓶頸都消耗在io等待上了。
第二個問題是關於索引和全表掃描
這個問題，可能開發人員的普遍認識就是全表掃描要比索引要慢，大多數情況是，但是dba碰到的更多情況是希望走全表掃描，因為走索引的代價其實更大。如果表很小的情況下，走索引和全表掃描來說，全表掃描要更好一些，
如果表很大的情況下，但是透過索引過濾出的資料也比較多，那麼也就建議還是走全表吧。
現在生產環境中碰到的這個sql問題就有如上的兩種情況。
這幾張表都是上千萬的大表，有的表中的資料分佈確實不透均勻，結果就在處理某一批資料的時候，效能還可以接受，但是在處理另外一批資料的時候效能抖動就很明顯。
如果同時執行多個查詢的情況下。可能等待時間都在io上了。
在檢視awr報告中的一些指標之後，我嘗試修改表的訪問路徑，把原本的兩個表關聯由nested loop join變為hash join。
然後兩個表都堅持走全表掃描，當然了考慮到了執行的效率，加了4個並行。這樣來說就算比較籠統的平衡了資料不均勻的問題。
修改hint為/*+ use_hash(CYC_CUST,CYC_PAY) parallel(CYC_CUST 4) */
從執行計劃來看是有些得不償失的，cpu消耗要高很多。但是在實際執行的時候效果卻很好。平均執行時間為10~15秒，執行的很穩定。在做了多個session的併發查詢的情況下，速度還是很穩定。
Plan hash value: 4044834379
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 567 | 37422 | 45236 (1)| 00:09:03 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10003 | 567 | 37422 | 45236 (1)| 00:09:03 | | | Q1,03 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 567 | 37422 | 45236 (1)| 00:09:03 | | | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | | | | | | | Q1,03 | PCWP | |
| 5 | PX SEND RANGE | :TQ10002 | | | | | | | Q1,02 | P->P | RANGE |
| 6 | NESTED LOOPS | | | | | | | | Q1,02 | PCWP | |
| 7 | NESTED LOOPS | | 567 | 37422 | 45235 (1)| 00:09:03 | | | Q1,02 | PCWP | |
|* 8 | FILTER | | | | | | | | Q1,02 | PCWC | |
|* 9 | HASH JOIN OUTER | | 567 | 32319 | 45219 (1)| 00:09:03 | | | Q1,02 | PCWP | |
| 10 | PX RECEIVE | | 1134 | 29484 | 8563 (1)| 00:01:43 | | | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 1134 | 29484 | 8563 (1)| 00:01:43 | | | Q1,01 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 1134 | 29484 | 8563 (1)| 00:01:43 | 171 | 171 | Q1,01 | PCWC | |
|* 13 | TABLE ACCESS FULL | CYCLE_CUSTOMERS | 1134 | 29484 | 8563 (1)| 00:01:43 | 171 | 171 | Q1,01 | PCWP | |
| 14 | BUFFER SORT | | | | | | | | Q1,02 | PCWC | |
| 15 | PX RECEIVE | | 1355 | 42005 | 36656 (1)| 00:07:20 | | | Q1,02 | PCWP | |
| 16 | PX SEND HASH | :TQ10000 | 1355 | 42005 | 36656 (1)| 00:07:20 | | | | S->P | HASH |
| 17 | PARTITION RANGE SINGLE| | 1355 | 42005 | 36656 (1)| 00:07:20 | 171 | 171 | | | |
|* 18 | TABLE ACCESS FULL | CYC_PAYER_POP | 1355 | 42005 | 36656 (1)| 00:07:20 | 171 | 171 | | | |
|* 19 | INDEX UNIQUE SCAN | CUSTOMER_PK | 1 | | 1 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 20 | TABLE ACCESS BY INDEX ROWID | CUSTOMER | 1 | 9 | 1 (0)| 00:00:01 | | | Q1,02 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
所以執行計劃只能作為大家調優的一個參考而已，dba需要對於資料需要有更加深入的瞭解。自己做到心中有數。
最後分享一下兩種方案的執行效果。
採用nested loop join+索引掃描
new1.log:Elapsed: 00:00:56.30
new1.log:Elapsed: 00:00:23.51
new1.log:Elapsed: 00:00:01.46
new1.log:Elapsed: 00:00:01.29
new1.log:Elapsed: 00:00:01.29
new1.log:Elapsed: 00:00:01.27
new1.log:Elapsed: 00:00:01.28
new1.log:Elapsed: 00:00:01.26
new1.log:Elapsed: 00:00:01.28
new2.log:Elapsed: 00:00:42.79
new2.log:Elapsed: 00:00:23.51
new2.log:Elapsed: 00:00:01.29
new2.log:Elapsed: 00:00:01.28
new2.log:Elapsed: 00:00:01.29
new2.log:Elapsed: 00:00:01.27
new2.log:Elapsed: 00:00:01.27
new2.log:Elapsed: 00:00:01.27
new2.log:Elapsed: 00:00:01.28
採用hash join+全表掃描
new1.log:Elapsed: 00:00:14.95
new1.log:Elapsed: 00:00:13.70
new1.log:Elapsed: 00:00:12.81
new1.log:Elapsed: 00:00:13.89
new1.log:Elapsed: 00:00:14.56
new1.log:Elapsed: 00:00:11.61
new1.log:Elapsed: 00:00:11.45
new1.log:Elapsed: 00:00:10.84
new1.log:Elapsed: 00:00:11.31
new2.log:Elapsed: 00:00:12.13
new2.log:Elapsed: 00:00:11.85
new2.log:Elapsed: 00:00:14.44
new2.log:Elapsed: 00:00:15.60
new2.log:Elapsed: 00:00:12.55
new2.log:Elapsed: 00:00:11.09
new2.log:Elapsed: 00:00:11.11
new2.log:Elapsed: 00:00:11.04
new2.log:Elapsed: 00:00:11.14
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8494287/viewspace-1347008/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 完美的執行計劃導致的效能問題
- 執行計劃中的COLLECTION ITERATOR PICKLER FETCH導致的效能問題
- 執行計劃問題導致處理速度時快時慢的問題
- 執行計劃變化導致CPU負載高的問題分析負載
- SQL執行計劃異常引起的效能問題SQL
- SQL執行計劃異常 引起的效能問題SQL
- Grant許可權導致執行計劃失效
- ORACLE DML執行計劃頻繁變更導致業務響應極慢問題的處理Oracle
- mysql的執行計劃快取問題MySql快取
- 統計資訊不正確導致執行計劃的錯誤選擇
- RLM$SCHDNEGACTION 執行導致負載問題負載
- MySQL8.0 view導致的效能問題MySqlView
- 11G的SORT GROUP BY NOSORT導致錯誤執行計劃
- 資料庫統計資訊不更新導致的效能問題資料庫
- autotrace 和explain plan for可能導致執行計劃錯誤AI
- 執行計劃錯誤導致系統負載高負載
- 一個執行計劃解析的小問題分析
- WebMagic多執行緒導致註解失效問題Web執行緒
- Oracle資料庫導致效能問題的可能原因Oracle資料庫
- memlock過低導致的資料庫效能問題資料庫
- ORACLE analyse table方式收集表統計資訊導致SQL執行計劃不準確而效能下降OracleSQL
- 一條執行4秒的sql語句導致的系統問題SQL
- SCHEDULER呼叫XDB程式導致效能問題
- 統計資訊不準確導致執行計劃走了笛卡爾積
- 一次ORACLE SQL謂詞跨界導致的執行計劃不準OracleSQL
- Oracle訪問表的執行計劃Oracle
- 交流（1）-- 執行計劃錯誤問題
- 找出導致db file scattered read等待事件發生的SQL及其執行計劃事件SQL
- 你知道嗎？——ASP.NET的Session會導致的效能問題ASP.NETSession
- Oracle訪問索引的執行計劃（一）Oracle索引
- Oracle訪問索引的執行計劃（二）Oracle索引
- Oracle訪問索引的執行計劃（三）Oracle索引
- Oracle訪問索引的執行計劃（四）Oracle索引
- Oracle訪問索引的執行計劃（五）Oracle索引
- 對一個執行計劃的疑問
- 一次oracle行級鎖導致的問題Oracle
- merge語句導致的效能問題緊急優化優化
- 一條insert語句導致的效能問題分析（二)