惊呆,一条sql竟然让oracle奔溃了

2021/4/20 2:26:40

本文主要是介绍惊呆,一条sql竟然让oracle奔溃了,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

目录

系统介绍

事故现场

解决方案

新的问题

总结


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

系统介绍

系统架构见下图:

图片

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,看一下代码:

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

我们再看一下TableAMapper.xml中的mergeFromTableB方法,代码如下:

<update id="mergeFromTableB" parameterType="list">
  <foreach collection="list" item="item" index="index" separator=";" close=";end;" open="begin">
      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
      on (ta.a = tb.a)
      WHEN MATCHED THEN UPDATE set
      ta.b=tb.b,
      ta.c=tb.c,
      ta.d=tb.d
      WHEN NOT MATCHED THEN insert(
      a,
      b,
      c,
      d
      )
      values (
      tb.a,
      tb.b,
      tb.c,
      tb.d
      )
    </foreach>
</update>

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

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

图片

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

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

解决方案

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

业务系统方案

1.循环单条执行merge语句,优点是修改简单,缺点是业务系统跟数据库交互太多,会影响跑批任务执行效率。
2.对mergeFromTableB进行分批调用,比如每1000条调用一次merge方法,改造稍微多一点,但是交互会少很多。

DBA方案

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

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

for (int i = 0; i < list.size(); i += 1000) {
    if (i + 1000 < list.size()) {
        tableAMapper.mergeFromTableB(list.subList(i, i + 1000));
    } else {
        tableAMapper.mergeFromTableB(list.subList(i, list.size()));
    }
}

新的问题

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

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

begin
merge into table_a ta USING(...;
merge into table_a ta USING(...;
end;

分析了一下,虽然放在了一个SQL块中,但还是单条执行,最后一起提交。

再做一次优化,把上面多条merge语句合成1条。

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

oracle的临时表有2种,一种是会话级别,一种是事务级别:
1.会话级别的临时表,数据会在整个会话的生命周期中,会话结束,临时表数据清空;
2.事务级别的临时表,数据会在整个事务执行过程中,事务结束,临时表数据清空。

下面看具体实施过程。

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

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

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

<insert id="batchInsertTemp" parameterType="list">
  insert all
  <foreach collection="list" index="index" item="item">
    into table_a_temp
    <trim prefix="(" suffix=")" suffixOverrides="," >
      a,
      <if test="item.b != null" >
        b,
      </if>
      <if test="item.c != null" >
        c,
      </if>
      <if test="item.d != null" >
        d,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      #{item.a},
      <if test="item.b != null" >
        #{item.b,jdbcType=VARCHAR},
      </if>
      <if test="item.c != null" >
        #{item.c,jdbcType=VARCHAR},
      </if>
      <if test="item.d != null" >
        #{item.d,jdbcType=VARCHAR},
      </if>
    </trim>
  </foreach>
  select 1 from dual
</insert>

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

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

<update id="mergeFromTempData">
  MERGE INTO table_a ta
    USING (select * from table_a_temp) tb
    on (ta.a = tb.a)
    WHEN MATCHED THEN UPDATE set
  ta.b = tb.b,
  ta.c = tb.c,
  ta.d = tb.d
  WHEN NOT MATCHED THEN
  insert
  (a, b, c, d)
  values
  (tb.a, tb.b, tb.c, tb.d)
</update>

4.最终业务代码修改如下:

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

总结

在oracle上执行SQL时,如果绑定变量的数量超过了65535,会引发ORA-07445。当然,引发ORA-07445的原因还有其他。
解决这个问题最好的方式是从业务代码层面进行修改。
也可以让DBA可以给oracle打一个补丁,但是oracle必须要停服务。

原创不易,如果觉得这篇文章对你有帮助,请给一个关注,点个赞,或者关注我的公众号:程序员jinjunzhu,关注后可以第一时间收到最新文章。



这篇关于惊呆,一条sql竟然让oracle奔溃了的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程