驚呆，一條sql竟然讓oracle崩潰了

來自公眾號：程序員jinjunzhu

一條sql就能讓oracle掛了，真的是不敢相信啊，前幾天生產(chǎn)上確實出現(xiàn)了這樣一個故障，我們來一起做一個事件回放。

系統(tǒng)介紹

系統(tǒng)架構(gòu)見下圖：

application1和application2是一個分布式系統(tǒng)中的2個應用，application1連接的數(shù)據(jù)庫是database1，application2連接的數(shù)據(jù)庫是database2，application2生產(chǎn)的數(shù)據(jù)要給application1做跑批使用。

application1要獲取database2的數(shù)據(jù)，并不是通過接口來獲取的，而是直連database2來獲取，因此application1也具有database2庫的讀權限。

database2中有1張表table_b，里面保存的數(shù)據(jù)是application1跑批需要的數(shù)據(jù)。application1查找到table_b的數(shù)據(jù)后，先保存到database1的數(shù)據(jù)庫表table_a中，等跑批時取出來用。

table_a和table_b的表結(jié)構(gòu)如下：

2個表的主鍵都是字段a，application1查詢出table_b的數(shù)據(jù)后，會根據(jù)主鍵a來判斷這條數(shù)據(jù)是否存在，如果數(shù)據(jù)存在，就更新，否則，就插入。

application1使用的orm框架是mybatis，為了減少應用和數(shù)據(jù)庫的交互，使用了oracle的merge語句。

注意：mybatis相關的文件有5個：
TableAMapper.java
TableBMapper.java
TableAMapper.xml
TableBMapper.xml
TableAEntity.java

熟悉mybatis的同學應該都知道，前兩個java類是sql操作接口類，第3、4兩個文件是存放sql的xml文件，跟前兩個文件對應，最后一個java文件是do類。

事故現(xiàn)場

TableBMapper中有一個方法selectForPage,用來按頁查詢table_b中數(shù)據(jù)，每頁1萬條數(shù)據(jù)，之后把這個list結(jié)果merge到table_a，看一下代碼：

//從table_b按每頁1萬條來查詢數(shù)據(jù)
List<TableAEntity> list = tableBMapper.selectForPage(startPage, 10000);
//把查到的數(shù)據(jù)一次性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>

注意：為了文章排版，我對表結(jié)構(gòu)做了簡化，真實案例中table_a這張表有60多個字段。

這條sql執(zhí)行后，我截取部分oracle的日志，如下：

圖中可以看到oracle報了ORA-07445錯誤。

分析日志后發(fā)現(xiàn)，sql綁定變量達到了了79010個，而oracle是不允許超過65535個的。

解決方案

前面的分析確定了導致oracle掛掉的原因是綁定變量超過了65535個，那對癥下藥，解決的方案有3個：

業(yè)務系統(tǒng)方案

1.循環(huán)單條執(zhí)行merge語句，優(yōu)點是修改簡單，缺點是業(yè)務系統(tǒng)跟數(shù)據(jù)庫交互太多，會影響跑批任務執(zhí)行效率。
2.對mergeFromTableB進行分批調(diào)用，比如每1000條調(diào)用一次merge方法，改造稍微多一點，但是交互會少很多。

DBA方案

給oracle打一個補丁，這個方案需要停服務。

業(yè)務方案2明細有優(yōu)勢，我用這個方案進行了改造，每次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()));
    }
}

新的問題

按照上面的方案改造完成后，數(shù)據(jù)庫不會奔潰了，但是新的問題出現(xiàn)了。測試的同學發(fā)現(xiàn)，每次處理超過1000條數(shù)據(jù)，非常耗時，有時竟然達到了4分鐘，驚呆。

看打印的批量sql，類似于下面的語句：

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

分析了一下，雖然放在了一個SQL塊中，但還是單條執(zhí)行，最后一起提交。

再做一次優(yōu)化，把上面多條merge語句合成1條。

我的優(yōu)化思路是創(chuàng)建一張臨時表，先把list中的數(shù)據(jù)插入到臨時表中，然后用一次merge把臨時表的數(shù)據(jù)merge進table_a這張表。

oracle的臨時表有2種，一種是會話級別，一種是事務級別：
1.會話級別的臨時表，數(shù)據(jù)會在整個會話的生命周期中，會話結(jié)束，臨時表數(shù)據(jù)清空；
2.事務級別的臨時表，數(shù)據(jù)會在整個事務執(zhí)行過程中，事務結(jié)束，臨時表數(shù)據(jù)清空。

下面看具體實施過程。

1.我們創(chuàng)建一張會話臨時表，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查詢到的數(shù)據(jù)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.把臨時表的數(shù)據(jù)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.最終業(yè)務代碼修改如下：

//從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把數(shù)據(jù)merge到table_a
tableAMapper.mergeFromTempData();

總結(jié)

在oracle上執(zhí)行SQL時，如果綁定變量的數(shù)量超過了65535，會引發(fā)ORA-07445。當然，引發(fā)ORA-07445的原因還有其他。
解決這個問題最好的方式是從業(yè)務代碼層面進行修改。
也可以讓DBA可以給oracle打一個補丁，但是oracle必須要停服務。

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

往
期
推
薦
1、最牛逼的 Java 日志框架，性能無敵，橫掃所有對手！
2、把Redis當作隊列來用，真的合適嗎？
3、驚呆了，Spring Boot居然這么耗內(nèi)存！你知道嗎？
4、牛逼哄哄的 BitMap，到底牛逼在哪？
5、全網(wǎng)最全 Java 日志框架適配方案！還有誰不會？
6、30個IDEA插件總有一款適合你
7、Spring中毒太深，離開Spring我居然連最基本的接口都不會寫了
點分享
點收藏
點點贊
點在看