共計 4170 個字符，預計需要花費 11 分鐘才能閱讀完成。

本篇內容介紹了“總結一條 SQL 竟然讓 Oracle 奔潰了”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

系統介紹

系統架構見下圖：

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 奔潰了”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注丸趣 TV 網站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！