SQL 语句 like 子句的转义符 escape 不能参数化导致无法命中 plan cache 的原因和解决方法

问题现象

一条业务 SQL 生成了多个执行计划，不复用已有执行计划，找到具体的 sql_id 的分析 sql audit 发现有些执行计划是可以 hit 命中计划并且统计计划的命中情况 hit_count>1，怀疑 SQL 包含 where ((a.CUST_NAME LIKE ? escape '\\'，可能影响执行计划生成。

关键诊断信息

触发条件

1. 普通客户端使用文本协议 SQL 中包含 LIKE '%xxx%' escape '\' 这种 SQL 不能参数化。

2. JDBC 连接或者 OBCI 连接使用 ps 协议能参数化成功但是参数变化任然不能命中 plan cache。

事前巡检

SQL 中包含 LIKE '%xxx%' escape '\'，escape 后面也可以是用户自定义一个转义符。

事后诊断

如果发现 SQL 中包含 LIKE '%xxx%' escape '\' 这种 SQL 不能参数化，在 v$ob_plan_cache_plan_stat 视图里的 special_param 字段会记录不能参数化参数值 如果发现参数变化不能命中 plan cache ，如果参数稳定就能命中计划，大概会命中此问题。

问题原因

SQL 中包含 LIKE '%xxx%' escape '\' 这种 SQL 不能参数化，参数变化不能命中 plan cache 是已知的问题，是当前数据库内核上实现上的限制。

##-- 使用场景
SQL 语句 like 子句使用 转义符 escape 的场景：如果想在 SQL LIKE 里查询有下划线 '_' 或是 '%' 等值的记录，直接写成 like 'xxx_xxx'，则会把 '_' 当成是 like 的通配符。
SQL 里提供了 escape 子句来处理这种情况，escape 可以指定 like 中使用的转义符是什么 LIKE '%xxx%' escape '_'，而在转义符后的字符将被当成原始字符。

查询 SQL，如下。

-- 在 v$ob_plan_cache_plan_stat 视图里的 special_param 字段会记录不能参数化参数值放这里
SELECT plan_id, svr_ip, query_sql, is_hit_plan from gv$OB_SQL_AUDIT where sql_id='xxxxxxxx';
select * from v$ob_plan_cache_plan_stat where plan_id = 1254;

问题的风险及影响

SQL 中包含 LIKE '%xxx%' escape '\' 这种 SQL 不能参数化，如果参数变化则不能命中 plan cache。

适用版本

OceanBase 数据库 V3.x、V4.x 版本。

解决方法

当前数据库内核上实现上的限制，所有版本存在此问题，建议规避这种写法了 LIKE '%xxx%' escape '\' 比如去掉 escape '\'。

规避方式

规避这种写法了 LIKE '%xxx%' escape '\' 建议尝试改 SQL 去掉 escape '\'。