问题现象
某集群环境 MySQL 租户开启 Auto DOP 后 SQL 执行耗时 2300s+ 耗时较长，执行计划走偏，不开启 Auto DOP，全表扫描执行耗时 475s。
关键诊断信息
估算的行数和实际的行数差距大估行不准。
计划开启了并行计划，并且启用了
JOIN FILTER例如set global runtime_filter_type ='range,in,bloom_filter'。执行计划中有算子
JOIN FILTER CREATE和JOIN FILTER USE算子。+-------------------------------------------------------------------------------+ | =========================================================================================================================================================================== |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |0 |SCALAR GROUP BY | |1 |1425129 |1 |2189794044 |0 |9 | |1 |└─PX COORDINATOR | |8 |1425129 |8 |2189792985 |1951834284 |1952433625 | |2 | └─EXCHANGE OUT DISTR |:EX10003 |8 |1425127 |8 |2189743469 |0 |321 | |3 | └─MERGE GROUP BY | |8 |1425127 |8 |2189743469 |0 |289 | |4 | └─HASH JOIN | |2826278 |1418724 |108320 |2189743469 |0 |105068 | |5 | ├─JOIN FILTER CREATE |:RF0000 |2789519 |741334 |2936335 |383863 |0 |119336 | |6 | │ └─EXCHANGE IN DISTR | |2789519 |741334 |2936335 |349349 |80363 |210491 | |7 | │ └─EXCHANGE OUT DISTR (HASH) |:EX10000 |2789519 |522843 |2936335 |348150 |0 |1609 | |8 | │ └─PX BLOCK ITERATOR | |2789519 |32044 |2936335 |347092 |0 |656 | |9 | │ └─TABLE FULL SCAN |fem |2789519 |32044 |2936335 |347092 |0 |204426 | |10| └─EXCHANGE IN DISTR | |243 |514160 |354350 |2189743342 |1094377198 |2189216540 | |11| └─EXCHANGE OUT DISTR (HASH) |:EX10002 |243 |514136 |354350 |2189741428 |16866 |1461 | |12| └─MATERIAL | |243 |513707 |354350 |2189740368 |0 |746472 | |13| └─NESTED-LOOP ANTI JOIN | |243 |513707 |354350 |2189645060 |0 |13174592 | |14| ├─NESTED-LOOP JOIN | |243 |464913 |354350 |2189645060 |0 |51627019 | |15| │ ├─EXCHANGE IN DISTR | |795 |419799 |2577629 |2189645060 |12325 |199731 | |16| │ │ └─EXCHANGE OUT DISTR (BC2HOST)|:EX10001 |795 |418978 |2577629 |2122487454 |2115799962 |3874 | |17| │ │ └─JOIN FILTER USE |:RF0000 |795 |418747 |2577629 |2122487454 |0 |2503 | |18| │ │ └─PX BLOCK ITERATOR | |795 |418747 |2577629 |2122487454 |0 |4199 | |19| │ │ └─TABLE FULL SCAN |prd |795 |418747 |2577629 |2122487454 |0 |3236126 | |20| │ └─TABLE RANGE SCAN |cust(idx_amm_acc_rlvnc_1) |1 |57 |354350 |2189645060 |0 |1416798700 | |21| └─SUBPLAN SCAN |VIEW1 |1 |201 |0 |2189741428 |0 |96416 | |22| └─DISTRIBUTED TABLE RANGE SCAN |dpe_busssign_reg(IDX_DPE_BUSSSIGN_REG_1_UNQ)|1 |201 |0 |2189741428 |0 |467686129 | ===========================================================================================================================================================================参与连接的表连接的字段有多列并且
NDV比较极端，INR_CUST_ACCT的NDV和行数接近，SUB_ACCT_SEQ_NO的NDV很小。prd.INR_CUST_ACCT prd.SUB_ACCT_SEQ_NO prd : rows: 54491958.000000 statis type: OPTIMIZER version: 1715868185639904 used partitions: [-1] INR_CUST_ACCT : NDV: 48099991.000000 Null: 0.000000 hist scale: -1.000000 Min: __OB__MIN__ Max: __OB__MAX__ SUB_ACCT_SEQ_NO : NDV: 4037.000000 Null: 0.000000 hist scale: -1.000000 Min: __OB__MIN__ Max: __OB__MAX__优化器追踪 optimizer trace log 的获取方法。
step1: proxy”保持“会话 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; step2: 开启当前session的优化器追踪功能 call dbms_xplan.enable_opt_trace(); step3: 设置追踪日志的level和日志文件后缀 call dbms_xplan.set_opt_trace_parameter(identifier=>'trace_test', `level`=>3); step4: 查询计划 explain select * from t1; step5: 在observer日志目录下查看trace_test为后缀的追踪日志 vi /home/admin/oceanbase/log/optimizer_trace_BkkGn1_trace_test.trac step6: 关闭优当前session的化器追踪功能 call dbms_xplan.disable_opt_trace();如果命中了以上 4 条关键信息，大概就命中了此问题。
问题原因
SQL 的并行的计划中参与 JOIN 表下压 JOIN FILTER 后，估行过低导致了两个 NESTLOOP JOIN，性能很差。分配这个 JOIN FILTER 时，计算出了很高的选择率，JOIN FILTER 选择率计算存在问题，这种场景使用多列计算 JOIN FILTER 选择率的并且 NDV 比较极端，得到了偏差较大的结果。（Runtime Filter 是一种用于优化 Hash Join 性能的技术，它可以通过减少 Hash Join 需要 Probe 的数据量来提高查询的效率）
问题的风险及影响
估算走错计划，客户业务执行耗时增加。
适用版本
OceanBase 数据库 V4.2.x 及之后版本。
解决方法
USE_HASH(TABLE_NAME) Hint 指定使用 HASH JOIN。
规避方式
设置 runtime_filter_type 为空，关闭 runtime filter，命令如下。
set global runtime_filter_type ='';
