惊呆,一条SQL,Oracle炸了...

运维 数据库运维
一条 SQL 就能让 Oracle 挂了,真的是不敢相信啊,前几天生产上确实出现了这样一个故障,我们来一起做一个事件回放。

[[393461]]

图片来自 Pexels

系统介绍

系统架构见下图:

application1 和 application2 是一个分布式系统中的 2 个应用。

application1 连接的数据库是 database1,application2 连接的数据库是 database2,application2 生产的数据要给 application1 做跑批使用。

application1 要获取 database2 的数据,并不是通过接口来获取的,而是直连 database2 来获取,因此 application1 也具有 database2 库的读权限。

database2 中有 1 张表 table_b,里面保存的数据是 application1 跑批需要的数据。

application1 查找到 table_b 的数据后,先保存到 database1 的数据库表 table_a 中,等跑批时取出来用。

table_a 和 table_b 的表结构如下:

2 个表的主键都是字段 a,application1 查询出 table_b 的数据后,会根据主键a来判断这条数据是否存在,如果数据存在,就更新,否则,就插入。

application1 使用的 orm 框架是 MyBatis,为了减少应用和数据库的交互,使用了 Oracle 的 merge 语句。

注意,mybatis 相关的文件有 5 个:

  • TableAMapper.java
  • TableBMapper.java
  • TableAMapper.xml
  • TableBMapper.xml
  • TableAEntity.java

熟悉 MyBatis 的同学应该都知道,前两个 Java 类是 SQL 操作接口类,第 3、4 两个文件是存放 SQL 的 XML 文件,跟前两个文件对应,最后一个 Java 文件是 do 类。

事故现场

TableBMapper 中有一个方法 selectForPage,用来按页查询 table_b 中数据,每页 1 万条数据,之后把这个 list 结果 merge 到 table_a。

看一下代码:

  1. //从table_b按每页1万条来查询数据 
  2. List<TableAEntity> list = tableBMapper.selectForPage(startPage, 10000); 
  3. //把查到的数据一次性merge到table_a中 
  4. tableAMapper.mergeFromTableB(list); 

我们再看一下 TableAMapper.xml 中的 mergeFromTableB 方法。

代码如下:

  1. <update id="mergeFromTableB" parameterType="list"
  2.   <foreach collection="list" item="item" index="index" separator=";" close=";end;" open="begin"
  3.       MERGE INTO table_a ta USING(select #{item.a} as a,#{item.b} as b,#{item.c} as c, #{item.d} as d from dual) tb 
  4.       on (ta.a = tb.a) 
  5.       WHEN MATCHED THEN UPDATE set 
  6.       ta.b=tb.b, 
  7.       ta.c=tb.c, 
  8.       ta.d=tb.d 
  9.       WHEN NOT MATCHED THEN insert
  10.       a, 
  11.       b, 
  12.       c, 
  13.       d 
  14.       ) 
  15.       values ( 
  16.       tb.a, 
  17.       tb.b, 
  18.       tb.c, 
  19.       tb.d 
  20.       ) 
  21.     </foreach> 
  22. </update

注意:为了文章排版,我对表结构做了简化,真实案例中 table_a 这张表有 60 多个字段。

这条 SQL 执行后,我截取部分 Oracle 的日志,如下:

图中可以看到 Oracle 报了 ORA-07445 错误。

分析日志后发现,SQL 绑定变量达到了了 79010 个,而 Oracle 是不允许超过 65535 个的。

解决方案

前面的分析确定了导致 Oracle 挂掉的原因是绑定变量超过了 65535 个,那对症下药,解决的方案有 3 个:

业务系统方案

①循环单条执行 merge 语句,优点是修改简单,缺点是业务系统跟数据库交互太多,会影响跑批任务执行效率。

②对 mergeFromTableB 进行分批调用,比如每 1000 条调用一次 merge 方法,改造稍微多一点,但是交互会少很多。

DBA 方案

给 Oracle 打一个补丁,这个方案需要停服务。

业务方案 2 明细有优势,我用这个方案进行了改造,每次 1000 条,批量 merge。

代码如下:

  1. for (int i = 0; i < list.size(); i += 1000) { 
  2.     if (i + 1000 < list.size()) { 
  3.         tableAMapper.mergeFromTableB(list.subList(i, i + 1000)); 
  4.     } else { 
  5.         tableAMapper.mergeFromTableB(list.subList(i, list.size())); 
  6.     } 

新的问题

按照上面的方案改造完成后,数据库不会奔溃了,但是新的问题出现了。测试的同学发现,每次处理超过 1000 条数据,非常耗时,有时竟然达到了 4 分钟,惊呆。

看打印的批量 SQL,类似于下面的语句:

  1. begin 
  2. merge into table_a ta USING(...; 
  3. merge into table_a ta USING(...; 
  4. end

分析了一下,虽然放在了一个 SQL 块中,但还是单条执行,最后一起提交。再做一次优化,把上面多条 merge 语句合成 1 条。

我的优化思路是创建一张临时表,先把 list 中的数据插入到临时表中,然后用一次 merge 把临时表的数据 merge 进 table_a 这张表。

Oracle 的临时表有 2 种,一种是会话级别,一种是事务级别:

  • 会话级别的临时表,数据会在整个会话的生命周期中,会话结束,临时表数据清空。
  • 事务级别的临时表,数据会在整个事务执行过程中,事务结束,临时表数据清空。

下面看具体实施过程:

①我们创建一张会话临时表,SQL 如下:

  1. create global temporary table_a_temp on commit delete rows as select * from table_a; 
  2. comment on table_a_temp is 'table_a表临时表'

②把 table_b 查询到的数据 list 插入临时表,需要在 TableAMapper.xml 增加一个方法:

  1. <insert id="batchInsertTemp" parameterType="list"
  2.   insert all 
  3.   <foreach collection="list" index="index" item="item"
  4.     into table_a_temp 
  5.     <trim prefix="(" suffix=")" suffixOverrides="," > 
  6.       a, 
  7.       <if test="item.b != null" > 
  8.         b, 
  9.       </if> 
  10.       <if test="item.c != null" > 
  11.         c, 
  12.       </if> 
  13.       <if test="item.d != null" > 
  14.         d, 
  15.       </if> 
  16.     </trim> 
  17.     <trim prefix="values (" suffix=")" suffixOverrides="," > 
  18.       #{item.a}, 
  19.       <if test="item.b != null" > 
  20.         #{item.b,jdbcType=VARCHAR}, 
  21.       </if> 
  22.       <if test="item.c != null" > 
  23.         #{item.c,jdbcType=VARCHAR}, 
  24.       </if> 
  25.       <if test="item.d != null" > 
  26.         #{item.d,jdbcType=VARCHAR}, 
  27.       </if> 
  28.     </trim> 
  29.   </foreach> 
  30.   select 1 from dual 
  31. </insert

注意:Oracle 的 insert all 语句单次插入不能超过 1000 条。

③把临时表的数据 merge 到 table_a 中,需要在 TableAMapper.xml 增加一个方法:

  1. <update id="mergeFromTempData"
  2.   MERGE INTO table_a ta 
  3.     USING (select * from table_a_temp) tb 
  4.     on (ta.a = tb.a) 
  5.     WHEN MATCHED THEN UPDATE set 
  6.   ta.b = tb.b, 
  7.   ta.c = tb.c, 
  8.   ta.d = tb.d 
  9.   WHEN NOT MATCHED THEN 
  10.   insert 
  11.   (a, b, c, d) 
  12.   values 
  13.   (tb.a, tb.b, tb.c, tb.d) 
  14. </update

④最终业务代码修改如下:

  1. //从table_b查询 
  2. List<TableAEntity> list = tableBMapper.selectForPage(startPage, 10000); 
  3. //批量插入table_a_temp临时表 
  4. for (int i = 0; i < list.size(); i += 1000) { 
  5.     if (i + 1000 < list.size()) { 
  6.         tableAMapper.batchInsertTemp(list.subList(i, i + 1000)); 
  7.     } else { 
  8.         tableAMapper.batchInsertTemp(list.subList(i, list.size())); 
  9.     } 
  10. //从table_a_temp把数据merge到table_a 
  11. tableAMapper.mergeFromTempData(); 

总结

在 Oracle 上执行 SQL 时,如果绑定变量的数量超过了 65535,会引发 ORA-07445。当然,引发 ORA-07445 的原因还有其他。

解决这个问题最好的方式是从业务代码层面进行修改。也可以让 DBA 可以给 Oracle 打一个补丁,但是 Oracle 必须要停服务。

延伸阅读:

https://community.oracle.com/tech/apps-infra/discussion/2424571/ora-07445-exception-encountered-core-dump-ptmak-106-sigsegv-addres

作者:jinjunzhu

编辑:陶家龙

出处:转载自公众号程序员 jinjunzhu

 

责任编辑:武晓燕 来源: 程序员 jinjunzhu
相关推荐

2021-02-09 09:50:21

SQLOracle应用

2020-10-26 08:02:28

SQL慢查询索引

2020-05-27 11:55:47

Oracle SQL性能优化数据库

2010-11-15 14:16:09

Oracle表记录

2020-07-01 09:07:52

SQL索引语句

2011-03-23 13:52:09

ORACLESQL十进制

2022-10-12 07:38:24

SQL语句异常

2017-10-23 15:17:42

技术业务职位

2023-03-26 22:42:02

SQL关联索引

2022-02-11 14:43:53

SQL语句C/S架构

2022-05-31 13:58:09

MySQL查询语句

2023-10-06 15:29:07

MySQL数据库更新

2021-09-15 06:21:36

Update语句数据库

2020-04-17 14:16:10

SQL数据库HTTP

2022-08-31 14:06:55

DevOpsSRE开发

2022-07-31 22:07:03

宕机业务场景

2010-04-13 16:57:01

2023-02-26 23:31:01

SQL数据库

2024-01-03 17:42:32

SQL数据库

2020-08-20 09:27:43

Windows
点赞
收藏

51CTO技术栈公众号