总结一条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必须要停服务。

“总结一条SQL竟然让Oracle奔溃了”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！