如何分析这条SQL 慢在哪里？

OceanBase 技术问题
#1

【 使用环境 】生产环境 or 测试环境 生产
【 OB or 其他组件 】 4.0
【 使用版本 】 4.1.8
【问题描述】清晰明确描述问题
【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

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

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

#3

根据文档库中的信息，要分析一条 SQL 语句慢的原因，可以按照以下步骤进行：

  1. 确认各阶段耗时占比

  2. 检查是否为慢查询

  3. 使用 GV$OB_SQL_AUDIT 视图分析具体耗时

    • 如果确认慢在 observer 模块，则可以通过 oceanbase.gv$ob_sql_audit 分析具体是 observer 内的什么阶段耗时长了。
    • 关键字段包括：
      • GET_PLAN_TIME：获取执行计划的耗时。正常情况下该阶段的耗时小于 0.1 ms，异常请求下该字段的耗时可超过 100 ms。
      • rpc_count：用于确认 NLJ 算子中被驱动表的 rescan 次数。
    • 参考文档：OBServer SQL 性能问题的典型场景

  4. 检查是否存在硬解析问题

    • 频繁对 SQL 进行硬解析会导致 SQL 请求耗时增加。可以通过调大计划缓存的大小系统变量 ob_plan_cache_percentage 来提高计划缓存命中率。
    • 示例代码：
      -- 查看当前 ob_plan_cache_percentage 设置
show variables like 'ob_plan_cache_percentage';

-- 修改租户系统变量 ob_plan_cache_percentage
set global ob_plan_cache_percentage = 10;

-- 再次查看确认修改成功
show variables like 'ob_plan_cache_percentage';
    • 参考文档：OBServer SQL 性能问题的典型场景

  5. 分析执行计划

    • 使用 EXPLAIN EXTENDED 展示执行计划，检查优化器的估行和真实行数是否有巨大差距。
    • 如果存在明显差距，需要手动收集统计信息或创建更合适的索引。
    • 参考文档：SQL Explain 优化实践

  6. 考虑连接算法的选择

    • 如果优化器选择了不合适的连接算法（如 Nested Loops Join），可能会导致性能问题。可以尝试控制优化器选择其他连接算法（如 Hash Join）。
    • 参考文档：分布式计划选错连接算法的问题

  7. 利用 OCP 平台

    • OCP 提供了完善的慢 SQL 分析功能，包括执行计划、执行频率、耗时等，可以帮助快速定位慢 SQL 的相关信息。
    • 参考文档：OCP 慢 SQL 分析

请根据上述步骤逐步排查，并结合实际日志和诊断信息进一步分析。如果有具体的 SQL 语句和相关日志，可以提供更多细节以便更准确地定位问题。

检索到的文档如下:

  1. trace_log_slow_query_watermark
  2. OceanBase 集群运维管理之用户实操
  3. OceanBase 数据库性能的影响因素
  4. SQL 诊断
  5. SQL 性能问题的典型场景和排查思路
  6. SQL 调优典型场景和案例
  7. 常见的业务问题
1 个赞
#4

麻烦使用obdiag获取一下sql诊断信息
SQL性能问题, 此处env中的trace_id对应gv$ob_sql_audit的trace_id
obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”

1 个赞
#5

报告，我们环境没有部署obdiag，我想知道有没有轻量级的分析思路和工具方法。

1 个赞
#7

根据文档库中的信息，即使没有部署 obdiag，您仍然可以采用一些轻量级的方法来分析 SQL 语句的性能问题。以下是具体的建议：

轻量级分析思路和工具方法

  1. 使用 EXPLAIN 和 EXPLAIN EXTENDED 分析执行计划

    • 目的：了解 SQL 的执行计划，识别潜在的性能瓶颈。
    • 操作：
      EXPLAIN SELECT * FROM your_table WHERE condition;
EXPLAIN EXTENDED SELECT * FROM your_table WHERE condition;
    • 解释：EXPLAIN 可以展示 SQL 的执行计划，而 EXPLAIN EXTENDED 则提供了更详细的执行计划信息，包括优化器的选择依据等。

  2. 检查系统视图 GV$OB_SQL_AUDIT

    • 目的：获取 SQL 执行的详细统计信息，如执行时间、扫描行数等。
    • 操作：
      SELECT * FROM oceanbase.gv$ob_sql_audit 
WHERE sql_id = 'your_sql_id' 
AND tenant_id = (SELECT effective_tenant_id() FROM DUAL);
    • 关键字段：
      • ELAPSED_TIME：SQL 执行总耗时。
      • GET_PLAN_TIME：获取执行计划的时间。
      • EXECUTIONS：SQL 执行次数。
      • ROWS_PROCESSED：处理的行数。
    • 参考文档：通过比较 SQL 执行时间与 trace_log_slow_query_watermark 值来判断是否为慢查询

  3. 查看 OBServer 日志

    • 目的：确认是否存在 Slow Query 消息，进一步定位慢 SQL。
    • 操作：
      • 查找 OBServer 日志文件，通常位于 /home/admin/oceanbase/log/ 目录下。
      • 使用命令行工具（如 grep）查找 Slow Query 消息：
        grep "Slow Query" /home/admin/oceanbase/log/observer.log

  4. 调整系统变量进行临时优化

    • 目的：通过调整某些系统变量来观察对 SQL 性能的影响。
    • 操作：
      • 增加计划缓存命中率：
        SET GLOBAL ob_plan_cache_percentage = 10;
      • 查看当前设置：
        SHOW VARIABLES LIKE 'ob_plan_cache_percentage';
    • 参考文档：OBServer SQL 性能问题的典型场景

  5. 利用 OCP 平台（如果有）

    • 目的：OCP 提供了完善的慢 SQL 分析功能，包括执行计划、执行频率、耗时等。
    • 操作：
      • 登录 OCP 平台，进入 SQL 审计模块，查看慢 SQL 的相关信息。
    • 参考文档：OCP 慢 SQL 分析

  6. 手动收集统计信息

    • 目的：确保优化器有足够的信息来生成最优的执行计划。
    • 操作：
      • 对相关表进行统计信息收集：
        ANALYZE TABLE your_table;
    • 参考文档：SQL Explain 优化实践

  7. 考虑连接算法的选择

    • 目的：如果优化器选择了不合适的连接算法（如 Nested Loops Join），可能会导致性能问题。
    • 操作：
      • 尝试控制优化器选择其他连接算法（如 Hash Join）：
        /*+ HASH_JOIN(table_name) */
SELECT * FROM table1 JOIN table2 ON table1.id = table2.id;
    • 参考文档：分布式计划选错连接算法的问题

总结

尽管没有部署 obdiag，您仍然可以通过上述轻量级的方法来分析 SQL 语句的性能问题。这些方法可以帮助您初步定位问题，并采取相应的优化措施。如果需要更深入的诊断和分析，建议后续考虑部署 obdiag 或使用 OCP 平台。

如果您有具体的 SQL 语句或日志片段，可以提供更多细节以便更准确地定位问题。

详情请参考:

  1. 通过 obdiag 工具进行诊断和分析
  2. OceanBase 生态工具介绍
  3. 进行 Sysbench 测试
  4. obdiag

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

1 个赞
#8

1、explain extended sql 执行计划保存在文本里 发一下
2、 收集SQL性能问题信息

obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”
https://www.oceanbase.com/docs/common-obdiag-cn-1000000001768178
3、使用 DBMS_XPLAN 包配合 sql_audit 查看执行计划。
select /*+ parallel(15) */query_sql,sql_id,svr_ip,TRACE_ID,client_ip,TENANT_NAME,user_name,DB_NAME,ELAPSED_TIME,RET_CODE,FROM_UNIXTIME(ROUND(REQUEST_TIME/1000/1000),’%Y-%m-%d %H:%i:%S’) from GV$OB_SQL_AUDIT
WHERE REQUEST_TIME>=‘2024-04-05 14:34:00’ and lower(query_sql) like ‘%select%’;
select dbms_xplan.display_cursor(sql_id, ‘all’);

1 个赞
#9

1、explain extended sql 执行计划保存在文本里 发一下 -》已经收集，附件有
20250103_OB_SQL优化.txt (35.6 KB)

2、 收集SQL性能问题信息 ->暂时无法收集，因为没有部署

Server version: OceanBase 3.2.4.4 (r104040012023071110-7cfad5c709ba7f8d5f60a8a65cf0d8cb82d99d28) (Built Jul 11 2023 10:52:08)

obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”
https://www.oceanbase.com/docs/common-obdiag-cn-1000000001768178
3、使用 DBMS_XPLAN 包配合 sql_audit 查看执行计划。 ->暂时无法收集，因为当前版本是3.2
select /*+ parallel(15) */query_sql,sql_id,svr_ip,TRACE_ID,client_ip,TENANT_NAME,user_name,DB_NAME,ELAPSED_TIME,RET_CODE,FROM_UNIXTIME(ROUND(REQUEST_TIME/1000/1000),’%Y-%m-%d %H:%i:%S’) from GV$OB_SQL_AUDIT
WHERE REQUEST_TIME>=‘2024-04-05 14:34:00’ and lower(query_sql) like ‘%select%’;
select dbms_xplan.display_cursor(‘C2FC473F9390B68985F6C3664AC43DBC’,‘all’);

1 个赞
#10

你这个是商业版的吧 社区版没有3.2版本的

1 个赞
#11

是的，商业版本。

1 个赞
#12

你好，你提的这个技术问题牵涉到OceanBase企业版范围内的功能细节；针对此类问题，建议你通过以下方式寻求帮助：

  1. 如你所在的企业客户已签署OceanBase企业版销售合同，请你联系客户经理；

  2. 如你所在的企业客户尚未签署OceanBase企业版销售合同，你可通过OceanBase官网商务咨询页面留下你的联系方式，OceanBase企业版的业务顾问会在一个工作日内与你联系。

另外，我们欢迎你使用社区版，并在论坛/社群中分享你对社区版本的想法、经验和问题，与其他社区成员共同交流。

1 个赞
#13

谢谢！学到了！

1 个赞
#15

理解，我认为社区应该是开放的，

#16

社区只对开源的版本 开放 因为商业版和社区 不一样 所以没有办法给你提供技术支持 非常抱歉

#17

我认为思路是一样的。社区版应该更加有活力

#18

商业版的版本和社区版 相差挺大的 发布的版本也不一样 商业版的优质服务你可以找商业的客户经理 会有更好的问题解答的 所以开源社区这里没有办法给你优质服务 主要活力在开源 真的不好意思 望理解

#19

好的。我会持续关注开源版本，其实也就是探讨，

#20

好的 谢谢对开源的支持