執行計劃的偏差導緻的性能問題

在生産環境中有一條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秒左右。這樣看來實際效果比期望的要好。

---------------------------------------------------------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------------------------------------------------------------------------------

| 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.27

new1.log:Elapsed: 00:00:01.28

new1.log:Elapsed: 00:00:01.26

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.27

采用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

執行計劃的偏差導緻的性能問題

繼續閱讀

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

賦予使用者dba權限--解釋以上：

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

為什麼要選擇UniDAC

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

windows不能在本地計算機上運作oracleDbConsoleorcl

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離

Oracle 批量查詢傳入List 傳回List