Java Jdbc减少与Oracle之间交互提升批量处理性能，到底该如何优化才好？

来源：互联网发布：淘宝助理怎么上传新图编辑：程序博客网时间：2024/06/05 09:28

不拾掇Java有好几年了(N>3吧)，之所以写这篇文章其实是纯粹是为了给开发人员一些好的使用jdbc真正去减少交互和提升批量处理batch update性能的例子；如果你是DBA，那么工作之余你可以把这篇文章推荐给开发看一下，也许这些例子他已经知道了，倘若他不知道，那么也算一种福利了。

能考虑到在应用程序client和数据库服务器DB server间减少交互时间，批量更新处理的绝对是有助于重构和优化代码的好同志；但这种优化一定要注意方法，如果是自行去重新发明一种轮子的话，效果往往是不如人意的。

例如Tom Kytes曾在他的著作里提到这样2个例子，他去协助开发的2家企业的在研发应用的过程中，分别通过应用程序自己去在Oracle中实现了user profile和advanced queue的功能，有一定经验的朋友肯定会知道这2样功能其实Oracle Enterprise Edition企业版软件都是原生态支持的，而自己在DB中去实现它们，最终结果自然是项目的失败。

类似的有朋友在开发过程中，为了优化Oracle JDBC中的批量更新update操作，想到了这样的方式，例如要插入INSERT 15000行数据，则在JAVA层面将15000条INSERT语句拼接在一个PL/SQL block里，这15000条SQL涉及到的变量仍使用PreparedStatement.setXXX方法带入，其在JAVA层面的SQL STRING，如：

begin   --我是一个拼接起来的SQL匿名块  insert into insertit values(?,?,?,?);  insert into insertit values(?,?,?,?);  insert into insertit values(?,?,?,?);  insert into insertit values(?,?,?,?);  insert into insertit values(?,?,?,?);  insert into insertit values(?,?,?,?);  insert into insertit values(?,?,?,?);  insert into insertit values(?,?,?,?);  insert into insertit values(?,?,?,?);  insert into insertit values(?,?,?,?);  insert into insertit values(?,?,?,?);  insert into insertit values(?,?,?,?); .....................  commit ; end;

如上15000个INSERT拼接成一个PL/SQL block，一次性PreparedStatement.execute()提交给DB，通过这样来减少Jdbc Thin Client与DB Server之间的交互。先不说别的，光在JAVA里循环控制拼接SQL的写法多少是要花点时间的。

这种写法和 JDBC里PreparedStatement.setExecuteBatch、或者PreparedStatement+addBatch()+executeBatch()的执行效率究竟如何呢？

我们在一个简单的JAVA程序里测试这三者写法的实际性能，并窥探其在DB中的表现，以下为JAVA代码(多年不写，就勿纠结代码风格)：

/* * To change this template, choose Tools | Templates * and open the template in the editor. */package apptest;import oracle.jdbc.*;import java.sql.*;/** * * @author xiangbli */public class Apptest {    /**     * @param args the command line arguments     */    public static void main(String[] args) throws SQLException {        // TODO code application logic here        try {            Class.forName("oracle.jdbc.driver.OracleDriver");        }catch(Exception e){}        Connection cnn1=DriverManager.getConnection("jdbc:oracle:thin:@192.168.56.101:1521:cdb1", "c##maclean", "oracle");        Statement stat1=cnn1.createStatement();        cnn1.setAutoCommit(false);   ResultSet rst1=stat1.executeQuery("select * from v$version");   while(rst1.next())   {       System.out.println(rst1.getString(1));   }        long startTime = System.currentTimeMillis();             long stopTime = System.currentTimeMillis();   String str="begin \n  --我是一个拼接起来的SQL匿名块 \n";   int i;     for(i=0;i<=15000; i++)     {     str= str.concat(" insert into insertit values(?,?,?,?); \n");     }       str=str.concat("  commit ; end; ");    System.out.print(str);    cnn1.createStatement().execute("alter system flush shared_pool");    System.out.print("\n alter system flush shared_pool 已刷新共享池，避免SQL游标缓存 影响第一次测试 \n");     PreparedStatement  pstmt = cnn1.prepareStatement(str);     int j;     for (j=0;j<=15000;j++)     {         pstmt.setInt(1+j*4, 1);         pstmt.setInt(2+j*4, 1);         pstmt.setInt(3+j*4, 1);         pstmt.setInt(4+j*4, 1);     }  //  System.out.println (" Statement Execute Batch Value " +((OraclePreparedStatement)pstmt).getExecuteBatch());startTime = System.currentTimeMillis();     pstmt.execute(); stopTime = System.currentTimeMillis();     System.out.println("拼接15000条INSERT SQL 第一次运行的耗时 Elapsed time was " + (stopTime - startTime) + " miliseconds.");     startTime = System.currentTimeMillis();     pstmt.execute();     stopTime = System.currentTimeMillis();     System.out.println("拼接15000条INSERT SQL 第二次运行的耗时 Elapsed time was " + (stopTime - startTime) + " miliseconds.");    cnn1.createStatement().execute("alter system flush shared_pool");    System.out.print("\n alter system flush shared_pool 已刷新共享池，避免SQL游标缓存 影响第二次测试 \n");        startTime = System.currentTimeMillis();        int batch=1000;       PreparedStatement  pstmt2 = cnn1.prepareStatement("insert into insertit values(?,?,?,?)");      ((OraclePreparedStatement)pstmt2).setExecuteBatch(batch);       for (int z=0;z<=15000;z++)       {       pstmt2.setInt(1, z);       pstmt2.setInt(2, z);       pstmt2.setInt(3, z);       pstmt2.setInt(4, z);       pstmt2.executeUpdate();       }       ((OraclePreparedStatement)pstmt2).sendBatch();     cnn1.commit();     stopTime = System.currentTimeMillis();      System.out.println("batch size= "+batch+" 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was " + (stopTime - startTime) + " miliseconds.");      startTime = System.currentTimeMillis();       PreparedStatement  pstmt3 = cnn1.prepareStatement("insert into insertit values(?,?,?,?)");      ((OraclePreparedStatement)pstmt3).setExecuteBatch(batch);       for (int z=0;z<=15000;z++)       {       pstmt3.setInt(1, z);       pstmt3.setInt(2, z);       pstmt3.setInt(3, z);       pstmt3.setInt(4, z);       pstmt3.executeUpdate();       }     ((OraclePreparedStatement)pstmt3).sendBatch();     cnn1.commit();     stopTime = System.currentTimeMillis();      System.out.println("batch size= "+batch+" 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was " + (stopTime - startTime) + " miliseconds.");      String insert = "insert into insertit values (?,?,?,?)";      PreparedStatement  pstmt4 = cnn1.prepareStatement(insert);       startTime = System.currentTimeMillis();      for (int u=0;u<=15000;u++)      {          pstmt4.setInt(1, u);          pstmt4.setInt(2, u);          pstmt4.setInt(3, u);          pstmt4.setInt(4, u);          pstmt4.addBatch();      }      pstmt4.executeBatch();       cnn1.commit();     stopTime = System.currentTimeMillis();       System.out.println(" BATCH update 第一次运行的耗时 Elapsed time was " + (stopTime - startTime) + " miliseconds.");     }    }

拼接SQL和PreparedStatement.setExecuteBatch均执行2次，第一次没有游标缓存，第二次有游标缓存。PreparedStatement+addBatch()+executeBatch()只执行一次。

以下为JAVA程序端的测试结果：

alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第一次测试
拼接15000条INSERT SQL 第一次运行的耗时 Elapsed time was 441299 miliseconds.
拼接15000条INSERT SQL 第二次运行的耗时 Elapsed time was 5938 miliseconds.

alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 1000 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 322 miliseconds.
batch size= 1000 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 131 miliseconds.
BATCH update 第一次运行的耗时 Elapsed time was 80 miliseconds.

以下为DB SERVER端 10046 trace的结果：

begin  --我是一个拼接起来的SQL匿名块 insert into insertit values(:1 ,:2 ,:3 ,:4 ); insert into insertit values(:5 ,:6 ,:7 ,:8 ); insert into insertit values(:9 ,:10 ,:11 ,:12 ); insert into insertit values(:13 ,:14 ,:15 ,:16 ); insert into insertit values(:17 ,:18 ,:19 ,:20 ); insert into insertit values(:21 ,:22 ,:23 ,:24 ); insert into insertit values(:25 ,:26 ,:27 ,:28 ); insert into insertit values(:29 ,:30 ,:31 ,:32 ); insert into insertit values(:33 ,:34 ,:35 ,:36 ); insert into insertit values(:37 ,:38 ,:39 ,:40 ); insert into insertit values(:41 ,:42 ,:43 ,:44 ); insert into insertit values(:45 ,:46 ,:47 ,:48 ); insert into insertit values(:49 ,:50 ,:51 ,:52 ); insert into insertit values(:53 ,:54 ,:55 ,:56 ); insert into insertit values(:57 ,:58 ,:59 ,:60 ); insert into insertit values(:61 ,:62 ,:63 ,:64 ); insert into insertit values(:65 ,:66 ,:67 ,:68 ); insert into insertit values(:69 ,:70 ,:71 ,:72 );................................... insert into insertit values(:59989 ,:59990 ,:59991 ,:59992 ); insert into insertit values(:59993 ,:59994 ,:59995 ,:59996 ); insert into insertit values(:59997 ,:59998 ,:59999 ,:60000 ); insert into insertit values(:60001 ,:60002 ,:60003 ,:60004 );  commit ; end;call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1    176.10     179.33          0         97          0           0Execute      2    150.51     155.37          2          4          0           2Fetch        0      0.00       0.00          0          0          0           0------- ------  -------- ---------- ---------- ---------- ----------  ----------total        3    326.61     334.71          2        101          0           2===>这是拼接SQL 在DB SERVER端总耗时334秒， CPU时间 326秒insert into insertitvalues(:1 ,:2 ,:3 ,:4 )call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        2      0.00       0.00          0          0          0           0Execute     32      0.09       0.11          4        823       1000       30002Fetch        0      0.00       0.00          0          0          0           0------- ------  -------- ---------- ---------- ---------- ----------  ----------total       34      0.09       0.11          4        823       1000       30002==》这是　使用PreparedStatement.setExecuteBatch的结果，　耗时0.11秒，cpu时间 0.09秒,因为batch size是1000，所以实际是每1000次INSERT执行一次，所以总的执行次数约为30次insert into insertitvalues (:1 ,:2 ,:3 ,:4 )call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute      1      0.03       0.04          1         93        475       15001Fetch        0      0.00       0.00          0          0          0           0------- ------  -------- ---------- ---------- ---------- ----------  ----------total        2      0.03       0.04          1         93        475       15001==>这是使用addBatch()+executeBatch()， execute和parse均只为一次，即15000条数据仅解析一次 执行一次类似于PL/SQL中bulk collect INSERT的效果

以上可以看到拼接SQL的写法不管是对比 setExecuteBatch 还是 executeBatch都要满几百倍。

拼接15000条INSERT语句到一个Pl/SQL block中的慢主要体现在：

他是一个太长的PL/SQL block，在第一次 Parse解析时Oracle 使用PL/SQL engine引擎要扫描整个block，从上面的tkprof结果可以看到光parse就消耗了179秒，即便不用setExecuteBatch 还是 executeBatch仅使用最普通的batch size=1的循环SQL也要比这个拼接SQL块。
它的执行需要在Pl/SQL引擎和SQL引擎之间不断切换，所以Execute也非常慢
它要分配60000个绑定变量，这对于PGA的压力太大了，很可能导致SWAP
由于变量和SQL过长，会引起一些莫名得小概率发生的BUG

实际上JDBC 原生态的 setExecuteBatch 、 executeBatch都是为了减少 client-server之间的交互。

setExecuteBatch 是等待可执行的SQL达到一定数目后，一次性提交给Oracle，一般推荐的Batch Size是20~50，我简单测试了不同batch size在本例中的情况：

alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 1 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 4990 miliseconds.
batch size= 1 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 3778 miliseconds.
成功构建 (总时间: 9 秒)

alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 10 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 829 miliseconds.
batch size= 10 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 474 miliseconds.
alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 20 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 694 miliseconds.
batch size= 20 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 366 miliseconds.
成功构建 (总时间: 1 秒)

alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 30 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 516 miliseconds.
batch size= 30 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 204 miliseconds.
成功构建 (总时间: 1 秒)
alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 40 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 481 miliseconds.
batch size= 40 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 188 miliseconds.
成功构建 (总时间: 1 秒)

alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 50 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 456 miliseconds.
batch size= 50 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 171 miliseconds.
成功构建 (总时间: 1 秒)

alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 100 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 450 miliseconds.
batch size= 100 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 123 miliseconds.
成功构建 (总时间: 1 秒)

alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 200 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 402 miliseconds.
batch size= 200 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 92 miliseconds.
成功构建 (总时间: 1 秒)
alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 500 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 386 miliseconds.
batch size= 500 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 95 miliseconds.
成功构建 (总时间: 1 秒)
alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 1000 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 369 miliseconds.
batch size= 1000 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 87 miliseconds.
成功构建 (总时间: 1 秒)

可以看到上述例子中200~500的Execute batch size获得的性能较好。

PreparedStatement+addBatch()+executeBatch()的方式很像PL/SQL中的bulk collect处理，将变量置入batch array中，执行时将array中的数据一次插入到表中，即解析一次、执行一次，虽然对PGA会有一些少量压力，但是从上例中也能看到，获得了最快的速度。

综上所述对于Java Jdbc减少交互提升批量处理性能，最好的办法是尽可能多用PreparedStatement setExecuteBatch 和 executeBatch。

对于软件开发，特别是上层软件的开发(OS、Database、programming language除外)，千万不要想当然去自己发明一种新的”轮子”出来，必要的基础工具包括 “扳手” “螺丝” “轮胎”，他们都是现成的，也是被久经考验高效可用的，自己去重新发明轮子，难免不费时费力而不讨好。