Java Jdbc减少交互提升批量处理性能,到底该如何优化才好?
不拾掇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 0 Execute 2 150.51 155.37 2 4 0 2 Fetch 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 insertit values (:1 ,:2 ,:3 ,:4 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 32 0.09 0.11 4 823 1000 30002 Fetch 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 insertit values (:1 ,:2 ,:3 ,:4 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.03 0.04 1 93 475 15001 Fetch 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
posted on 2013-03-19 00:52 Oracle和MySQL 阅读(299) 评论(0) 编辑 收藏 举报