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

從業(yè)務(wù)拿到了相關(guān)SQL，這是一條DML語(yǔ)句：

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

看到這條SQL，第一感覺(jué)是使用NOTIN子查詢可能不是一個(gè)好主意，先來(lái)檢查下子查詢的結(jié)果集，也就是表PMT_K5CZ1_BT2的數(shù)據(jù)量。

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

近3000萬(wàn)條數(shù)據(jù)！用NOTIN子查詢，雖然這是一個(gè)非常不好的SQL書(shū)寫(xiě)習(xí)慣，不過(guò)理想情況下優(yōu)化器都會(huì)進(jìn)行查詢轉(zhuǎn)換，況且業(yè)務(wù)反饋遷移前是沒(méi)問(wèn)題的，耳聽(tīng)為虛，眼見(jiàn)為實(shí)，我們來(lái)驗(yàn)證下。

由于原庫(kù)數(shù)據(jù)仍然處于保留期，且相關(guān)表的數(shù)據(jù)量基本一致，我們可以分別執(zhí)行對(duì)比，為測(cè)試方便，對(duì)語(yǔ)句稍做修改，先去除INSERTINTO部分：

結(jié)果和業(yè)務(wù)反饋一致，這條語(yǔ)句在新庫(kù)長(zhǎng)時(shí)間無(wú)法返回結(jié)果，在原庫(kù)雖然效率不高，但20秒左右就可以正常返回結(jié)果，看來(lái)問(wèn)題就出在SELECT部分。

統(tǒng)計(jì)信息、索引和執(zhí)行環(huán)境是影響執(zhí)行計(jì)劃生成的常見(jiàn)因素，首先檢查統(tǒng)計(jì)信息和索引，但沒(méi)有發(fā)現(xiàn)任何異常，暫時(shí)可以排除這兩個(gè)因素。

對(duì)比SELECT語(yǔ)句在新舊環(huán)境生成的執(zhí)行計(jì)劃，看看有哪些區(qū)別。

 --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))

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

我們注意到，新庫(kù)執(zhí)行計(jì)劃的OutlineData部分，多出了22個(gè)OPT_PARAM參數(shù)信息，這說(shuō)明很多優(yōu)化器相關(guān)參數(shù)并非默認(rèn)值，到了這一步，結(jié)合SQL語(yǔ)句中的NOTIN子查詢結(jié)構(gòu)，有經(jīng)驗(yàn)的DBA應(yīng)該能判斷出其中的_optimizer_squ_bottomup參數(shù)有最大嫌疑，因?yàn)檫@個(gè)參數(shù)和NOTIN子查詢展開(kāi)直接相關(guān)。

如果對(duì)這個(gè)參數(shù)不熟悉也沒(méi)關(guān)系，可以在session級(jí)別對(duì)上述參數(shù)逐個(gè)設(shè)置，然后執(zhí)行SQL測(cè)試，最終在設(shè)置_optimizer_squ_bottomup為true時(shí)，生成了和原庫(kù)一致的執(zhí)行計(jì)劃，查詢很快執(zhí)行完畢。為了完全確認(rèn)問(wèn)題，重新將_optimizer_squ_bottomup設(shè)置為false，分別用NOTEXISTS或添加ISNOT NULL條件改寫(xiě)這條SQL，最終都生成了比較高效的執(zhí)行計(jì)劃（HASHJOIN ANTI），查詢很快返回結(jié)果，不過(guò)顯然使用NOTEXISTS會(huì)更加高效。

NOT EXISTS方法：

IS NOT NULL方法：

至此，問(wèn)題原因很清楚了，無(wú)論是12c或19c，默認(rèn)配置下優(yōu)化器在生成執(zhí)行計(jì)劃時(shí)，會(huì)嘗試將NOTIN子查詢轉(zhuǎn)換為更高效的JOIN操作，但隱含參數(shù)_optimizer_squ_bottomup設(shè)置為false禁用了這個(gè)功能，最終只能使用低效的FILTER操作。

解決這個(gè)問(wèn)題，有三種方法：

1. 數(shù)據(jù)庫(kù)變更：session或system級(jí)別設(shè)置_optimizer_squ_bottomup參數(shù)為true；

2. 業(yè)務(wù)數(shù)據(jù)變更：在查詢相關(guān)兩張表的vres_number字段同時(shí)添加非空約束；

3. SQL語(yǔ)句優(yōu)化：在外部查詢和子查詢中同時(shí)添加vres_number IS NOT NULL條件；或者使用NOT EXISTS替代NOT IN改寫(xiě)SQL語(yǔ)句。

經(jīng)過(guò)了解，19c環(huán)境的部署嚴(yán)格按照標(biāo)準(zhǔn)化文檔執(zhí)行，設(shè)置了大量隱含參數(shù)，主要目的是為了規(guī)避BUG，提高數(shù)據(jù)庫(kù)穩(wěn)定性，因此第一種方法不推薦，這里建議開(kāi)發(fā)人員同時(shí)使用后兩種方法優(yōu)化。

另外，Oracle的優(yōu)化器越來(lái)越智能，但智能化并不能解決所有問(wèn)題，只有堅(jiān)持良好規(guī)范的SQL書(shū)寫(xiě)習(xí)慣，才能適應(yīng)基礎(chǔ)環(huán)境的變化，確保程序的健壯性。