近日，一套生產庫由Oracle12c多租戶環境遷移至19c多租戶環境，遷移操作使用工具EXPDP/IMPDP，整個過程較為順利。但遷移完成后的第二天，業務反饋部分SQL執行比原庫慢很多，甚至有些SQL長時間無法返回結果。因為遷移前考慮到這套庫非核心生產，而且硬件提升較大，所以并未執行嚴格的性能測試，現在出現這種問題也不算意外。

從業務拿到了相關SQL，這是一條DML語句：

 INSERTINTOxzhj.pmt_k5cz1_bt3
  SELECTa.*
   FROMxzhj.pmt_k5cz1_bt1 a
   WHEREa.vres_number NOTIN
     (SELECTvres_number
        FROMxzhj.pmt_k5cz1_bt2)

看到這條SQL，第一感覺是使用NOTIN子查詢可能不是一個好主意，先來檢查下子查詢的結果集，也就是表PMT_K5CZ1_BT2的數據量。

 SQL> selectcount(*)fromxzhj.pmt_k5cz1_bt2;
  COUNT(*)
 ------------------
 29318502

近3000萬條數據！用NOTIN子查詢，雖然這是一個非常不好的SQL書寫習慣，不過理想情況下優化器都會進行查詢轉換，況且業務反饋遷移前是沒問題的，耳聽為虛，眼見為實，我們來驗證下。

由于原庫數據仍然處于保留期，且相關表的數據量基本一致，我們可以分別執行對比，為測試方便，對語句稍做修改，先去除INSERTINTO部分：

結果和業務反饋一致，這條語句在新庫長時間無法返回結果，在原庫雖然效率不高，但20秒左右就可以正常返回結果，看來問題就出在SELECT部分。

統計信息、索引和執行環境是影響執行計劃生成的常見因素，首先檢查統計信息和索引，但沒有發現任何異常，暫時可以排除這兩個因素。

對比SELECT語句在新舊環境生成的執行計劃，看看有哪些區別。

 --12C
 PLAN_TABLE_OUTPUT
 -------------------------------------------------------------------------------------
 Planhash value:2565751982
 
 ------------------------------------------------------------------------------------
 |Id | Operation           | Name        | Starts | E-Rows|E-Bytes| Cost(%CPU)|
 ------------------------------------------------------------------------------------
 |  0| SELECTSTATEMENT  |               |     3|       |       | 40217(100)|
 |  1| SORT AGGREGATE     |              |      3|      1|    26|           |
 |*  2|   HASH JOIN ANTI NA |              |      3|    153|  3978| 40217  (1)|
 |  3|    TABLEACCESSFULL| PMT_K5CZ1_BT1 |      3|  15345|   179K|  136  (0)|
 |  4|    TABLEACCESSFULL| PMT_K5CZ1_BT2 |      3|     29M|  393M|39978 (1)|
 ------------------------------------------------------------------------------------
 
 QueryBlock Name / ObjectAlias (identifiedbyoperationid):
 -------------------------------------------------------------
 
   1- SEL$5DA710D3
    3- SEL$5DA710D3 / A@SEL$1
    4- SEL$5DA710D3 / PMT_K5CZ1_BT2@SEL$2
 
 OutlineData
 -------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE(12.2.0.1)
      DB_VERSION(12.2.0.1)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$5DA710D3""A"@"SEL$1")
      FULL(@"SEL$5DA710D3""PMT_K5CZ1_BT2"@"SEL$2")
      LEADING(@"SEL$5DA710D3""A"@"SEL$1" "PMT_K5CZ1_BT2"@"SEL$2")
      USE_HASH(@"SEL$5DA710D3""PMT_K5CZ1_BT2"@"SEL$2")
      END_OUTLINE_DATA
  */
 
 PredicateInformation (identifiedbyoperationid):
 ---------------------------------------------------
 
   2- access("A"."vres_number"="vres_number")
 
 ColumnProjection Information (identifiedbyoperationid):
 -----------------------------------------------------------
 
   1- (#keys=0)COUNT(*)[22]
   2- (#keys=1)
   3- "A"."vres_number"[VARCHAR2,64]
   4- "vres_number"[VARCHAR2,64]
  

 --19C
 PLAN_TABLE_OUTPUT
 -------------------------------------------------------------------------------------
 Planhash value:2190031897
 
 ------------------------------------------------------------------------------------
 |Id | Operation           | Name        | Starts | E-Rows|E-Bytes| Cost(%CPU)|
 ------------------------------------------------------------------------------------
 |  0| SELECTSTATEMENT  |               |     1|       |       | 26367(100)|
 |  1| SORT AGGREGATE     |              |      1|      1|    12|           |
 |*  2|   FILTER           |              |      1|       |       |          |
 |   3|    TABLEACCESSFULL| PMT_K5CZ1_BT1 |      1|  15202|   178K|   88  (0)|
 |* 4|    TABLEACCESSFULL| PMT_K5CZ1_BT2 |     24|      1|    14| 26279 (2)|
 ------------------------------------------------------------------------------------
 
 QueryBlock Name / ObjectAlias (identifiedbyoperationid):
 -------------------------------------------------------------
 
   1- SEL$1
    3- SEL$1 / A@SEL$1
    4- SEL$2 / PMT_K5CZ1_BT2@SEL$2
 
 OutlineData
 -------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE(19.1.0)
      DB_VERSION(19.1.0)
      OPT_PARAM(_b_tree_bitmap_plansfalse)
      OPT_PARAM(_optim_peek_user_bindsfalse)
      OPT_PARAM(_optimizer_cost_based_transformationoff)
      OPT_PARAM(_optimizer_squ_bottomupfalse)
      OPT_PARAM(_bloom_filter_enabledfalse)
      OPT_PARAM(_optimizer_extended_cursor_sharingnone)
      OPT_PARAM(_gby_hash_aggregation_enabledfalse)
      OPT_PARAM(_replace_virtual_columnsfalse)
      OPT_PARAM(_bloom_pruning_enabledfalse)
      OPT_PARAM(_optimizer_extended_cursor_sharing_relnone)
      OPT_PARAM(_optimizer_adaptive_cursor_sharingfalse)
      OPT_PARAM(_optimizer_connect_by_elim_dupsfalse)
      OPT_PARAM(_connect_by_use_union_allold_plan_mode)
      OPT_PARAM(_optimizer_use_feedbackfalse)
      OPT_PARAM(_optimizer_partial_join_evalfalse)
      OPT_PARAM(_px_adaptive_dist_methodoff)
      OPT_PARAM(_optimizer_strans_adaptive_pruningfalse)
      OPT_PARAM(_optimizer_aggr_groupby_elimfalse)
      OPT_PARAM(_optimizer_reduce_groupby_keyfalse)
      OPT_PARAM(_optimizer_nlj_hj_adaptive_joinfalse)
      OPT_PARAM(optimizer_index_cost_adj80)
      OPT_PARAM(_fix_control8560951:1 8893626:0 9344709:0 9195582:0 9380298:1 13704562:014142884:1
             16053273:08611462:017760375:0 17938754:0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1""A"@"SEL$1")
      PQ_FILTER(@"SEL$1"SERIAL)
      FULL(@"SEL$2""PMT_K5CZ1_BT2"@"SEL$2")
      END_OUTLINE_DATA
  */
 PredicateInformation (identifiedbyoperationid):
 ---------------------------------------------------
   2- filter( ISNULL)
   4- filter(LNNVL("vres_number"<>:B1))

可以發現，差異主要在原庫（12c）使用HASHJOIN ANTI NA，而新庫（19c）使用了FILTER，顯然后者是一種非常低效的操作，但為什么會有這種變化？

我們注意到，新庫執行計劃的OutlineData部分，多出了22個OPT_PARAM參數信息，這說明很多優化器相關參數并非默認值，到了這一步，結合SQL語句中的NOTIN子查詢結構，有經驗的DBA應該能判斷出其中的_optimizer_squ_bottomup參數有最大嫌疑，因為這個參數和NOTIN子查詢展開直接相關。

如果對這個參數不熟悉也沒關系，可以在session級別對上述參數逐個設置，然后執行SQL測試，最終在設置_optimizer_squ_bottomup為true時，生成了和原庫一致的執行計劃，查詢很快執行完畢。為了完全確認問題，重新將_optimizer_squ_bottomup設置為false，分別用NOTEXISTS或添加ISNOT NULL條件改寫這條SQL，最終都生成了比較高效的執行計劃（HASHJOIN ANTI），查詢很快返回結果，不過顯然使用NOTEXISTS會更加高效。

NOT EXISTS方法：

IS NOT NULL方法：

至此，問題原因很清楚了，無論是12c或19c，默認配置下優化器在生成執行計劃時，會嘗試將NOTIN子查詢轉換為更高效的JOIN操作，但隱含參數_optimizer_squ_bottomup設置為false禁用了這個功能，最終只能使用低效的FILTER操作。

解決這個問題，有三種方法：

1. 數據庫變更：session或system級別設置_optimizer_squ_bottomup參數為true；

2. 業務數據變更：在查詢相關兩張表的vres_number字段同時添加非空約束；

3. SQL語句優化：在外部查詢和子查詢中同時添加vres_number IS NOT NULL條件；或者使用NOT EXISTS替代NOT IN改寫SQL語句。

經過了解，19c環境的部署嚴格按照標準化文檔執行，設置了大量隱含參數，主要目的是為了規避BUG，提高數據庫穩定性，因此第一種方法不推薦，這里建議開發人員同時使用后兩種方法優化。

另外，Oracle的優化器越來越智能，但智能化并不能解決所有問題，只有堅持良好規范的SQL書寫習慣，才能適應基礎環境的變化，確保程序的健壯性。