OBCE-4.2.2.1 慢SQL 生成 outline绑定不生效

撑伞接落花 · 2024 年8 月 7 日 09:08

【使用环境】生产环境
【 OB or 其他组件】OBCE
【使用版本】OBCE-4.2.2.1
【问题描述】慢SQL 生成 outline绑定不生效
【复现路径】
慢 SQL :

SELECT
  COUNT(1)
FROM
  spot_job_content sjc
  LEFT JOIN spot_job sj ON sj.spot_job_id = sjc.spot_job_id
  LEFT JOIN spot s ON sj.spot_id = s.spot_id
WHERE
  s.proj_id = 49
  AND sjc.modified_date >= '2024-08-07 08:45:53.143'

完整执行计划：

===========================================================
|ID|OPERATOR                   |NAME|EST.ROWS|EST.TIME(us)|
-----------------------------------------------------------
|0 |SCALAR GROUP BY            |    |1       |21308       |
|1 |└─NESTED-LOOP JOIN         |    |0       |21308       |
|2 |  ├─NESTED-LOOP JOIN       |    |0       |21287       |
|3 |  │ ├─TABLE FULL SCAN      |sjc |0       |21266       |
|4 |  │ └─DISTRIBUTED TABLE GET|sj  |1       |21          |
|5 |  └─DISTRIBUTED TABLE GET  |s   |1       |21          |
===========================================================
Outputs & filters:
-------------------------------------
  0 - output([T_FUN_COUNT(*)(0x7eef17629a10)]), filter(nil), rowset=16
      group(nil), agg_func([T_FUN_COUNT(*)(0x7eef17629a10)])
  1 - output(nil), filter(nil), rowset=16
      conds(nil), nl_params_([sj.spot_id(0x7eef17625e90)(:2)]), use_batch=false
  2 - output([sj.spot_id(0x7eef17625e90)]), filter(nil), rowset=16
      conds(nil), nl_params_([sjc.spot_job_id(0x7eef17622860)(:0)]), use_batch=false
  3 - output([sjc.spot_job_id(0x7eef17622860)]), filter([sjc.modified_date(0x7eef17628880) >= INTERNAL_FUNCTION('2024-08-07 08:45:53.143', 114, 17)(0x7eef1762ad20)(0x7eef176280f0)]), rowset=16
      access([sjc.spot_job_id(0x7eef17622860)], [sjc.modified_date(0x7eef17628880)]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([sjc.spot_job_content_id(0x7eef1762a8b0)]), range(MIN ; MAX)always true
  4 - output([sj.spot_id(0x7eef17625e90)]), filter(nil), rowset=16
      access([sj.spot_id(0x7eef17625e90)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([sj.spot_job_id(0x7eef17622530)]), range(MIN ; MAX), 
      range_cond([sj.spot_job_id(0x7eef17622530) = :0(0x7eef17780ee0)])
  5 - output(nil), filter([s.proj_id(0x7eef17627520) = 49(0x7eef17626d90)]), rowset=16
      access([s.proj_id(0x7eef17627520)]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([s.spot_id(0x7eef176261c0)]), range(MIN ; MAX), 
      range_cond([:2 = s.spot_id(0x7eef176261c0)(0x7eef177817b0)])
Used Hint:
-------------------------------------
  /*+
      
  */
Qb name trace:
-------------------------------------
  stmt_id:0, stmt_type:T_EXPLAIN 
  stmt_id:1, SEL$1 > SEL$EA493D33 > SEL$60EBDDF2
Outline Data: 
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      LEADING(@"SEL$60EBDDF2" (("fm_catl"."sjc"@"SEL$1" "fm_catl"."sj"@"SEL$1") "fm_catl"."s"@"SEL$1"))
      USE_NL(@"SEL$60EBDDF2" "fm_catl"."s"@"SEL$1")
      USE_NL(@"SEL$60EBDDF2" "fm_catl"."sj"@"SEL$1")
      FULL(@"SEL$60EBDDF2" "sjc"@"SEL$1")
      FULL(@"SEL$60EBDDF2" "sj"@"SEL$1")
      USE_DAS(@"SEL$60EBDDF2" "sj"@"SEL$1")
      FULL(@"SEL$60EBDDF2" "s"@"SEL$1")
      USE_DAS(@"SEL$60EBDDF2" "s"@"SEL$1")
      SIMPLIFY_GROUP_BY(@"SEL$1")
      OUTER_TO_INNER(@"SEL$EA493D33")
      OPTIMIZER_FEATURES_ENABLE('4.2.2.0')
      END_OUTLINE_DATA
  */
Optimization Info:
-------------------------------------
  sjc:
      table_rows:587477
      physical_range_rows:587850
      logical_range_rows:587608
      index_back_rows:0
      output_rows:0
      table_dop:1
      dop_method:Table DOP
      avaiable_index_name:[IDX_spot_job_eq_id, IDX_spot_job_id, spot_job_content]
      pruned_index_name:[IDX_spot_job_eq_id]
      stats version:1722953459720139
      dynamic sampling level:0
      estimation method:[OPTIMIZER STATISTICS, STORAGE]
  sj:
      table_rows:49919
      physical_range_rows:1
      logical_range_rows:1
      index_back_rows:0
      output_rows:1
      table_dop:1
      dop_method:DAS DOP
      avaiable_index_name:[sj_si_idx, spot_job]
      pruned_index_name:[sj_si_idx]
      stats version:1722953400577250
      dynamic sampling level:0
      estimation method:[OPTIMIZER STATISTICS]
  s:
      table_rows:48472
      physical_range_rows:1
      logical_range_rows:1
      index_back_rows:0
      output_rows:0
      table_dop:1
      dop_method:DAS DOP
      avaiable_index_name:[spot_pi_idx, spot]
      pruned_index_name:[spot_pi_idx]
      stats version:1722953406520268
      dynamic sampling level:0
      estimation method:[OPTIMIZER STATISTICS]
  Plan Type:
      LOCAL
  Note:
      Degree of Parallelisim is 1 because of table property

spot 表结构：

CREATE TABLE `spot` (
  `spot_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `spot_name` varchar(255) DEFAULT NULL,
  `site_id` bigint(20) DEFAULT NULL,
  `bl_id` bigint(20) DEFAULT NULL,
  `fl_id` bigint(20) DEFAULT NULL,
  `rm_id` bigint(20) DEFAULT NULL,
  `spot_type` int(11) DEFAULT NULL,
  `nfc_tag` varchar(255) DEFAULT NULL,
  `qrcode_hash` varchar(100) DEFAULT NULL,
  `proj_id` bigint(20) NOT NULL,
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  `created_date` datetime DEFAULT NULL,
  `created_by` varchar(50) DEFAULT NULL,
  `creator` varchar(50) DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
  `modified_by` varchar(50) DEFAULT NULL,
  `modifier` varchar(50) DEFAULT NULL,
  `version` int(11) DEFAULT '0',
  PRIMARY KEY (`spot_id`),
  KEY `spot_pi_idx` (`proj_id`)
) AUTO_INCREMENT = 96936 DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC

我想让 s表走 spot_pi_idx 索引，加了hint 生成执行计划如下：

sql :

SELECT /*+index(s spot_pi_idx)*/
  COUNT(1)
FROM
  spot_job_content sjc
  LEFT JOIN spot_job sj ON sj.spot_job_id = sjc.spot_job_id
  LEFT JOIN spot s ON sj.spot_id = s.spot_id
WHERE
  s.proj_id = 49
  AND sjc.modified_date >= '2024-08-07 08:45:53.143'

执行计划：

=====================================================================
|ID|OPERATOR                   |NAME          |EST.ROWS|EST.TIME(us)|
---------------------------------------------------------------------
|0 |SCALAR GROUP BY            |              |1       |21311       |
|1 |└─NESTED-LOOP JOIN         |              |0       |21311       |
|2 |  ├─NESTED-LOOP JOIN       |              |0       |21290       |
|3 |  │ ├─TABLE FULL SCAN      |sjc           |0       |21269       |
|4 |  │ └─DISTRIBUTED TABLE GET|sj            |1       |21          |
|5 |  └─DISTRIBUTED TABLE GET  |s(spot_pi_idx)|1       |21          |
=====================================================================
Outputs & filters:
-------------------------------------
  0 - output([T_FUN_COUNT(*)(0x7ec41ec29f50)]), filter(nil), rowset=16
      group(nil), agg_func([T_FUN_COUNT(*)(0x7ec41ec29f50)])
  1 - output(nil), filter(nil), rowset=16
      conds(nil), nl_params_([sj.spot_id(0x7ec41ec263d0)(:2)]), use_batch=false
  2 - output([sj.spot_id(0x7ec41ec263d0)]), filter(nil), rowset=16
      conds(nil), nl_params_([sjc.spot_job_id(0x7ec41ec22da0)(:0)]), use_batch=false
  3 - output([sjc.spot_job_id(0x7ec41ec22da0)]), filter([sjc.modified_date(0x7ec41ec28dc0) >= INTERNAL_FUNCTION('2024-08-07 08:45:53.143', 114, 17)(0x7ec41ec2b260)(0x7ec41ec28630)]), rowset=16
      access([sjc.spot_job_id(0x7ec41ec22da0)], [sjc.modified_date(0x7ec41ec28dc0)]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([sjc.spot_job_content_id(0x7ec41ec2adf0)]), range(MIN ; MAX)always true
  4 - output([sj.spot_id(0x7ec41ec263d0)]), filter(nil), rowset=16
      access([sj.spot_id(0x7ec41ec263d0)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([sj.spot_job_id(0x7ec41ec22a70)]), range(MIN ; MAX), 
      range_cond([sj.spot_job_id(0x7ec41ec22a70) = :0(0x7ec41ed804c0)])
  5 - output(nil), filter(nil), rowset=16
      access(nil), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([s.proj_id(0x7ec41ec27a60)], [s.spot_id(0x7ec41ec26700)]), range(MIN ; MAX), 
      range_cond([s.proj_id(0x7ec41ec27a60) = 49(0x7ec41ec272d0)], [:2 = s.spot_id(0x7ec41ec26700)(0x7ec41ece3900)])
Used Hint:
-------------------------------------
  /*+
      
      INDEX("s" "spot_pi_idx")
  */
Qb name trace:
-------------------------------------
  stmt_id:0, stmt_type:T_EXPLAIN 
  stmt_id:1, SEL$1 > SEL$EA493D33 > SEL$60EBDDF2
Outline Data: 
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      LEADING(@"SEL$60EBDDF2" (("fm_catl"."sjc"@"SEL$1" "fm_catl"."sj"@"SEL$1") "fm_catl"."s"@"SEL$1"))
      USE_NL(@"SEL$60EBDDF2" "fm_catl"."s"@"SEL$1")
      USE_NL(@"SEL$60EBDDF2" "fm_catl"."sj"@"SEL$1")
      FULL(@"SEL$60EBDDF2" "sjc"@"SEL$1")
      FULL(@"SEL$60EBDDF2" "sj"@"SEL$1")
      USE_DAS(@"SEL$60EBDDF2" "sj"@"SEL$1")
      INDEX(@"SEL$60EBDDF2" "s"@"SEL$1" "spot_pi_idx")
      USE_DAS(@"SEL$60EBDDF2" "s"@"SEL$1")
      SIMPLIFY_GROUP_BY(@"SEL$1")
      OUTER_TO_INNER(@"SEL$EA493D33")
      OPTIMIZER_FEATURES_ENABLE('4.2.2.0')
      END_OUTLINE_DATA
  */
Optimization Info:
-------------------------------------
  sjc:
      table_rows:587477
      physical_range_rows:587928
      logical_range_rows:587634
      index_back_rows:0
      output_rows:0
      table_dop:1
      dop_method:Table DOP
      avaiable_index_name:[IDX_spot_job_eq_id, IDX_spot_job_id, spot_job_content]
      pruned_index_name:[IDX_spot_job_eq_id]
      stats version:1722953459720139
      dynamic sampling level:0
      estimation method:[OPTIMIZER STATISTICS, STORAGE]
  sj:
      table_rows:49919
      physical_range_rows:1
      logical_range_rows:1
      index_back_rows:0
      output_rows:1
      table_dop:1
      dop_method:DAS DOP
      avaiable_index_name:[sj_si_idx, spot_job]
      pruned_index_name:[sj_si_idx]
      stats version:1722953400577250
      dynamic sampling level:0
      estimation method:[OPTIMIZER STATISTICS]
  s:
      table_rows:48472
      physical_range_rows:1
      logical_range_rows:1
      index_back_rows:0
      output_rows:1
      table_dop:1
      dop_method:DAS DOP
      avaiable_index_name:[spot_pi_idx, spot]
      pruned_index_name:[spot]
      stats version:1722953406520268
      dynamic sampling level:0
      estimation method:[OPTIMIZER STATISTICS]
  Plan Type:
      LOCAL
  Note:
      Degree of Parallelisim is 1 because of table property

绑定 outline


create or replace outline otl_4A0528CE227AE2371D316F3C1AAB9A4E on '4A0528CE227AE2371D316F3C1AAB9A4E' using hint
 /*+
      BEGIN_OUTLINE_DATA
      LEADING(@"SEL$60EBDDF2" (("fm_catl"."sjc"@"SEL$1" "fm_catl"."sj"@"SEL$1") "fm_catl"."s"@"SEL$1"))
      USE_NL(@"SEL$60EBDDF2" "fm_catl"."s"@"SEL$1")
      USE_NL(@"SEL$60EBDDF2" "fm_catl"."sj"@"SEL$1")
      FULL(@"SEL$60EBDDF2" "sjc"@"SEL$1")
      FULL(@"SEL$60EBDDF2" "sj"@"SEL$1")
      USE_DAS(@"SEL$60EBDDF2" "sj"@"SEL$1")
      INDEX(@"SEL$60EBDDF2" "s"@"SEL$1" "spot_pi_idx")
      USE_DAS(@"SEL$60EBDDF2" "s"@"SEL$1")
      SIMPLIFY_GROUP_BY(@"SEL$1")
      OUTER_TO_INNER(@"SEL$EA493D33")
      OPTIMIZER_FEATURES_ENABLE('4.2.2.0')
      END_OUTLINE_DATA
  */

查看sql 执行计划：

===========================================================
|ID|OPERATOR                   |NAME|EST.ROWS|EST.TIME(us)|
-----------------------------------------------------------
|0 |SCALAR GROUP BY            |    |1       |21311       |
|1 |└─NESTED-LOOP JOIN         |    |0       |21311       |
|2 |  ├─NESTED-LOOP JOIN       |    |0       |21290       |
|3 |  │ ├─TABLE FULL SCAN      |sjc |0       |21269       |
|4 |  │ └─DISTRIBUTED TABLE GET|sj  |1       |21          |
|5 |  └─DISTRIBUTED TABLE GET  |s   |1       |21          |
===========================================================
Outputs & filters:
-------------------------------------
  0 - output([T_FUN_COUNT(*)(0x7ec0d10299c0)]), filter(nil), rowset=16
      group(nil), agg_func([T_FUN_COUNT(*)(0x7ec0d10299c0)])
  1 - output(nil), filter(nil), rowset=16
      conds(nil), nl_params_([sj.spot_id(0x7ec0d1025e40)(:2)]), use_batch=false
  2 - output([sj.spot_id(0x7ec0d1025e40)]), filter(nil), rowset=16
      conds(nil), nl_params_([sjc.spot_job_id(0x7ec0d1022810)(:0)]), use_batch=false
  3 - output([sjc.spot_job_id(0x7ec0d1022810)]), filter([sjc.modified_date(0x7ec0d1028830) >= INTERNAL_FUNCTION('2024-08-07 08:45:53.143', 114, 17)(0x7ec0d102acd0)(0x7ec0d10280a0)]), rowset=16
      access([sjc.spot_job_id(0x7ec0d1022810)], [sjc.modified_date(0x7ec0d1028830)]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([sjc.spot_job_content_id(0x7ec0d102a860)]), range(MIN ; MAX)always true
  4 - output([sj.spot_id(0x7ec0d1025e40)]), filter(nil), rowset=16
      access([sj.spot_id(0x7ec0d1025e40)]), partitions(p0)
      is_index_back=false, is_global_index=false, 
      range_key([sj.spot_job_id(0x7ec0d10224e0)]), range(MIN ; MAX), 
      range_cond([sj.spot_job_id(0x7ec0d10224e0) = :0(0x7ec0d1180e90)])
  5 - output(nil), filter([s.proj_id(0x7ec0d10274d0) = 49(0x7ec0d1026d40)]), rowset=16
      access([s.proj_id(0x7ec0d10274d0)]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
      range_key([s.spot_id(0x7ec0d1026170)]), range(MIN ; MAX), 
      range_cond([:2 = s.spot_id(0x7ec0d1026170)(0x7ec0d1181760)])
Used Hint:
-------------------------------------
  /*+
      
  */
Qb name trace:
-------------------------------------
  stmt_id:0, stmt_type:T_EXPLAIN 
  stmt_id:1, SEL$1 > SEL$EA493D33 > SEL$60EBDDF2
Outline Data: 
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      LEADING(@"SEL$60EBDDF2" (("fm_catl"."sjc"@"SEL$1" "fm_catl"."sj"@"SEL$1") "fm_catl"."s"@"SEL$1"))
      USE_NL(@"SEL$60EBDDF2" "fm_catl"."s"@"SEL$1")
      USE_NL(@"SEL$60EBDDF2" "fm_catl"."sj"@"SEL$1")
      FULL(@"SEL$60EBDDF2" "sjc"@"SEL$1")
      FULL(@"SEL$60EBDDF2" "sj"@"SEL$1")
      USE_DAS(@"SEL$60EBDDF2" "sj"@"SEL$1")
      FULL(@"SEL$60EBDDF2" "s"@"SEL$1")
      USE_DAS(@"SEL$60EBDDF2" "s"@"SEL$1")
      SIMPLIFY_GROUP_BY(@"SEL$1")
      OUTER_TO_INNER(@"SEL$EA493D33")
      OPTIMIZER_FEATURES_ENABLE('4.2.2.0')
      END_OUTLINE_DATA
  */
Optimization Info:
-------------------------------------
  sjc:
      table_rows:587477
      physical_range_rows:587928
      logical_range_rows:587634
      index_back_rows:0
      output_rows:0
      table_dop:1
      dop_method:Table DOP
      avaiable_index_name:[IDX_spot_job_eq_id, IDX_spot_job_id, spot_job_content]
      pruned_index_name:[IDX_spot_job_eq_id]
      stats version:1722953459720139
      dynamic sampling level:0
      estimation method:[OPTIMIZER STATISTICS, STORAGE]
  sj:
      table_rows:49919
      physical_range_rows:1
      logical_range_rows:1
      index_back_rows:0
      output_rows:1
      table_dop:1
      dop_method:DAS DOP
      avaiable_index_name:[sj_si_idx, spot_job]
      pruned_index_name:[sj_si_idx]
      stats version:1722953400577250
      dynamic sampling level:0
      estimation method:[OPTIMIZER STATISTICS]
  s:
      table_rows:48472
      physical_range_rows:1
      logical_range_rows:1
      index_back_rows:0
      output_rows:0
      table_dop:1
      dop_method:DAS DOP
      avaiable_index_name:[spot_pi_idx, spot]
      pruned_index_name:[spot_pi_idx]
      stats version:1722953406520268
      dynamic sampling level:0
      estimation method:[OPTIMIZER STATISTICS]
  Plan Type:
      LOCAL
  Note:
      Degree of Parallelisim is 1 because of table property

可以看到sql 并没有按照预期走执行计划。

chwei · 2024 年8 月 7 日 09:29

你先确定一下你绑定的outline是不是真的生效了，可以查看 DBA_OB_OUTLINES视图，再确定是否生成了新的执行计划

具体可以参考:OceanBase分布式数据库-海量数据笔笔算数

撑伞接落花 · 2024 年8 月 7 日 09:55

select
  a.sql_id,
  a.outline_name,
  a.outline_id,
  b.outline_data,
  b.QUERY_SQL  from  
  oceanbase.DBA_OB_OUTLINES a,
  oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT b
where
  a.sql_id = b.sql_id
  and a.sql_id = '4A0528CE227AE2371D316F3C1AAB9A4E'
  and outline_name = 'otl_4A0528CE227AE2371D316F3C1AAB9A4E'

可以看到 outline_id = 500582 是生效的，但是 sql 就是不按照outline 绑定的执行计划走，还有什么方法可以实现？

辞霜 · 2024 年8 月 7 日 10:19

sql有再次执行过么

撑伞接落花 · 2024 年8 月 7 日 12:51

有在次执行过了，执行计划也看过了。跟预期不一样

Royce1220 · 2024 年8 月 7 日 13:41

看一下真实的执行计划，之前有遇到过绑定之后explain extended还是原来的计划，但是真实计划其实是绑定之后的

皇甫侯 · 2024 年8 月 7 日 15:17

用 explain 看不了真实的执行计划一直都是预期的，你直接explain加了hint的SQL看看是不是你想要的执行计划

撑伞接落花 · 2024 年8 月 7 日 16:44

这个也看不出来是不是 hint 是否生效。explian hint sql 那肯定是按照hint 的执行计划的。

撑伞接落花 · 2024 年8 月 7 日 16:44

真实的执行计划是怎么看的？我目前都是 explain sql 的

Royce1220 · 2024 年8 月 7 日 16:55

先通过执行sql找到sql trace_id,通过trace_id访问gv$ob_sql_audit获取svr_ip,svr_port,tenant_id,plan_id四要素，再通过四要素去查GV$OB_PLAN_CACHE_PLAN_EXPLAIN

Royce1220 · 2024 年8 月 7 日 16:59

还有一种验证outline是否生效的方法是查询GV$OB_PLAN_CACHE_PLAN_STAT 的outline_id字段
如果不为-1，那就是outline生效了，官方有说明explain展示的可能不是outline绑定的执行计划
参考https://www.oceanbase.com/knowledge-base/oceanbase-database-20000001057?back=kb

皇甫侯 · 2024 年8 月 7 日 20:15

没生效。。？你是不是用的Mysql客户端忘记加 -c 了，正常可以在Used Hint中看到hint是否生效

撑伞接落花 · 2024 年8 月 7 日 20:41

我是在odc 中查看的

皇甫侯 · 2024 年8 月 7 日 22:07

obclient中试下呢

辞霜 · 2024 年8 月 8 日 17:49

你好，请问参照楼上的建议使用黑屏化查看了么，如还存在这个问题这边帮你联系下相关同学。

shiyh · 2024 年8 月 8 日 20:40

outline有没有生效，是看GV$OB_PLAN_CACHE_PLAN_STAT视图中的OUTLINE_ID是否为-1？

如果为-1，则表示outline没有生效。
如果GV$OB_PLAN_CACHE_PLAN_STAT视图中的OUTLINE_ID与DBA_OB_OUTLINES中的OUTLINE_ID相同，则表示outline生效。

你使用的查询语句，只展示了DBA_OB_OUTLINES中的OUTLINE_ID，没有看到GV$OB_PLAN_CACHE_PLAN_STAT视图中的OUTLINE_ID信息。

撑伞接落花 · 2024 年8 月 9 日 22:29

按照如上方法查看了下，outline_id 非-1 ,说明生效了。
odc 查看执行计划不准，这个缺失始料未及的。

shiyh · 2024 年8 月 10 日 07:45

ODC显示的执行计划是估算的，不是真实的执行计划。真实的执行计划看GV$OB_PLAN_CACHE_PLAN_EXPLAIN视图。