问题现象
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 固定计划失败，频繁出现的几种代价改写，如下表格中罗列的内容。
| 问题场景 | 解决的 Hint |
|---|---|
| or 展开 | USE_CONCAT / NO_EXPAND |
| semi join 改写为 inner join | 强制触发与禁止触发 Hint：UNNEST / NO_UNNEST |
| group by pullup | 将内联视图中 group by 上拉到连接后，需要在内联视图中添加 Hint MERGE/NO_MERGE 控制 |
| group by pushdown | 将 group by 下推到连接下，需要添加 Hint PLACE_GROUP_BY / NO_PLACE_GROUP_BY 到 group by 层 query block |
问题的风险及影响
部分基于代价的改写，导致计划绑定失效，SQL 执行不稳定。
影响租户
影响 OceanBase 数据库中的 SYS 租户和 Oracle 租户以及 MySQL 租户。
适用版本
OceanBase 数据库 V3.x 版本。
解决方法
使用上正确的 Hint，生成对应的执行计划，并加对应的 outline_data 绑定给对应的 SQL。
以本案例中的 group by pullup 改写为例，可以参考如下步骤解决/验证问题。
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;根据步骤 1 中的 trace_id 获取 sql_id。
select sql_id from gv$sql_audit where trace_id='xxxxx';根据步骤 2 的 sql_id，在 gv$plan_cache_plan_stat 中获取对应的 outline_data。
select outline_data from gv$plan_cache_plan_stat where sql_id='xxxxx'\G;执行原始 SQL 获取 sql_id。
对步骤 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*/;查看
gv$outline中对应的 outline 是否正确。清空原始 SQL 的 plan cache，重新执行原始 SQL，查看在
gv$plan_cache_plan_stat中，对应 sql_id 的 outline 是否已经跟gv$outline中一致的 outline。另外也可以通过 explain extened 原始 SQL，查看到计划中 group by 上拉到 2 表合并之后。
