驚呆，一條SQL，Oracle炸了...
圖片來(lái)自 Pexels
系統(tǒng)介紹
系統(tǒng)架構(gòu)見(jiàn)下圖：
application1 和 application2 是一個(gè)分布式系統(tǒng)中的 2 個(gè)應(yīng)用。
application1 連接的數(shù)據(jù)庫(kù)是 database1，application2 連接的數(shù)據(jù)庫(kù)是 database2，application2 生產(chǎn)的數(shù)據(jù)要給 application1 做跑批使用。
application1 要獲取 database2 的數(shù)據(jù)，并不是通過(guò)接口來(lái)獲取的，而是直連 database2 來(lái)獲取，因此 application1 也具有 database2 庫(kù)的讀權(quán)限。
database2 中有 1 張表 table_b，里面保存的數(shù)據(jù)是 application1 跑批需要的數(shù)據(jù)。
application1 查找到 table_b 的數(shù)據(jù)后，先保存到 database1 的數(shù)據(jù)庫(kù)表 table_a 中，等跑批時(shí)取出來(lái)用。
table_a 和 table_b 的表結(jié)構(gòu)如下：
2 個(gè)表的主鍵都是字段 a，application1 查詢(xún)出 table_b 的數(shù)據(jù)后，會(huì)根據(jù)主鍵a來(lái)判斷這條數(shù)據(jù)是否存在，如果數(shù)據(jù)存在，就更新，否則，就插入。
application1 使用的 orm 框架是 MyBatis，為了減少應(yīng)用和數(shù)據(jù)庫(kù)的交互，使用了 Oracle 的 merge 語(yǔ)句。
注意，mybatis 相關(guān)的文件有 5 個(gè)：
- TableAMapper.java
- TableBMapper.java
- TableAMapper.xml
- TableBMapper.xml
- TableAEntity.java
熟悉 MyBatis 的同學(xué)應(yīng)該都知道，前兩個(gè) Java 類(lèi)是 SQL 操作接口類(lèi)，第 3、4 兩個(gè)文件是存放 SQL 的 XML 文件，跟前兩個(gè)文件對(duì)應(yīng)，最后一個(gè) Java 文件是 do 類(lèi)。
事故現(xiàn)場(chǎng)
TableBMapper 中有一個(gè)方法 selectForPage，用來(lái)按頁(yè)查詢(xún) table_b 中數(shù)據(jù)，每頁(yè) 1 萬(wàn)條數(shù)據(jù)，之后把這個(gè) list 結(jié)果 merge 到 table_a。
看一下代碼：
- //從table_b按每頁(yè)1萬(wàn)條來(lái)查詢(xún)數(shù)據(jù)
- List<TableAEntity> list = tableBMapper.selectForPage(startPage, 10000);
- //把查到的數(shù)據(jù)一次性merge到table_a中
- tableAMapper.mergeFromTableB(list);
我們?cè)倏匆幌?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>
注意：為了文章排版，我對(duì)表結(jié)構(gòu)做了簡(jiǎn)化，真實(shí)案例中 table_a 這張表有 60 多個(gè)字段。
這條 SQL 執(zhí)行后，我截取部分 Oracle 的日志，如下：
圖中可以看到 Oracle 報(bào)了 ORA-07445 錯(cuò)誤。
分析日志后發(fā)現(xiàn)，SQL 綁定變量達(dá)到了了 79010 個(gè)，而 Oracle 是不允許超過(guò) 65535 個(gè)的。
解決方案
前面的分析確定了導(dǎo)致 Oracle 掛掉的原因是綁定變量超過(guò)了 65535 個(gè)，那對(duì)癥下藥，解決的方案有 3 個(gè)：
業(yè)務(wù)系統(tǒng)方案
①循環(huán)單條執(zhí)行 merge 語(yǔ)句，優(yōu)點(diǎn)是修改簡(jiǎn)單，缺點(diǎn)是業(yè)務(wù)系統(tǒng)跟數(shù)據(jù)庫(kù)交互太多，會(huì)影響跑批任務(wù)執(zhí)行效率。
②對(duì) mergeFromTableB 進(jìn)行分批調(diào)用，比如每 1000 條調(diào)用一次 merge 方法，改造稍微多一點(diǎn)，但是交互會(huì)少很多。
DBA 方案
給 Oracle 打一個(gè)補(bǔ)丁，這個(gè)方案需要停服務(wù)。
業(yè)務(wù)方案 2 明細(xì)有優(yōu)勢(shì)，我用這個(gè)方案進(jìn)行了改造，每次 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()));
- }
- }
新的問(wèn)題
按照上面的方案改造完成后，數(shù)據(jù)庫(kù)不會(huì)奔潰了，但是新的問(wèn)題出現(xiàn)了。測(cè)試的同學(xué)發(fā)現(xiàn)，每次處理超過(guò) 1000 條數(shù)據(jù)，非常耗時(shí)，有時(shí)竟然達(dá)到了 4 分鐘，驚呆。
看打印的批量 SQL，類(lèi)似于下面的語(yǔ)句：
- begin
- merge into table_a ta USING(...;
- merge into table_a ta USING(...;
- end;
分析了一下，雖然放在了一個(gè) SQL 塊中，但還是單條執(zhí)行，最后一起提交。再做一次優(yōu)化，把上面多條 merge 語(yǔ)句合成 1 條。
我的優(yōu)化思路是創(chuàng)建一張臨時(shí)表，先把 list 中的數(shù)據(jù)插入到臨時(shí)表中，然后用一次 merge 把臨時(shí)表的數(shù)據(jù) merge 進(jìn) table_a 這張表。
Oracle 的臨時(shí)表有 2 種，一種是會(huì)話級(jí)別，一種是事務(wù)級(jí)別：
- 會(huì)話級(jí)別的臨時(shí)表，數(shù)據(jù)會(huì)在整個(gè)會(huì)話的生命周期中，會(huì)話結(jié)束，臨時(shí)表數(shù)據(jù)清空。
- 事務(wù)級(jí)別的臨時(shí)表，數(shù)據(jù)會(huì)在整個(gè)事務(wù)執(zhí)行過(guò)程中，事務(wù)結(jié)束，臨時(shí)表數(shù)據(jù)清空。
下面看具體實(shí)施過(guò)程：
①我們創(chuàng)建一張會(huì)話臨時(shí)表，SQL 如下：
- create global temporary table_a_temp on commit delete rows as select * from table_a;
- comment on table_a_temp is 'table_a表臨時(shí)表';
②把 table_b 查詢(xún)到的數(shù)據(jù) list 插入臨時(shí)表，需要在 TableAMapper.xml 增加一個(gè)方法：
- <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 語(yǔ)句單次插入不能超過(guò) 1000 條。
③把臨時(shí)表的數(shù)據(jù) merge 到 table_a 中，需要在 TableAMapper.xml 增加一個(gè)方法：
- <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è)務(wù)代碼修改如下：
- //從table_b查詢(xún)
- List<TableAEntity> list = tableBMapper.selectForPage(startPage, 10000);
- //批量插入table_a_temp臨時(shí)表
- 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í)，如果綁定變量的數(shù)量超過(guò)了 65535，會(huì)引發(fā) ORA-07445。當(dāng)然，引發(fā) ORA-07445 的原因還有其他。
解決這個(gè)問(wèn)題最好的方式是從業(yè)務(wù)代碼層面進(jìn)行修改。也可以讓 DBA 可以給 Oracle 打一個(gè)補(bǔ)丁，但是 Oracle 必須要停服務(wù)。
延伸閱讀：
https://community.oracle.com/tech/apps-infra/discussion/2424571/ora-07445-exception-encountered-core-dump-ptmak-106-sigsegv-addres
作者：jinjunzhu
編輯：陶家龍
出處：轉(zhuǎn)載自公眾號(hào)程序員 jinjunzhu
