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

标签: java jdbc oracle | 发表时间:2013-03-18 09:43 | 作者:liu_maclean
出处:http://blog.csdn.net

不拾掇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中的慢主要体现在:

  1. 他是一个太长的PL/SQL block,在第一次 Parse解析时Oracle 使用PL/SQL engine引擎要扫描整个block,从上面的tkprof结果可以看到光parse就消耗了179秒,即便不用setExecuteBatch 还是 executeBatch仅使用最普通的batch size=1的循环SQL也要比这个拼接SQL块。
  2. 它的执行需要在Pl/SQL引擎和SQL引擎之间不断切换,所以Execute也非常慢
  3. 它要分配60000个绑定变量,这对于PGA的压力太大了,很可能导致SWAP
  4. 由于变量和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除外),千万不要想当然去自己发明一种新的”轮子”出来,必要的基础工具 包括 “扳手” “螺丝” “轮胎”,他们都是现成的,也是被久经考验 高效可用的, 自己去重新发明轮子,难免不费时费力而不讨好。

作者:liu_maclean 发表于2013-3-18 9:43:17 原文链接
阅读:0 评论:0 查看评论

相关 [java jdbc oracle] 推荐:

oracle的jdbc驱动

- - Oracle - 数据库 - ITeye博客
oracle的jdbc驱动主要有下面三类:.   1、JDBC OCI: oci是oracle call interface的缩写,此驱动类似于传统的ODBC 驱动. 因为它需要Oracle Call Interface and Net8,所以它需要在运行使用此驱动的JAVA程序的机器上安装客户端软件,其实主要是用到orcale客户端里以dll方式提供的oci和服务器配置.

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

- - CSDN博客推荐文章
不拾掇Java有好几年了(N>3吧),之所以写这篇文章其实是纯粹是为了给开发人员一些好的使用jdbc真正去减少交互和提升批量处理batch update性能的例子;  如果你是DBA,那么工作之余你可以把这篇文章推荐给开发看一下, 也许这些例子他已经知道了, 倘若他不知道,那么也算一种福利了. 能考虑到在应用程序client和 数据库服务器DB server间减少交互时间,批量更新处理的绝对是有助于重构和优化代码的好同志;  但这种优化一定要注意方法,如果是自行去重新发明一种轮子的话, 效果往往是不如人意的.

Java HIVE 使用Jdbc连接Hive

- - CSDN博客云计算推荐文章
1,使用Jdbc方式链接hive,首先需要启动hive的Thrift Server,否则会导致错误. hive --service hiveserver   是两”-“,. ----通过Class.forName("org.apache.hadoop.hive.jdbc.HiveDriver");加载hive驱动.

java JDBC 链接hive 操作实例

- - CSDN博客云计算推荐文章
1.在eclipse下面 导入包,简便方式 new ->mapred project -> 右键 ->选择“Properties”->Java Build Path->Library->Add External Jars 将/usr/hive/lib的所有jar包 添加上. (因为之前的配置 所有jar包 已经包括 java链接mysql的包).

(转)Oracle JDBC Memory Management中的客户端缓存

- - jackyrong
  对于oracle jdbc中,一个容易忽略的参数是:prepared-statement-cache-size,这次转来. http://xulingbo.net/?p=109这篇好文,详细讲解了这个参数用法. 从Oracle10g开始在JDBC驱动中,增加了对执行每个Statement的缓存.

oracle jdbc fetchsize取值对性能的影响

- - CSDN博客编程语言推荐文章
       通过JDBC取数据时,默认是10条数据取一次,即fetch size为10,如果增大这个数字可以减少客户端与oracle的往返,减少响应时间,网上有建议这个数字不要超过100,要不然对中间件内存消耗大(没有做过实验). String query_string = "SELECT * FROM test";//test有5万条记录.

JDBC操纵Oracle数据库中的BLOB字段

- - Oracle - 数据库 - ITeye博客
                                                                        BLOB字段的写入. 方法一:(JDBC2.0 规范). 2.插入一个空的blob. 3.使用行级锁定blob字段.                                                                        BLOB字段的读取.

正确使用MySQL JDBC setFetchSize()方法解决JDBC处理大结果集 java.lang.OutOfMemoryError: Java hea

- - Java - 编程语言 - ITeye博客
昨天在项目中需要对日志的查询结果进行导出功能. 日志导出功能的实现是这样的,输入查询条件,然后对查询结果进行导出. 之前的解决方案都是多次查询,然后使用limit 限制每次查询的条数. 那么能不能一次查询就把所有结果倒出来了. 于是我就使用一次查询,不使用limit分页. 结果出现 java.lang.OutOfMemoryError: Java heap space问题.

HA-JDBC -

- -
The state manager component is responsible for storing the active status of each database in the cluster, as well as any durability state.

Oracle发布Java 7安全修复

- - InfoQ cn
近日,Oracle发布了 Java SE 7u11以修复安全漏洞 CVE-2013-0422,该漏洞在过去几天内得到了广泛的使用,可以在允许运行Java的浏览器上远程安装与执行代码. 这利用了applet(通常没必要配置就可以在浏览器中运行Java),并且可以通过 Java 7运行时的特性与反射来跳出安全沙箱.