Cost Model 基于真实基数的代价计算是否可以显示

[Gone222]

【 使用环境 】测试环境
【 OB or 其他组件 】OB
【 使用版本 】4.2.0
【问题描述】清晰明确描述问题
我想咨询一下，OceanBase 是否支持获悉类似 pg_hint_plan插件外部注入真实基数或者实际执行 SQL 得到根据真实运行的基数计算出来的 cost model 的计算代价，不是基于预估的基数的，这样可以便于了解到底是基数预估的问题，还是代价模型本身的问题。像 TiDB 是支持把代价模型的公式也在执行计划中显示出来。
【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

【SOP系列 22 】——故障诊断第一步(自助诊断和诊断信息收集)

【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！

[论坛小助手]

您的问题非常合理且具有技术深度。目前，OceanBase 数据库并没有直接提供类似于 PostgreSQL 的 pg_hint_plan 插件的功能，即允许用户通过 hint 注入真实基数来重新计算执行计划的代价。不过，我们可以探讨一些间接的方法来帮助您更好地理解执行计划的成本计算，并判断是基数预估的问题还是代价模型本身的问题。

间接方法

1. 查看执行计划 (Explain Plan)

   • 使用 EXPLAIN 命令查看 SQL 的执行计划。这将展示出基于当前统计信息的预估成本。
   • 示例命令:

     EXPLAIN SELECT * FROM table_name;
     

2. 分析实际执行情况

   • 使用 DBMS_XPLAN 包中的 DISPLAY_CURSOR 子程序来查看已执行的查询计划详情，这可以帮助您了解实际执行时的情况。
   • 示例命令:

     SELECT DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'your_sql_id');
     

3. 监控执行计划

   • 利用 GV$SQL_PLAN_MONITOR 视图来获取租户级别的执行计划相关信息，这有助于理解实际执行时的性能表现。
   • 示例查询:

     SELECT * FROM GV$SQL_PLAN_MONITOR WHERE SQL_ID = 'your_sql_id';
     

4. 计划缓存视图

   • 使用 GV$OB_PLAN_CACHE_PLAN_EXPLAIN 视图来查询某条 SQL 在计划缓存中的物理执行计划，这可以提供更详细的执行计划信息。
   • 示例查询:

     SELECT plan_line_id, operator, name, rows, cost
     FROM oceanbase.gv$plan_cache_plan_explain
     WHERE ip = 'your_ip'
     AND port = your_port
     AND tenant_id = your_tenant_id
     AND plan_id = your_plan_id;
     

分析思路

• 对比预估与实际成本
  • 比较 EXPLAIN 输出的预估成本与 GV$SQL_PLAN_MONITOR 中的实际成本，以此判断预估成本是否准确。
• 观察计划变化
  • 如果发现计划在不同时间点发生变化，可以通过 DBMS_XPLAN.DISPLAY_CURSOR 来观察这些变化，进一步判断是否因为基数预估的变化导致了计划的选择差异。
• 使用计划绑定
  • 如果确定了某个执行计划更为稳定且高效，可以考虑使用计划绑定来固定这个计划，避免因代价模型的波动而影响性能。

结论

虽然 OceanBase 目前没有直接提供基于真实基数的代价计算显示功能，但通过上述方法您可以较为全面地了解执行计划的成本计算情况，并判断问题所在。如果在实践中遇到具体问题，建议联系 OceanBase 的技术支持团队获取更深入的帮助。

希望以上信息对您有所帮助！如果有更具体的需求或疑问，请随时告知。

具体信息可参考以下文档:

• SQL 调优常见问题
• SQL FAQ
• 计划缓存相关视图
• SQL 执行计划
• DISPLAY_SQL_PLAN_BASELINE
• DBMS_XPLAN 概述
• 实时执行计划展示
• 基于代价的查询改写

[Gone222]

@论坛小助手

ELECT plan_line_id, operator, name, rows, cost
FROM oceanbase.gv$plan_cache_plan_explain
WHERE ip = 'your_ip'
AND port = your_port
AND tenant_id = your_tenant_id
AND plan_id = your_plan_id;

SELECT plan_line_id, operator, name, rows, cost
FROM oceanbase.gv$plan_cache_plan_explain
WHERE ip = ‘your_ip’
AND port = your_port
AND tenant_id = your_tenant_id
AND plan_id = your_plan_id; 这里得到的 cost 就是基于真实基数实际计算的 cost 吗？ rows 就是实际扫描出来的真实基数。

[Gone222]

@论坛小助手

GV$SQL_PLAN_MONITOR

社区版也支持吗？DBMS_XPLAN是默认都有的嘛

[Gone222]

以及这里如何获取这么多 id 的流程可否也详细介绍一下。

[论坛小助手]

针对您的问题，我们将逐一进行解答：

如何获取 GV$SQL_PLAN_MONITOR 中的 SQL_ID 和其他 ID

1. 获取 SQL_ID

   • 执行 SQL 语句后，可以通过 V$SQL 视图来获取 SQL_ID。
   • 示例查询:

     SELECT sql_id FROM V$SQL WHERE sql_text LIKE '%your_sql_statement%';
     

2. 获取 PLAN_ID

   • 首先，需要从 V$OB_PLAN_CACHE_PLAN_STAT 视图中获取 PLAN_ID。
   • 示例查询:

     SELECT plan_id FROM V$OB_PLAN_CACHE_PLAN_STAT WHERE tenant_id = your_tenant_id AND statement LIKE '%your_sql_statement%';
     

3. 获取 IP 和 PORT

   • 这些信息通常指的是执行 SQL 的 OceanBase 服务器的 IP 地址和端口号。可以从 V$OB_PLAN_CACHE_PLAN_STAT 视图中获取。
   • 示例查询:

     SELECT svr_ip, svr_port FROM V$OB_PLAN_CACHE_PLAN_STAT WHERE tenant_id = your_tenant_id AND statement LIKE '%your_sql_statement%';
     

关于 GV$SQL_PLAN_MONITOR

• GV$SQL_PLAN_MONITOR 视图提供了 SQL 语句执行过程中的实时监控数据，包括执行计划的详细信息、执行时间、读取的行数等。
• 社区版支持情况：GV$SQL_PLAN_MONITOR 视图在社区版中也是可用的，但请注意，某些高级特性可能会在企业版中才有。
• 如何使用：可以使用如下命令来查询 GV$SQL_PLAN_MONITOR，以获取 SQL 语句的执行详情。

  SELECT * FROM GV$SQL_PLAN_MONITOR WHERE SQL_ID = 'your_sql_id';
  

关于 DBMS_XPLAN

• DBMS_XPLAN 是一个 PL/SQL 包，用于展示 SQL 语句的执行计划。
• 是否默认存在：DBMS_XPLAN 在 OceanBase 数据库中是默认提供的，无需额外安装。
• 使用示例：使用 DBMS_XPLAN.DISPLAY_CURSOR 来查看 SQL 语句的执行计划。

  SELECT DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'your_sql_id');
  

关于 GV$OB_PLAN_CACHE_PLAN_EXPLAIN

• GV$OB_PLAN_CACHE_PLAN_EXPLAIN 视图提供了存储在计划缓存中的执行计划的详细信息。
• 获取 COST 和 ROWS
  • COST 字段表示物理算子预估的代价，它基于统计信息进行计算，并非基于实际运行时的数据。
  • ROWS 字段表示物理算子预估的结果行数，同样基于统计信息而非实际运行时的数据。
  • 因此，这里的 COST 和 ROWS 并不是基于实际运行时的真实基数计算得出的，而是基于预估的基数进行计算的。

总结

• GV$SQL_PLAN_MONITOR 和 DBMS_XPLAN 提供了 SQL 语句执行过程中的实时监控数据和执行计划详情，这些信息可以帮助您了解 SQL 语句的实际执行情况。
• GV$OB_PLAN_CACHE_PLAN_EXPLAIN 中的 COST 和 ROWS 基于统计信息进行计算，而不是基于实际运行时的真实基数。

如果您有更具体的需求或疑问，请随时告知。

具体信息可参考以下文档:

• 计划缓存相关视图
• 实时执行计划展示

(小助手的答复已结束，如未能解决您的问题，请继续提问并等待其他同学的回复，谢谢！)

[Gone222]

未解决，我需要知道基于真实基数的 cost

[zmix]

@论坛小助手

DBMS_XPLAN

有这种方法，修改统计信息基数，应该要企业版也支持，Oracle也是这样

[Gone222]

@Gone222

SELECT plan_line_id, operator, name, rows, cost
FROM oceanbase.gv$plan_cache_plan_explain
WHERE ip = ‘your_ip’
AND port = your_port
AND tenant_id = your_tenant_id
AND plan_id = your_plan_id; 这里得到的 cost 就是基于真实基数实际计算的 cost 吗？ rows 就是实际扫描出来的真实基数。

SELECT plan_line_id, operator, name, rows, cost
FROM oceanbase.gv$plan_cache_plan_explain
WHERE ip = ‘your_ip’
AND port = your_port
AND tenant_id = your_tenant_id
AND plan_id = your_plan_id; 这里得到的 cost 就是基于真实基数实际计算的 cost 吗？ rows 就是实际扫描出来的真实基数。这个问题可以补充回复一下吗？

[zmix]

预估的

[zmix]

(G)V$OB_SQL_PLAN
可以看下这个视图
实时执行计划展示-V4.3.2-OceanBase 数据库文档-分布式数据库使用文档

[辞霜]

gv$plan_cache_plan_explain 该表获取的信息都是跟进统计信息预估出来的，楼上小助手也回答出来了

• COST 字段表示物理算子预估的代价，它基于统计信息进行计算，并非基于实际运行时的数据。
• ROWS 字段表示物理算子预估的结果行数，同样基于统计信息而非实际运行时的数据。

[Gone222]

所以想知道真实的就是上面这个回复对吧：https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001050820，real_cost和 real_cardinality。如果是这样的话，那我没什么额外的问题了，谢谢。

[辞霜]

是的

[Gone222]

你好，我想再补充确认一下，这里的 real_cost就是 OB 基于 real_cardinality和 cost model 的公式计算出来的值对吗？