不良SQL书写习惯引发的性能问题

近日，一套生产库由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书写习惯，才能适应基础环境的变化，确保程序的健壮性。