SQL远程计划SPF变慢被放大

2024年 5月 7日 31.7k 0

前言:

众所周知,ob是分布式数据库,所以sql避免不了会有远程和分布式执行计划。

前一段时间业务遇到一条sql超时,执行效率很低,反馈过来要求优化排查。

处理过程:

因为我们有几套核心系统业务类型基本一样,去另一套ob库查询该sql的执行历史,发现并不慢,对比执行计划也一样,只是远程算子位置不一样,手工将该sql涉及的表副本leader切到同一台server上,将他解决。

思路分析:

测试环境:OceanBase 3.2.3.2 (r105000062022090916-4dc1f420f94fe716a60cce42b110437c4aad731e

生产环境:OceanBase 3.2.3.3 (r107060012023041113-3cfe0f0783ae40aa8a41ec7d074ab7dedb93f702

因为该问题可以稳定复现,所以处理后去测试分析。

sql文本脱敏后如下

obclient>   Select count(*)
    ->  from PPPPDDDDD t
    -> WHERE (t.CCCCCCID = 'SSS.AA' or exists
    ->        (Select 1
    ->           FROM OOOOOOOO_CCCCC
    ->          Where PAAAID = 'SSS.AA'
    ->            and SUUUUUID = t.CCCCCCID) or exists
    ->        (select 1
    ->           from PPPPDDDD_GG B
    ->          where exists (select 1
    ->                   from OOOOOOOO_CCCCC
    ->                  where SUUUUUID = 'SSS.AA'
    ->                    and PAAAID = b.OGGID)
    ->             or exists (select 1
    ->                   from OOOOOOOO_CCCCC
    ->                  where PAAAID = 'SSS.AA'
    ->                    and PAAAID = b.OGGID)
    ->            and b.status = 1
    ->            and b.POOID = t.POOID))
    ->   AND t.ISSSSSTION = 0
    ->   And Exists
    -> (select 1
    ->          from PPPPDDD_IIIMENT i
    ->         where i.POOID = t.POOID
    ->           and i.RRRRON in (111, 123))
    ->   AND (t.PRDDDTYPE like '%' || 'Ptype_Pppson' || '%' or
    ->       t.PRDDDTYPE = 'Ptype_Wddd' or
    ->       t.PRDDDTYPE = 'Ptype_Apppp' or
    ->       t.PRDDDTYPE = 'Ptype_Prooo' or
    ->       t.PRDDDTYPE = 'Ptype_Prmmm' or
    ->       t.PRDDDTYPE = 'Ptype_FFF' or t.PRDDDTYPE = 'PppType_Ssss' or
    ->       (t.PRDDDTYPE = 'Ptype_Unnnnn' and exists
    ->        (select 1
    ->            from PP_RRR_UNNN_CCCSI a
    ->           where a.uniiiiiiid = 'ALL'
    ->             and a.solllluuuoid = t.POOID)))
    ->   AND (t.PRDDDTYPE <> 'REwriterticty')
    ->   and t.auuuustatus in (1, 2)
    ->   AND t.MAAAAROD = 0
    ->   AND nvl(t.enddate, sysdate + 1) > sysdate
    ->   and t.Status = 1
    -> Order by t.POOID;

测试环境进行了3次测试,为了方便下文分别称PPPPDDDDD为t表,PPPPDDDD_GG为B表,OOOOOOOO_CCCCC为C表,PPPPDDD_IIIMENT为i表,PP_RRR_UNNN_CCCSI为a表,生产出问题时B和C表副本不在一个server,

>当5张表的主副本在一个server上时,在测试环境执行是8s

>当b,a两表leader副本在一个server,另外3张表的leader在另一个server上,通过2881分别直连两个节点的效率分别是1分钟和14.5分钟,使用2883默认连接的时候执行时间是14.5分钟

说明下b表10w数据,c表1000w数据,a表600数据,t表8w数据,i表6w数据。

因为我们做了多次测试,暂时不详细说了,简单介绍下

select 1
    ->           from PPPPDDDD_GG B
    ->          where exists (select 1
    ->                   from OOOOOOOO_CCCCC
    ->                  where SUUUUUID = 'SSS.AA'
    ->                    and PAAAID = b.OGGID)
    ->             or exists (select 1
    ->                   from OOOOOOOO_CCCCC
    ->                  where PAAAID = 'SSS.AA'
    ->                    and PAAAID = b.OGGID)
    ->            and b.status = 1

单独把B,C的关联条件拿出来,执行很快,0点几秒,走的hash,因为他在原sql中是两层exist嵌套没办法转换成hash连接,用hint指定nest_loop仍然很快,去除B,C的条件后原语句执行也很快,结果160条数据。正是这160条数据导致我一开始感觉走filter效率也会很快。

那么下面对比下执行计划

SQL远程计划SPF变慢被放大-1

通过对比可以看到慢的执行计划15-17号是个远程算子,问题主要出在这里。limit截断算子正常是流式执行,外层传参进入内层,匹配到一条数据后就会停止,继续下面的执行,但是右面慢的执行计划有个exchange的远程查询推入,会进行查询重写,相当于外层传参一次,b表会全表数据拿出来进行一次比对,这个过程进行160次,导致变慢被放大。

结论:

因为分布式执行计划的特殊性,所以传统dba接触时会有一些忽略和知识上的盲区,需要通过不断的学习丰富自己。这个问题,在4.x最新版本这个机制会有优化,现在是计算下压,下压到不同server上去执行,所以外层驱动每传参一次就会去协调者节点查询重写一次,相当于一次新的执行,现在ob的新版本中会将远程节点数据拉取到协调者节点,就可以避免多次的查询重写。我们这种迁移副本的处理方式需要关闭rebalance参数避免分区自动均衡回来,长时间关闭该参数可能造成节点数据不均衡,当然这个问题还有别的解决方法,比如去改写语句,将bc关联的这部分条件改写到上一层,让优化器自动改写走hash连接,也可以规避。

行之所向,莫问远方。

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论