OceanBase 数据库 V4.x 之前版本部分基于代价改写场景会导致绑定的 outline 失效的原因和解决方法

问题现象

OceanBase 数据库 V3.X 情况下，执行下面的 SQL，默认生成的计划，group by 在子查询B表。但是更好的计划是对 A，B 查询之后的结果进行 group by 操作。

原始执行 SQL。

update kfab_hxlsdj29 a
set a.hspichao='2024121741890761',
a.weihguiy='B99990',
a.weihjigo='9998',
a.weihriqi='20241217',
a.shijchuo=20241217151019577
where a.farendma = '9999'
  and a.zwlsrzzt = '0'
  and a.jiaoyirq = to_date('20241217','YYYY/MM/DD')
  and a.chenggbz = 'S'
  and a.bishuzhi = (
select count(*) as bishuzhi
from kfal_jymxzz29 b
where b.farendma = '9999'
  and b.jiaoyirq = to_date('20241217','YYYY/MM/DD')
  and b.hxlshuih = a.hxlshuih
  and b.jioyddfq = substr('20241217',-2)
)
  and rownum <= 2000;

差的计划，如下图所示。

好的计划，如下图所示。

在 OCP 中给对应的 SQL 进行计划绑定（可以通过 gv$outline 中的 outline_content 查看绑定后的计划）。绑定后的 outline_data 如下。

gv$outline -> outline_content

对于好的计划，需要优化器进行 group by pullup 的改写，对 A，B 查询之后的结果进行 group by 操作。但是优化器对于 group by pullup 的改写没有体现在上面生成的 outline 中。

优化器改写之后的正确 Hint 如下。

/*+ 
BEGIN_OUTLINE_DATA 
NO_USE_HASH_AGGREGATION(@"SEL$2") 
LEADING(@"SEL$2" ("ACDATA.A"@"SEL$2" "ACDATA.B"@"SEL$2" )) 
USE_NL(@"SEL$2" ("ACDATA.B"@"SEL$2" )) 
PQ_DISTRIBUTE(@"SEL$2" ("ACDATA.B"@"SEL$2" ) BC2HOST NONE) 
NO_USE_NL_MATERIALIZATION(@"SEL$2" ("ACDATA.B"@"SEL$2" )) 
INDEX(@"SEL$2" "ACDATA.A"@"SEL$2" "KFAB_HXLSDJ29_IDX1") 
INDEX(@"SEL$2" "ACDATA.B"@"SEL$2" "KFAL_JYMXZZ29_IDX1") 
MERGE(@"SEL$1") 
END_OUTLINE_DATA
*/

关键信息

outline 固定失效，V3.x 合并之后生成新计划。

问题原因

OceanBase 数据库 V4.x 之前版本 outline data 中没有记录优化器进行的查询改写，当发生基于代价改写会导致 outline data 固定计划失败，频繁出现的几种代价改写，如下表格中罗列的内容。

问题的风险及影响

部分基于代价的改写，导致计划绑定失效，SQL 执行不稳定。

影响租户

影响 OceanBase 数据库中的 SYS 租户和 Oracle 租户以及 MySQL 租户。

适用版本

OceanBase 数据库 V3.x 版本。

解决方法

使用上正确的 Hint，生成对应的执行计划，并加对应的 outline_data 绑定给对应的 SQL。

以本案例中的 group by pullup 改写为例，可以参考如下步骤解决/验证问题。

1. SQL 中带有 /*+ merge */ Hint，执行后查看 trace_id。

   此处 /*+ merge */ 的改写含义为：对于 count(*) 的 group by 操作从 B 表上拉到 A 和 B 表 join 之后的结果。执行的 SQL 如下所示。

   update kfab_hxlsdj29 a
   set a.hspichao='2024121741890761',
   a.weihguiy='B99990',
   a.weihjigo='9998',
   a.weihriqi='20241217',
   a.shijchuo=20241217151019577
   where a.farendma = '9999'
   and a.zwlsrzzt = '0'
   and a.jiaoyirq = to_date('20241217','YYYY/MM/DD')
   and a.chenggbz = 'S'
   and a.bishuzhi = (
   select count(*) as bishuzhi
   from kfal_jymxzz29 b
   where b.farendma = '9999'
   and b.jiaoyirq = to_date('20241217','YYYY/MM/DD')
   and b.hxlshuih = a.hxlshuih
   and b.jioyddfq = substr('20241217',-2)
   )
   and rownum <= 2000;
   

2. 根据步骤 1 中的 trace_id 获取 sql_id。

   select sql_id from gv$sql_audit where trace_id='xxxxx';
   

3. 根据步骤 2 的 sql_id，在 gv$plan_cache_plan_stat 中获取对应的 outline_data。

   select outline_data from gv$plan_cache_plan_stat where sql_id='xxxxx'\G;
   

4. 执行原始 SQL 获取 sql_id。

5. 对步骤 4 中的 sql_id 绑定步骤 3 中的 Hint。 group by 上拉的计划中，会显示 MERGE(@"SEL$1")。

   

   绑定 outline 的示例。

   create outline outline_mia on '6DBF83D6E6A78656F96C9F14C25952C5' using hint /*+ BEGIN_OUTLINE_DATA NO_USE_HASH_AGGREGATION(@"SEL$2") LEADING(@"SEL$2" ("SYS.A"@"SEL$2" "SYS.B"@"SEL$2" )) USE_NL(@"SEL$2" ("SYS.B"@"SEL$2" )) PQ_DISTRIBUTE(@"SEL$2" ("SYS.B"@"SEL$2" ) BC2HOST NONE) NO_USE_NL_MATERIALIZATION(@"SEL$2" ("SYS.B"@"SEL$2" )) INDEX(@"SEL$2" "SYS.A"@"SEL$2" "KFAB_HXLSDJ29_IDX1") INDEX(@"SEL$2" "SYS.B"@"SEL$2" "KFAL_JYMXZZ29_IDX1") MERGE(@"SEL$1") END_OUTLINE_DATA*/;
   

6. 查看 gv$outline 中对应的 outline 是否正确。

7. 清空原始 SQL 的 plan cache，重新执行原始 SQL，查看在 gv$plan_cache_plan_stat 中，对应 sql_id 的 outline 是否已经跟 gv$outline 中一致的 outline。另外也可以通过 explain extened 原始 SQL，查看到计划中 group by 上拉到 2 表合并之后。