驚呆，一條SQL，Oracle炸了...

作者：jinjunzhu 2021-04-16 07:04:53

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

圖片來自 Pexels

系統(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 庫的讀權(quán)限。

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)方案

①循環(huán)單條執(zhí)行 merge 語句，優(yōu)點是修改簡單，缺點是業(yè)務系統(tǒng)跟數(shù)據(jù)庫交互太多，會影響跑批任務執(zhí)行效率。

②對 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 種，一種是會話級別，一種是事務級別：

會話級別的臨時表，數(shù)據(jù)會在整個會話的生命周期中，會話結(jié)束，臨時表數(shù)據(jù)清空。
事務級別的臨時表，數(shù)據(jù)會在整個事務執(zhí)行過程中，事務結(jié)束，臨時表數(shù)據(jù)清空。

下面看具體實施過程：

①我們創(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表臨時表';

②把 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 條。

③把臨時表的數(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>

④最終業(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 必須要停服務。