99热在线精品一区二区三区_国产伦精品一区二区三区女破破_亚洲一区二区三区无码_精品国产欧美日韩另类一区

本篇內(nèi)容介紹了“總結(jié)一條SQL竟然讓Oracle奔潰了”的有關(guān)知識，在實(shí)際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

專注于為中小企業(yè)提供做網(wǎng)站、成都做網(wǎng)站服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)衡山免費(fèi)做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動了數(shù)千家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。

系統(tǒng)介紹

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

application1和application2是一個(gè)分布式系統(tǒng)中的2個(gè)應(yīng)用，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中，等跑批時(shí)取出來用。

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

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

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

注意：mybatis相關(guān)的文件有5個(gè)：

TableAMapper.java

TableBMapper.java

TableAMapper.xml

TableBMapper.xml

TableAEntity.java

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

事故現(xiàn)場

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

//從table_b按每頁1萬條來查詢數(shù)據(jù) List list = tableBMapper.selectForPage(startPage, 10000); //把查到的數(shù)據(jù)一次性merge到table_a中 tableAMapper.mergeFromTableB(list);

我們再看一下TableAMapper.xml中的mergeFromTableB方法，代碼如下：

          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       )      

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

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

圖中可以看到oracle報(bào)了ORA-07445錯誤。

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

解決方案

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

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

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

2.對mergeFromTableB進(jìn)行分批調(diào)用，比如每1000條調(diào)用一次merge方法，改造稍微多一點(diǎn)，但是交互會少很多。

DBA方案

給oracle打一個(gè)補(bǔ)丁，這個(gè)方案需要停服務(wù)。

業(yè)務(wù)方案2明細(xì)有優(yōu)勢，我用這個(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()));     } }

新的問題

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

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

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

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

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

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

oracle的臨時(shí)表有2種，一種是會話級別，一種是事務(wù)級別：

1.會話級別的臨時(shí)表，數(shù)據(jù)會在整個(gè)會話的生命周期中，會話結(jié)束，臨時(shí)表數(shù)據(jù)清空;

2.事務(wù)級別的臨時(shí)表，數(shù)據(jù)會在整個(gè)事務(wù)執(zhí)行過程中，事務(wù)結(jié)束，臨時(shí)表數(shù)據(jù)清空。

下面看具體實(shí)施過程。

1.我們創(chuàng)建一張會話臨時(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í)表';

2.把table_b查詢到的數(shù)據(jù)list插入臨時(shí)表，需要在 TableAMapper.xml 增加一個(gè)方法：

   insert all        into table_a_temp            a,                b,                       c,                       d,                        #{item.a},                #{item.b,jdbcType=VARCHAR},                       #{item.c,jdbcType=VARCHAR},                       #{item.d,jdbcType=VARCHAR},                  select 1 from dual 

注意：oracle的insert all語句單次插入不能超過1000條。

3.把臨時(shí)表的數(shù)據(jù)merge到table_a中，需要在 TableAMapper.xml 增加一個(gè)方法：

   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) 

4.最終業(yè)務(wù)代碼修改如下：

//從table_b查詢 List 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ù)量超過了65535，會引發(fā)ORA-07445。當(dāng)然，引發(fā)ORA-07445的原因還有其他。

解決這個(gè)問題最好的方式是從業(yè)務(wù)代碼層面進(jìn)行修改。

也可以讓DBA可以給oracle打一個(gè)補(bǔ)丁，但是oracle必須要停服務(wù)。

“總結(jié)一條SQL竟然讓Oracle奔潰了”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！

分享文章：總結(jié)一條SQL竟然讓Oracle奔潰了
網(wǎng)站地址：http://www.ef60e0e.cn/article/gjjspp.html