升级Doris后通过catalog查询sql变慢

Viewed 32

同一条sql,升级Doris后,通过catalog查询sql变慢,原来只需要几秒钟,现在需要一分钟。
升级前版本:Doris2.1.5
升级后版本:Doris2.1.6
执行SQL:

select *,concat(WORKSHOP,LINE_BODY) as workshop_line
from in_orcl.MODULE_BI.TB_MPL_PRODUCTION_PLANNING_XLS
where concat(week_id,plan_version) = (select max(concat(week_id,plan_version))
                                          from in_orcl.MODULE_BI.TB_MPL_PRODUCTION_PLANNING_XLS);

in_orcl.MODULE_BI.TB_MPL_PRODUCTION_PLANNING_XLS表数据量:66427条。

explain执行计划:

PLAN FRAGMENT 0
  OUTPUT EXPRS:
    P_CODE[#305]
    WEEK_ID[#306]
    ID[#307]
    PLAN_VERSION[#308]
    PLAN_CATEGORY[#309]
    GLASS_TYPE[#310]
    COMPONENT_FACTORY[#311]
    WORKSHOP[#312]
    LINE_BODY[#313]
    SALES_AREA[#314]
    SALES_TEAM[#315]
    CLIENT_NAME[#316]
    REQUIREMENT_TYPE[#317]
    ORDER_NAME[#318]
    PRODUCT_CODE[#319]
    CONTRACT_NO[#320]
    OMS_ORDER[#321]
    REVIEW_NUMBER[#322]
    ERP_AGREEMENT_NUMBER[#323]
    COMPONENT_STANDARD[#324]
    COMPONENT_TYPE[#325]
    PRODUCT_TYPE[#326]
    EXTENDED_VERSION[#327]
    FRAME_TYPE[#328]
    PROJECT_TOTAL[#329]
    WIRE_BOX_TERMINAL[#330]
    WIRE_BOX_BODY[#331]
    RIBBON_TYPE[#332]
    SUPERVISION[#333]
    INSPECTION_BATCH[#334]
    SHIPPING_PLACE[#335]
    INSPECTION_CYCLE[#336]
    VERSION[#337]
    ILLUSTRATE[#338]
    POWER_HIT[#339]
    POWER_SALES[#340]
    POWER_PRODUCTION[#341]
    PRODUCTION_YIELD[#342]
    QUALITY_SALES[#343]
    QUALITY_PRODUCTION[#344]
    GEAR[#345]
    DEMAND_POWER[#346]
    YEAR_MONTH[#347]
    BEFORE_DAY[#348]
    DAY1[#349]
    DAY2[#350]
    DAY3[#351]
    DAY4[#352]
    DAY5[#353]
    DAY6[#354]
    DAY7[#355]
    DAY8[#356]
    DAY9[#357]
    DAY10[#358]
    DAY11[#359]
    DAY12[#360]
    DAY13[#361]
    DAY14[#362]
    DAY15[#363]
    DAY16[#364]
    DAY17[#365]
    DAY18[#366]
    DAY19[#367]
    DAY20[#368]
    DAY21[#369]
    DAY22[#370]
    DAY23[#371]
    DAY24[#372]
    DAY25[#373]
    DAY26[#374]
    DAY27[#375]
    DAY28[#376]
    DAY29[#377]
    DAY30[#378]
    DAY31[#379]
    workshop_line[#380]
  PARTITION: UNPARTITIONED
""
  HAS_COLO_PLAN_NODE: false
""
  VRESULT SINK
     MYSQL_PROTOCAL
""
  8:VEXCHANGE
     offset: 0
     distribute expr lists: 
""
PLAN FRAGMENT 1
""
"  PARTITION: HASH_PARTITIONED: expr_concat(week_id, plan_version)[#195]"
""
  HAS_COLO_PLAN_NODE: false
""
  STREAM DATA SINK
    EXCHANGE ID: 08
    UNPARTITIONED
""
  7:VHASH JOIN(321)
  |  join op: INNER JOIN(PARTITIONED)[]
"  |  equal join conjunct: (expr_concat(week_id, plan_version)[#195] = max(concat(week_id, plan_version))[#76])"
  |  cardinality=1
  |  vec output tuple id: 6
  |  output tuple id: 6
  |  vIntermediate tuple ids: 5 
  |  hash output slot ids: 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 
"  |  final projections: P_CODE[#228], WEEK_ID[#229], ID[#230], PLAN_VERSION[#231], PLAN_CATEGORY[#232], GLASS_TYPE[#233], COMPONENT_FACTORY[#234], WORKSHOP[#235], LINE_BODY[#236], SALES_AREA[#237], SALES_TEAM[#238], CLIENT_NAME[#239], REQUIREMENT_TYPE[#240], ORDER_NAME[#241], PRODUCT_CODE[#242], CONTRACT_NO[#243], OMS_ORDER[#244], REVIEW_NUMBER[#245], ERP_AGREEMENT_NUMBER[#246], COMPONENT_STANDARD[#247], COMPONENT_TYPE[#248], PRODUCT_TYPE[#249], EXTENDED_VERSION[#250], FRAME_TYPE[#251], PROJECT_TOTAL[#252], WIRE_BOX_TERMINAL[#253], WIRE_BOX_BODY[#254], RIBBON_TYPE[#255], SUPERVISION[#256], INSPECTION_BATCH[#257], SHIPPING_PLACE[#258], INSPECTION_CYCLE[#259], VERSION[#260], ILLUSTRATE[#261], POWER_HIT[#262], POWER_SALES[#263], POWER_PRODUCTION[#264], PRODUCTION_YIELD[#265], QUALITY_SALES[#266], QUALITY_PRODUCTION[#267], GEAR[#268], DEMAND_POWER[#269], YEAR_MONTH[#270], BEFORE_DAY[#272], DAY1[#273], DAY2[#274], DAY3[#275], DAY4[#276], DAY5[#277], DAY6[#278], DAY7[#279], DAY8[#280], DAY9[#281], DAY10[#282], DAY11[#283], DAY12[#284], DAY13[#285], DAY14[#286], DAY15[#287], DAY16[#288], DAY17[#289], DAY18[#290], DAY19[#291], DAY20[#292], DAY21[#293], DAY22[#294], DAY23[#295], DAY24[#296], DAY25[#297], DAY26[#298], DAY27[#299], DAY28[#300], DAY29[#301], DAY30[#302], DAY31[#303], concat(WORKSHOP[#235], LINE_BODY[#236])"
  |  final project output tuple id: 6
"  |  distribute expr lists: expr_concat(week_id, plan_version)[#195]"
"  |  distribute expr lists: max(concat(week_id, plan_version))[#76]"
  |  
  |----4:VEXCHANGE
  |       offset: 0
  |       distribute expr lists: 
  |    
  6:VEXCHANGE
     offset: 0
     distribute expr lists: 
""
PLAN FRAGMENT 2
""
  PARTITION: RANDOM
""
  HAS_COLO_PLAN_NODE: false
""
  STREAM DATA SINK
    EXCHANGE ID: 06
"    HASH_PARTITIONED: expr_concat(week_id, plan_version)[#195]"
""
  5:VJdbcScanNode(296)
"     TABLE: ""MODULE_BI"".""TB_MPL_PRODUCTION_PLANNING_XLS"""
"     QUERY: SELECT ""P_CODE"", ""WEEK_ID"", ""ID"", ""PLAN_VERSION"", ""PLAN_CATEGORY"", ""GLASS_TYPE"", ""COMPONENT_FACTORY"", ""WORKSHOP"", ""LINE_BODY"", ""SALES_AREA"", ""SALES_TEAM"", ""CLIENT_NAME"", ""REQUIREMENT_TYPE"", ""ORDER_NAME"", ""PRODUCT_CODE"", ""CONTRACT_NO"", ""OMS_ORDER"", ""REVIEW_NUMBER"", ""ERP_AGREEMENT_NUMBER"", ""COMPONENT_STANDARD"", ""COMPONENT_TYPE"", ""PRODUCT_TYPE"", ""EXTENDED_VERSION"", ""FRAME_TYPE"", ""PROJECT_TOTAL"", ""WIRE_BOX_TERMINAL"", ""WIRE_BOX_BODY"", ""RIBBON_TYPE"", ""SUPERVISION"", ""INSPECTION_BATCH"", ""SHIPPING_PLACE"", ""INSPECTION_CYCLE"", ""VERSION"", ""ILLUSTRATE"", ""POWER_HIT"", ""POWER_SALES"", ""POWER_PRODUCTION"", ""PRODUCTION_YIELD"", ""QUALITY_SALES"", ""QUALITY_PRODUCTION"", ""GEAR"", ""DEMAND_POWER"", ""YEAR_MONTH"", ""BEFORE_DAY"", ""DAY1"", ""DAY2"", ""DAY3"", ""DAY4"", ""DAY5"", ""DAY6"", ""DAY7"", ""DAY8"", ""DAY9"", ""DAY10"", ""DAY11"", ""DAY12"", ""DAY13"", ""DAY14"", ""DAY15"", ""DAY16"", ""DAY17"", ""DAY18"", ""DAY19"", ""DAY20"", ""DAY21"", ""DAY22"", ""DAY23"", ""DAY24"", ""DAY25"", ""DAY26"", ""DAY27"", ""DAY28"", ""DAY29"", ""DAY30"", ""DAY31"" FROM ""MODULE_BI"".""TB_MPL_PRODUCTION_PLANNING_XLS"""
"     final projections: P_CODE[#77], WEEK_ID[#78], ID[#79], PLAN_VERSION[#80], PLAN_CATEGORY[#81], GLASS_TYPE[#82], COMPONENT_FACTORY[#83], WORKSHOP[#84], LINE_BODY[#85], SALES_AREA[#86], SALES_TEAM[#87], CLIENT_NAME[#88], REQUIREMENT_TYPE[#89], ORDER_NAME[#90], PRODUCT_CODE[#91], CONTRACT_NO[#92], OMS_ORDER[#93], REVIEW_NUMBER[#94], ERP_AGREEMENT_NUMBER[#95], COMPONENT_STANDARD[#96], COMPONENT_TYPE[#97], PRODUCT_TYPE[#98], EXTENDED_VERSION[#99], FRAME_TYPE[#100], PROJECT_TOTAL[#101], WIRE_BOX_TERMINAL[#102], WIRE_BOX_BODY[#103], RIBBON_TYPE[#104], SUPERVISION[#105], INSPECTION_BATCH[#106], SHIPPING_PLACE[#107], INSPECTION_CYCLE[#108], VERSION[#109], ILLUSTRATE[#110], POWER_HIT[#111], POWER_SALES[#112], POWER_PRODUCTION[#113], PRODUCTION_YIELD[#114], QUALITY_SALES[#115], QUALITY_PRODUCTION[#116], GEAR[#117], DEMAND_POWER[#118], YEAR_MONTH[#119], concat(WEEK_ID[#78], PLAN_VERSION[#80]), BEFORE_DAY[#120], DAY1[#121], DAY2[#122], DAY3[#123], DAY4[#124], DAY5[#125], DAY6[#126], DAY7[#127], DAY8[#128], DAY9[#129], DAY10[#130], DAY11[#131], DAY12[#132], DAY13[#133], DAY14[#134], DAY15[#135], DAY16[#136], DAY17[#137], DAY18[#138], DAY19[#139], DAY20[#140], DAY21[#141], DAY22[#142], DAY23[#143], DAY24[#144], DAY25[#145], DAY26[#146], DAY27[#147], DAY28[#148], DAY29[#149], DAY30[#150], DAY31[#151]"
     final project output tuple id: 4
""
PLAN FRAGMENT 3
""
  PARTITION: UNPARTITIONED
""
  HAS_COLO_PLAN_NODE: true
""
  STREAM DATA SINK
    EXCHANGE ID: 04
"    HASH_PARTITIONED: max(concat(week_id, plan_version))[#76]"
""
  3:VAGGREGATE (merge finalize)(315)
"  |  output: max(partial_max(concat(week_id, plan_version))[#75])[#76]"
  |  group by: 
  |  cardinality=1
  |  distribute expr lists: 
  |  
  2:VEXCHANGE
     offset: 0
     distribute expr lists: 
""
PLAN FRAGMENT 4
""
  PARTITION: RANDOM
""
  HAS_COLO_PLAN_NODE: false
""
  STREAM DATA SINK
    EXCHANGE ID: 02
    UNPARTITIONED
""
  1:VAGGREGATE (update serialize)(309)
"  |  output: partial_max(concat(WEEK_ID[#1], PLAN_VERSION[#3]))[#75]"
  |  group by: 
  |  cardinality=1
  |  distribute expr lists: 
  |  
  0:VJdbcScanNode(303)
"     TABLE: ""MODULE_BI"".""TB_MPL_PRODUCTION_PLANNING_XLS"""
"     QUERY: SELECT ""WEEK_ID"", ""PLAN_VERSION"" FROM ""MODULE_BI"".""TB_MPL_PRODUCTION_PLANNING_XLS"""
""
""
Statistics
 planed with unknown column statistics

2 Answers

216 为了兼容更多的 Oracle JDBC Driver 版本带来了一些性能问题,217 已经修复,可以等新版本发布升级,或者暂时将 be/lib/java_extensions/jdbc-scanner/jdbc-scanner-jar-with-dependencies.jar 这个 jar 替换成 215 的

感谢反馈!
麻烦确认下:
1、216跑的时候集群是否有cpu/mem/io负载
2、开启data cache多次查询时效率如何