同样的SQL，于4.2.1.9版本和4.2.3版本执行效率差异较大

OceanBase 技术问题
#1

【 使用环境 】测试环境
【 OB or 其他组件 】oceanbase
【 使用版本 】4.2.1.9
【问题描述】
1、4.2.1.9版本下，该SQL执行时间120s，而4.2.3该SQL执行时间正常。
4.2.1.9：

image
image2235×89 59 KB

4.2.3：

image
image2236×156 74.3 KB

2、查看SQL trace
4.2.1.9环境：

image
image2227×621 201 KB

image
image2504×587 73.5 KB

2、另外，检查两边表结构的时候，我发现索引没显示在4.2.1.9版本的show create table中。

image
image1636×719 176 KB

添加报错，

image
image2239×718 285 KB

重建索引后，仍然无法显示。

【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

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

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

#3

推荐使用 obdiag 工具进行日志采集和根因分析，并且将采集到的日志提供给我们以便确认问题所在。

根因分析场景: SQL性能问题
根因分析命令: obdiag rca run --scene=sql_performance --env “{db_connect=’-h127.0.0.1 -P2881 -utest@test -p****** -Dtest’, trace_id=‘Yxx’}”

日志采集场景: SQL性能问题
日志采集命令: obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-h127.0.0.1 -P2881 -utest@test -p****** -Dtest’, trace_id=‘Yxx’}”

请您提供一些信息以便我们更好地帮助您：

  1. 您能否提供具体的 SQL 语句？
  2. 在 4.2.1.9 版本中，您是否尝试过手动绑定正确的执行计划？如果尝试过，请提供绑定计划的具体操作步骤。
  3. 您能否提供 show create table 的输出结果，以便我们了解表结构和索引情况？
  4. 您是否在 4.2.1.9 版本中进行了任何配置变更或系统升级？如果有，请提供变更或升级的详细信息。
  5. 您能否提供两个版本的系统日志，特别是与 SQL 执行相关的日志部分？

感谢您的配合！

详情请参考:

  1. SQL 调优典型场景和案例
  2. 设置并行执行参数

附上敏捷诊断工具 obdiag 使用帮助链接

1 个赞
#4

这个报错说明表已存在该索引

#5

1、把两个库的同一个语句的执行计划发一下 explain extended 保存在文本里
2、根据小助手提示 使用一下 obdiag工具

收集SQL性能问题信息

obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”

#6

1、SQL执行计划有点不同。
SQL：

SELECT 
    COUNT(DISTINCT friend.id_)
FROM
    ftsp_kh_khxx khxx
        JOIN
    ftsp_kh_pg pg ON pg.kh_khxx_id_ = khxx.id_
        JOIN
    ftsp_infra_user u ON u.id_ = pg.infra_user_id_
        AND u.is_delete_ = '0'
        JOIN
    ftsp_zj_bmyh bmyh ON bmyh.infra_user_id_ = u.id_
        AND bmyh.is_delete_ = '0'
        JOIN
    ftsp_zj_bmxx bmxx ON bmyh.zj_bmxx_id_ = bmxx.id_
        AND bmxx.is_delete_ = '0'
        JOIN
    ftsp_wechat_friend friend ON u.work_weixin_id_ = friend.infra_user_wxid_
        AND friend.source_ = '2'
        AND friend.is_delete_ = '0'
        LEFT JOIN
    ftsp_wechat_kh_relation relation ON relation.kh_khxx_id_ = khxx.id_
        AND relation.type_ = '1'
        AND friend.wxid_ = relation.wxid_
WHERE
    bmyh.is_active_ = '1'
        AND bmxx.id_ IN ('6C1C102CA0CC4C21A0769899FEADD207' , '320087F31E5F4E6BB6569534F59B551E',
        'h0000000000000132733423341051904',
        'h0000000000000393298104177876992',
        'h0000000000000393298379668168704',
        'h0000000000000393300092013748224',
        'h0000000000000393300151883243520',
        'h0000000000000581281461633056768',
        'h0000000000000581284892187860992',
        'h0000000000000581285001206210560',
        'h0000000000000581285700316995584',
        'h0000000000000581286048465248256',
        'h0000000000000581286220318416897',
        'h0000000000000393298175371993088',
        'h0000000000000393301178686595072',
        'h0000000000000393301524829937664',
        'h0000000000000393301575643914240',
        'h0000000000000393301699728203776',
        'h0000000000000581302831549022208',
        'h0000000000000581303318226706432',
        'h0000000000000581303601086423040',
        'h0000000000000393301232356925440',
        'h0000000000000393302875056087040',
        'h0000000000000393302927241601024',
        'h0000000000000581309324193210368',
        'h0000000000000581298656274587648',
        'h0000000000000581311606389596160',
        'h0000000000000581311724991897600',
        'h0000000000000393301369166733312',
        'h0000000000000393302094835834880',
        'h0000000000000581313461068800000',
        'h0000000000000391748375082147840',
        'h0000000000000414951899304173568',
        'h0000000000000414952018460164096',
        'h0000000000000414952097392771072',
        'h0000000000000414952169291522048',
        'h0000000000000414952234726858752',
        'h0000000000000414952309578424320',
        'h0000000000000437519455809929216')
        AND bmxx.zj_zjxx_id_ = 'AA4356930FCF4456B63BC1EF0E63BF40'
        AND khxx.zj_zjxx_id_ = 'AA4356930FCF4456B63BC1EF0E63BF40'
        AND EXISTS( SELECT 
            1
        FROM
            ftsp_wechat_kh_relation re
                JOIN
            ftsp_kh_khxx kh ON re.kh_khxx_id_ = kh.id_
        WHERE
            re.type_ = '1'
                AND re.wxid_ = friend.wxid_
                AND kh.name_ LIKE CONCAT('%', 'XXXXX', '%'))
        AND khxx.zj_zjxx_id_ = 'AA4356930FCF4456B63BC1EF0E63BF40';

4.2.1.9版本：

image
image2141×683 184 KB

4.2.3版本：

image
image1220×714 175 KB

收集SQL性能问题信息

obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”

obdiag_gather_pack_20241114165941.tar.gz (147.9 KB)

#7

是的，所以有点奇怪，4.2.1.9，show create table没看到这些普通索引。

不过重建了就可以正常显示了，表结构及数据都是通过OMS导入~

#8

根据您提供的信息，我们可以从以下几个方面来分析和解决问题：

1. SQL 执行计划差异

4.2.1.9 版本的执行计划

  • 主要操作：
    • SCALAR GROUP BY
    • NESTED-LOOP SEMI JOIN
    • NESTED-LOOP JOIN
    • DISTRIBUTED TABLE RANGE SCAN (多个表)
    • DISTRIBUTED TABLE GET

4.2.3 版本的执行计划

  • 主要操作：
    • SCALAR GROUP BY
    • NESTED-LOOP OUTER JOIN
    • NESTED-LOOP JOIN
    • DISTRIBUTED TABLE RANGE SCAN (多个表)
    • DISTRIBUTED TABLE GET

2. 索引问题

您提到在 4.2.1.9 版本中 SHOW CREATE TABLE 没有显示普通索引，但在重建索引后可以正常显示。这可能是由于索引元数据在某些情况下没有正确同步或更新。以下是一些可能的原因和解决方案：

  • 元数据同步问题：

    • 确认索引是否在所有副本上都已创建成功。
    • 使用 SHOW INDEX FROM table_name; 查看索引信息，确保索引确实存在。

  • 重建索引：

    • 您已经重建了索引，这是一个有效的临时解决方案。
    • 确保在重建索引后，所有副本上的索引都已更新。

3. 执行计划绑定

由于 4.2.1.9 版本的执行计划可能导致性能问题，您可以尝试绑定正确的执行计划。以下是具体步骤：

  1. 获取当前执行计划：

    EXPLAIN EXTENDED SELECT 
    COUNT(DISTINCT friend.id_)
FROM
    ftsp_kh_khxx khxx
        JOIN
    ftsp_kh_pg pg ON pg.kh_khxx_id_ = khxx.id_
        JOIN
    ftsp_infra_user u ON u.id_ = pg.infra_user_id_
        AND u.is_delete_ = '0'
        JOIN
    ftsp_zj_bmyh bmyh ON bmyh.infra_user_id_ = u.id_
        AND bmyh.is_delete_ = '0'
        JOIN
    ftsp_zj_bmxx bmxx ON bmyh.zj_bmxx_id_ = bmxx.id_
        AND bmxx.is_delete_ = '0'
        JOIN
    ftsp_wechat_friend friend ON u.work_weixin_id_ = friend.infra_user_wxid_
        AND friend.source_ = '2'
        AND friend.is_delete_ = '0'
        LEFT JOIN
    ftsp_wechat_kh_relation relation ON relation.kh_khxx_id_ = khxx.id_
        AND relation.type_ = '1'
        AND friend.wxid_ = relation.wxid_
WHERE
    bmyh.is_active_ = '1'
        AND bmxx.id_ IN ('6C1C102CA0CC4C21A0769899FEADD207' , '320087F31E5F4E6BB6569534F59B551E', ...)
        AND bmxx.zj_zjxx_id_ = 'AA4356930FCF4456B63BC1EF0E63BF40'
        AND khxx.zj_zjxx_id_ = 'AA4356930FCF4456B63BC1EF0E63BF40'
        AND EXISTS( SELECT 
            1
        FROM
            ftsp_wechat_kh_relation re
                JOIN
            ftsp_kh_khxx kh ON re.kh_khxx_id_ = kh.id_
        WHERE
            re.type_ = '1'
                AND re.wxid_ = friend.wxid_
                AND kh.name_ LIKE CONCAT('%', 'XXXXX', '%'))
        AND khxx.zj_zjxx_id_ = 'AA4356930FCF4456B63BC1EF0E63BF40';

  2. 创建 Outline 绑定执行计划：

    CREATE OUTLINE my_outline ON
SELECT 
    COUNT(DISTINCT friend.id_)
FROM
    ftsp_kh_khxx khxx
        JOIN
    ftsp_kh_pg pg ON pg.kh_khxx_id_ = khxx.id_
        JOIN
    ftsp_infra_user u ON u.id_ = pg.infra_user_id_
        AND u.is_delete_ = '0'
        JOIN
    ftsp_zj_bmyh bmyh ON bmyh.infra_user_id_ = u.id_
        AND bmyh.is_delete_ = '0'
        JOIN
    ftsp_zj_bmxx bmxx ON bmyh.zj_bmxx_id_ = bmxx.id_
        AND bmxx.is_delete_ = '0'
        JOIN
    ftsp_wechat_friend friend ON u.work_weixin_id_ = friend.infra_user_wxid_
        AND friend.source_ = '2'
        AND friend.is_delete_ = '0'
        LEFT JOIN
    ftsp_wechat_kh_relation relation ON relation.kh_khxx_id_ = khxx.id_
        AND relation.type_ = '1'
        AND friend.wxid_ = relation.wxid_
WHERE
    bmyh.is_active_ = '1'
        AND bmxx.id_ IN ('6C1C102CA0CC4C21A0769899FEADD207' , '320087F31E5F4E6BB6569534F59B551E', ...)
        AND bmxx.zj_zjxx_id_ = 'AA4356930FCF4456B63BC1EF0E63BF40'
        AND khxx.zj_zjxx_id_ = 'AA4356930FCF4456B63BC1EF0E63BF40'
        AND EXISTS( SELECT 
            1
        FROM
            ftsp_wechat_kh_relation re
                JOIN
            ftsp_kh_khxx kh ON re.kh_khxx_id_ = kh.id_
        WHERE
            re.type_ = '1'
                AND re.wxid_ = friend.wxid_
                AND kh.name_ LIKE CONCAT('%', 'XXXXX', '%'))
        AND khxx.zj_zjxx_id_ = 'AA4356930FCF4456B63BC1EF0E63BF40'
WITH (outline = '/*+ use_hash(bmyh) use_hash(friend) use_hash(pg) */');

4. 日志分析

您已经提供了 obdiag 收集的日志包，建议进一步分析日志以确定具体问题。以下是一些可能的分析方向：

  • SQL Trace：

    • 检查 trace_id 对应的 SQL 执行日志，查看是否有明显的性能瓶颈。
    • 使用 obdiag 工具分析 trace_id 对应的 SQL 执行计划和执行时间。

  • 系统日志：

    • 检查 observer.log 和 rootservice.log，查看是否有错误或警告信息。
    • 特别关注与 SQL 执行相关的日志，如 LocalDevice_submit 时间过长等。

5. 其他建议

  • 升级版本：

    • 如果可能，建议升级到 4.2.3 版本，以获得更好的性能和稳定性。

  • 性能调优：

    • 根据执行计划和日志分析结果，调整索引和查询条件，优化 SQL 性能。

如果您需要进一步的帮助，建议联系 OceanBase 的技术支持团队，他们可以提供更详细的分析和解决方案。希望以上信息对您有所帮助！

详情请参考:

  1. 计划缓存的缺点和解决方案
  2. 从 SQL 文件导入数据到 OceanBase 数据库

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

#9

话说，老师，请问，4.2.5.1，修复那个“已知的缺陷导致的DAS与非DAS计划交叉枚举的情况。该内存不足问题在425版本得到缓解，预计在425bp1解决”的版本不知道几时发布呢？

#10

预计在12月初发布

#11

那既然12月初就发布了，

这个问题我也不知道有没必要跟进，我们使用过程中确实发现4.2.1.9有问题，但是，4.2.5的版本我相信应该没有这个问题。

暂时使用这个版本只是因为交叉枚举那个问题影响我们这边比较多的SQL，之前那个/+USE_DAS/我们尝试实在没效果。我们就等一等到4.2.5这个版本就可以了~感谢老师。

#12

obdiag的收集 麻烦在ob4.2.3版本收集一下

收集SQL性能问题信息

obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”

#13

oms的版用的是哪个版本 如果是oms导入的 建议先做一次 手动统计信息收集

#14

手动收集了呢。oms 4.2.6的版本。

相关执行语句，默认参数：

CALL DBMS_STATS.GATHER_SCHEMA_STATS('test', degree=>'16', method_opt=>'for all columns size 1');

4.2.3版本执行是正常的，毫秒级别，从执行计划上这个SQL在两个版本上都是没有问题，还有必要在正常的版本上进行obdiag gather scene run吗？

另外，这个问题在4.2.3版本没有问题，我们也只是测试环境，不继续跟进也没有问题的，但我确实认为在正常的版本上进行obdiag gather scene run没有太多必要就是跑得很正常~~~

#15

你好 两边的数据量是一样么 ob的配置（内存、cpu、磁盘等）也是一样的么？
想着是交叉对比着看看 为啥会相差那么大 看看具体走的执行计划 因为都不全 所以没法看

#16

麻烦在ob4.2.3版本在用obdiag收集一下 可以对比着看看 也可以看看集群信息的差别

收集SQL性能问题信息

obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”

#17

1、服务器配置是有区别的，但是这个服务器的配置并不是特别差的，另外后续我们也会升级ob的版本规避这个问题（这个服务器之前部署4.2.5确认没有这个问题，但是由于一些bugs问题，不得不重新初始化到4.2.1的版本）；
2、4.2.3的obdiag gather scene run 结果：
obdiag_gather_pack_20241115170305.tar.gz (145.9 KB)

#18

好的 了解 先分析一下 执行计划看看问题出在哪里

#19

这个是空的

image
image2551×245 30.1 KB

查询语句加一下这个hint /*+ monitor */ 执行一次 在用obdiag收集一下这个trace_id

#20

4.2.1还是4.2.3？

#21

ob423的 查询语句加一下hint /*+ monitor */ 麻烦在用obdiag收集一下 收集完 发出来