云栈社区»论坛 › 技术文档「 Note & Doc 」 › FORMAT OUTLINE 实战：解决 OceanBase SQL_ID 不一致的执行计划 ...

发回帖发新帖

140 积分	0 好友	18 主题

发消息

FORMAT OUTLINE 实战：解决 OceanBase SQL_ID 不一致的执行计划绑定问题

发表于前天 01:52 | 查看: 11| 回复: 0

一、引言：运维痛点与解决方案背景

在 OceanBase 数据库运维过程中，执行计划跳变是导致查询性能波动的核心问题之一。为避免计划异常变更，常规方案是通过 OUTLINE（执行计划大纲）将最优执行计划“固化”到目标 SQL 上。然而，这一方案存在关键局限：OUTLINE 的绑定依赖于 SQL_ID，而 SQL_ID 是通过对 SQL 文本进行 MD5 哈希计算生成的——即使 SQL 语义完全一致，仅因多一个空格、大小写差异、IN 列表值数量变化或换行符，就会生成不同的 SQL_ID，导致常规 OUTLINE 无法覆盖这类“相似 SQL”。

为解决这一痛点，OceanBase 推出 FORMAT OUTLINE 功能。该功能通过对 SQL 进行“模糊化处理”生成统一的 FORMAT_SQL，使语义相似但 SQL_ID 不同的 SQL 共享同一个 OUTLINE，彻底解决“一 SQL 一绑定”的运维负担。

二、FORMAT OUTLINE 核心能力解析

2.1 功能定义

FORMAT OUTLINE 是 OceanBase 针对“相似 SQL 执行计划统一”场景设计的增强型 OUTLINE 功能。它通过对 SQL 文本进行结构归一化处理，生成统一的 FORMAT_SQL 和对应的 FORMAT_SQL_ID（FORMAT_SQL 的 MD5 值），最终将 OUTLINE 绑定到 FORMAT_SQL_ID 上，实现“一类 SQL 共享一个执行计划”。

2.2 典型应用场景

当业务中存在大量“语义一致、文本细节差异”的 SQL 时，FORMAT OUTLINE 可发挥关键作用，例如：

IN 列表值数量变化：select * from t where c1 in (1,2)、select * from t where c1 in (1,2,3)
空格/换行符差异：select * from t where c1=1、select * from t where c1=1（多空格）
大小写差异：SELECT * FROM t、select * from t

以上 SQL 的 SQL_ID 不同，但通过 FORMAT OUTLINE 处理后，会共享同一个执行计划。

三、FORMAT OUTLINE 的核心处理逻辑

FORMAT OUTLINE 的本质是对 SQL 文本进行“语义提取 + 结构归一化”，忽略非语义相关的差异，具体逻辑如下表所示：

处理逻辑	详细说明	示例
忽略参数变化	对 SQL 中的“可变参数”（如 WHERE 条件中的具体值）进行占位符替换，仅保留参数位置，不关注参数内容	`select * from t where c1=1` 和 `select * from t where c1=2` 归一化为 `select * from t where c1=?`
忽略大小写差异	因 SQL 语法本身不区分大小写，统一将 SQL 文本转为大写（或小写）处理，消除大小写导致的文本差异	`SELECT * FROM t` 和 `select * from t` 统一为 `SELECT * FROM t`
忽略非语法符号差异	过滤掉 SQL 中不影响语义的符号，包括：多空格、前后空格、换行符（\n）、制表符（\t）等	`select * from t where c1 in (1,2)` 和 `select * from t where c1 in (1,2)`（多空格）归一化为同一结构
IN 表达式归一化	对 IN 列表进行特殊处理：无论 IN 后的值数量多少，均统一替换为 `in (?)`，仅保留 IN 的语法结构	`select * from t where c1 in (1,2)`、`select * from t where c1 in (1,2,3,4)` 均归一化为 `select * from t where c1 in (?)`

注意：此处的“归一化”与机器学习中的“数值缩放（如 [0,1] 区间）”无关，仅指 SQL 结构的统一化，避免因 IN 列表长度等细节导致的 SQL_ID 差异。

四、使用限制与关键注意事项

在生产环境中使用 FORMAT OUTLINE 前，需明确以下限制和优先级规则，避免绑定失效或异常覆盖：

4.1 基础使用限制

用户上下文限制：创建 FORMAT OUTLINE 时，必须切换到目标 SQL 所属的数据库用户下执行（如 USE test_db;），否则会因权限不足或上下文不匹配导致绑定失败。
版本支持：仅 OceanBase 425 LTS、435 LTS 及以上版本支持该功能，低版本需升级后使用。

4.2 Outline 优先级规则

当多个 OUTLINE 的 FORMAT_SQL_ID 相同时，存在明确的优先级覆盖关系：

高优先级：通过 FORMAT_SQL_TEXT 方式创建的 OUTLINE（直接基于归一化后的 SQL 文本绑定）；
低优先级：通过 FORMAT_SQL_ID 方式创建的 OUTLINE（基于 FORMAT_SQL 的 MD5 值绑定）。

生产建议：优先使用 FORMAT_SQL_ID 方式创建 OUTLINE。原因是：FORMAT_SQL_ID 是 FORMAT_SQL_TEXT 的 MD5 值（32 位固定长度），相比长文本匹配更高效，且能避免因文本长度过长导致的存储或匹配性能问题。

4.3 其他注意事项

绑定前需确认 FORMAT_SQL_ID 唯一性：确保同一 FORMAT_SQL_ID 仅对应一类语义一致的 SQL，避免误绑定到无关 SQL；
HINT 合法性校验：USING HINT 后指定的优化器提示（如 parallel(4)）需符合 OceanBase 语法，否则 OUTLINE 创建成功但执行计划不生效；
计划缓存刷新：创建 FORMAT OUTLINE 后，若目标 SQL 已存在于计划缓存中，需手动刷新缓存（如执行 ALTER SYSTEM FLUSH PLAN CACHE;）或等待缓存过期，才能让新 OUTLINE 生效。

五、FORMAT OUTLINE 完整操作示例（含详细验证步骤）

以下以“统一绑定 t1 表的 IN 查询 SQL”为例，展示 FORMAT OUTLINE 的创建→验证→管理全流程，重点补充“验证环节”的细节操作与结果解读。

5.1 步骤 1：准备测试场景

假设业务中存在以下 3 条语义一致但 SQL_ID 不同的 SQL（差异点：IN 列表长度、空格数量）：

-- SQL 1：IN列表含3个值，多空格
SELECT * from t1  where c1 in( 1,2,3);
-- SQL 2：IN列表含4个值，无多余空格
SELECT * from t1 where c1 in(1,2,3,4);
-- SQL 3：IN列表含5个值，换行符
SELECT * from t1 where c1 in(1,2,3,4,5);

5.2 步骤 2：获取 FORMAT_SQL_ID

FORMAT_SQL_ID 是 FORMAT OUTLINE 的核心绑定标识，有两种获取方式（根据 SQL 是否已执行选择）：

方式 1：从 SQL 审计视图查询（适用于已执行过的 SQL）

OceanBase 的 GV$OB_SQL_AUDIT 视图记录了所有执行过的 SQL 的详细信息，包括 sql_id、query_sql 和 FORMAT_SQL_ID。通过模糊匹配 SQL 文本，可直接查询目标 SQL 的 FORMAT_SQL_ID：

SELECT
   sql_id,          -- 原始SQL_ID（各不相同）
   query_sql,       -- 原始SQL文本（含细节差异）
   FORMAT_SQL_ID    -- 归一化后的统一标识
FROM
   oceanbase.GV$OB_SQL_AUDIT
WHERE
   QUERY_SQL like 'SELECT * from t1%'  -- 模糊匹配目标SQL
   AND USER_NAME = 'test_user';        -- 过滤目标用户（可选，精准定位）

查询结果示例（3 条 SQL 的 FORMAT_SQL_ID 均为 DA73FD97F82D313ABDE0E1B547D900FE，证明已归一化）：

sql_id	query_sql	FORMAT_SQL_ID
274696E09690D23BCDFC19875F3D53353	SELECT * from t1 where c1 in( 1,2,3);	DA73FD97F82D313ABDE0E1B547D900FE
327E7C587D20FC5FC93D3A84172A5B05B	SELECT * from t1 where c1 in(1,2,3,4);	DA73FD97F82D313ABDE0E1B547D900FE
6A199C33626A6E7F1FEE49652F6E2FE8	SELECT * from t1 where c1 in(1,2,3,4,5);	DA73FD97F82D313ABDE0E1B547

方式 2：通过函数预计算（适用于未执行过的 SQL）

若 SQL 尚未在数据库中执行，可通过 statement_digest_text 函数生成 SQL 的结构摘要，再对摘要进行 MD5 哈希并转大写，得到 FORMAT_SQL_ID：

-- 步骤1：生成SQL结构摘要（自动归一化，替换可变部分）
SELECT statement_digest_text('SELECT * from t1  where c1 in( 1,2,3,4,5)');
-- 输出结果：SELECT * FROM T1 WHERE C1 IN (?) （已归一化）

-- 步骤2：对摘要计算MD5并转大写，得到FORMAT_SQL_ID
SELECT UPPER(MD5(statement_digest_text('SELECT * from t1  where c1 in( 1,2,3,4,5)')));
-- 输出结果：DA73FD97F82D313ABDE0E1B547D900FE

5.3 步骤 3：创建 FORMAT OUTLINE

使用 CREATE FORMAT OUTLINE 语句，将 OUTLINE 绑定到步骤 2 获取的 FORMAT_SQL_ID 上，并指定优化器 HINT（此处以“并行度 4”为例）：

-- 语法：CREATE FORMAT OUTLINE 自定义名称 ON 'FORMAT_SQL_ID' USING HINT '优化器提示';
CREATE FORMAT OUTLINE t1_in_query_outline ON 'DA73FD97F82D313ABDE0E1B547D900FE' USING HINT /*+ parallel(4) */;  -- 绑定并行执行HINT

执行结果：若返回 Query OK, 0 rows affected，表示 FORMAT OUTLINE 创建成功。

5.4 步骤 4：验证 FORMAT OUTLINE 绑定效果（核心环节）

验证的核心目标是：确认新执行的目标 SQL 是否自动应用了绑定的 OUTLINE 和 HINT。需通过“执行测试 SQL→查询计划缓存→关联审计记录→验证执行计划”四步完成：

(1) 执行一条新的目标 SQL（触发 OUTLINE 匹配）

选择一条未执行过的、符合归一化规则的 SQL（如 IN 列表含 12 个值），确保其能匹配到已创建的 FORMAT OUTLINE：

-- 新测试SQL：IN列表长度与之前不同，含空格差异
SELECT * from t1  where c1 in( 1,2,3,4,5,6,7,8,9,10,11,12);

(2) 从计划缓存视图查询绑定状态

OceanBase 的 GV$OB_PLAN_CACHE_PLAN_STAT 视图记录了计划缓存中的执行计划详情，包括是否应用 OUTLINE、HINT 是否生效等关键信息。执行以下查询：

-- 语法说明：通过query_sql模糊匹配，查看outline和hint相关字段
SELECT
   sql_id,                -- 新SQL的SQL_ID
   plan_id,               -- 执行计划ID（后续关联用）
   query_sql,             -- 原始SQL文本
   outline_id,            -- 绑定的OUTLINE ID（非空则表示绑定成功）
   outline_data,          -- OUTLINE的具体内容（含HINT）
   hints_info,            -- 生效的HINT列表
   hints_all_worked       -- HINT是否全部生效（YES/NO）
FROM
   oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT
WHERE
   query_sql like 'SELECT * from t1  where c1 in( 1,2,3,4,5,6,7,8,9,10,11,12)%';

预期结果解读（若出现以下信息，说明绑定成功）：

字段	预期值 / 说明
outline_id	非空（如523748），表示 SQL 关联了 OUTLINE
outline_data	包含 `PARALLEL(@"SEL$1" "t1"@"SEL$1" 4)`，对应创建时的 `parallel(4)` HINT
hints_info	包含 `/+ PARALLEL(4) /`，表示 HINT 被识别
hints_all_worked	YES，表示所有 HINT 均生效（无语法错误或冲突）

(3) 从 SQL 审计视图关联计划 ID

通过 GV$OB_SQL_AUDIT 查询刚执行的 SQL 的 TRACE_ID 和 plan_id，确认其使用的计划与步骤 4.2 中的 plan_id 一致：

-- 1. 先查询新SQL的TRACE_ID（根据执行时间和SQL文本过滤）
SELECT
   trace_id,
   plan_id,
   query_sql
FROM
   oceanbase.GV$OB_SQL_AUDIT
WHERE
   query_sql like 'SELECT * from t1  where c1 in( 1,2,3,4,5,6,7,8,9,10,11,12)%'
   AND execute_time >= NOW() - INTERVAL 5 MINUTE;  -- 过滤5分钟内的执行记录

-- 2. 用查询到的TRACE_ID进一步确认plan_id（可选，精准校验）
SELECT
   sql_id,
   plan_id,
   format_sql_id  -- 确认FORMAT_SQL_ID与绑定的一致
FROM
   oceanbase.GV$OB_SQL_AUDIT
WHERE
   trace_id = 'YB420A6631FB-00063C50C79EE828-0-0';  -- 替换为实际查询到的TRACE_ID

预期结果：查询到的 plan_id 与步骤 4.2 中的 plan_id 完全一致，且 format_sql_id 仍为 DA73FD97F82D313ABDE0E1B547D900FE，证明 SQL 匹配到了目标 FORMAT OUTLINE。

(4) 用 EXPLAIN 验证执行计划细节

通过 EXPLAIN 语句直接查看执行计划，确认 parallel(4) HINT 是否实际生效（最直观的验证方式）：

-- 对测试SQL执行EXPLAIN，查看执行计划中的并行信息
EXPLAIN SELECT * from t1  where c1 in( 1,2,3,4,5,6,7,8,9,10,11,12);

预期执行计划片段（关键看 parallel 信息）：

=============================================
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | IN-OUT | PQ Distrib |
=============================================
|   0 | SELECT STATEMENT   |      |     1 |    10 |     2   (0)| 00:00:01 |       |       |        |            |
|   1 |  PX COORDINATOR    |      |       |       |            |          |       |       |        |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |     1 |    10 |     2   (0)| 00:00:01 |       |       | P->S  | QC (RAND)  |
|   3 |    TABLE ACCESS FULL| T1   |     1 |    10 |     2   (0)| 00:00:01 |       |       | S->P  | PQ Distrib Hash |
=============================================
Note
-----
   - dynamic sampling used for this statement (level=2)
   - parallel execution (degree: 4)  -- 此处显示并行度为4，证明HINT生效

若 Note 中出现 parallel execution (degree: 4)，则完全确认 FORMAT OUTLINE 的 HINT 已实际作用于执行计划。

5.5 步骤 5：管理 FORMAT OUTLINE（查看 / 删除）

(1) 查看已创建的 FORMAT OUTLINE

通过系统视图 DBA_OB_FORMAT_OUTLINES 可查询所有 FORMAT OUTLINE 的配置信息，包括名称、FORMAT_SQL_ID、创建时间等：

SELECT
   outline_name,        -- OUTLINE名称
   format_sql_id,       -- 绑定的FORMAT_SQL_ID
   format_sql_text,     -- 归一化后的SQL文本
   create_time,         -- 创建时间
   creator              -- 创建者
FROM
   oceanbase.DBA_OB_FORMAT_OUTLINES
WHERE
   outline_name = 't1_in_query_outline';  -- 过滤目标OUTLINE

(2) 删除不需要的 FORMAT OUTLINE

若需取消绑定（如执行计划调整），可通过 DROP FORMAT OUTLINE 语句删除：

-- 语法：DROP FORMAT OUTLINE 自定义名称;
DROP FORMAT OUTLINE t1_in_query_outline;

注意：删除后需刷新计划缓存，已缓存的执行计划才会失效，新执行的 SQL 将不再应用该 OUTLINE。

六、总结与生产实践建议

FORMAT OUTLINE 通过“SQL 结构归一化”解决了 SQL_ID 不一致场景下的执行计划统一绑定问题，是 OceanBase 运维中优化“相似 SQL”性能的关键工具。结合生产实践，总结以下建议：

优先用 FORMAT_SQL_ID 绑定：相比文本绑定更高效，且避免长文本匹配的潜在问题；
绑定前先验证 FORMAT_SQL_ID：通过 GV$OB_SQL_AUDIT 或 statement_digest_text 确认 FORMAT_SQL_ID 是否唯一对应目标 SQL 类，避免误绑定；
验证需多维度确认：仅靠“创建成功”不足以判断生效，需结合计划缓存、EXPLAIN、SQL 审计记录多维度验证；
定期清理无效 OUTLINE：通过 DBA_OB_FORMAT_OUTLINES 定期检查，删除过期或无用的 FORMAT OUTLINE，减少计划缓存匹配开销。

通过以上方案，可高效解决“相似 SQL 执行计划不统一”的痛点，保障业务查询性能的稳定性。

OceanBase, FORMAT_OUTLINE, SQL, 执行计划, 数据库优化