基于Oracle数据库使用批量操作接口每次批量处理的数量不宜过大，否则会引起内存泄漏

DAS批量操作仅对JDBC驱动做的最简单的封装，引起内存泄漏的根本原因还是由于oracle JDCB内存管理机制导致的，当每次批量提交的数据量越大，内存泄漏的风险就越大（通过实际测试也应正了这一点），至于为什么会这样，在Oracle的白皮书中也含糊其辞。

综合Oracle的官方文档，每次批量提交的行数不要超过1000条，最好是50-100条，否则当提交数据较大时，必然会引起内存泄漏。

以下是测试验证过程：

测试代码，每次请求插入380个中文字符和1个int行数据：

@Test

    public void testBatchInert()

    {

        int count = 5000;

        System.out.println("Begin to execute insert....");

        CmdRequest request = new CmdRequest();

        for (int j = 0; j < 40; j++)

        {

            request.setSQL(INIT_DATA_SQL);

            for (int i = j * count; i < (j + 1) * count; i++)

            {

                request.addBatchDataParam("oid", DataType.INT, i);

                request

                        .addBatchDataParam(

                                "dchar",

                                DataType.STRING,

                                "哈哈呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵" +

                                "呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵" +

                                "呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵" +

                                "呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵" +

                                "呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵" +

                                "呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵" +

                                "呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵" +

                                "呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵" +

                                "呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵" +

                                "呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵" +

                                "呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵呵");

                request.addBatch();

            }

            cmd.execute(request);

            request.clearParams();

        }

        System.out.println("Insert OK....");

        try

        {

            Thread.sleep(1000 * 1000);

        }

        catch (InterruptedException e)

        {

            e.printStackTrace();

        }

    }

场景一、每次批量1W条数据，循环20次

内存无法完全释放，通过heap dump文件分析如下：

堆内存分析快照1：

堆内存分析快照2：

通过以上快照分析，有7620016的字符没有被释放，恰好是所有请求数据的总和。

场景二、每次批量5000条数据，循环40次处理

内存GC曲线：

通过GC曲线分析出，所有处理完成以后，仍然有近5M的内存没有释放

堆内存分析图1：

堆内存分析图2：

通过分析可以看出，仍然有3810016的字符没有被释放，依然存在泄漏，但比1W条一次少了一半的泄漏。

场景三、每次提交100条，循环2000次

内存GC曲线

通过GC的曲线可以看出，在执行完成以后可以做到内存的完全释放。

附：

一、Oracle官网地址：http://download.oracle.com/docs/cd/E11882_01/java.112/e16548/oraperf.htm#g1068570

Note:

·         Oracle recommends that you use JDBC standard features when possible. This recommendation applies to update batching as well. Oracle update batching is retained primarily for backwards compatibility.

·         For both standard update batching and Oracle update batching, Oracle recommends you to keep the batch sizes in the general range of 50 to 100. This is because though the drivers support larger batches, they in turn result in a large memory footprint with no corresponding increase in performance. Very large batches usually result in a decline in performance compared to smaller batches.

二、Oracle白皮书关于批量执行内存管理的说明

Statement Batching and Memory Use

The row data buffers are not the only large buffers the Oracle JDBC drivers can create. They can

also create large buffers to send PreparedStatement parameters to the database. Applications

generally read more data than they write and write smaller chunks of data at one time. As a result

the parameter data buffers tend to be much smaller than the row data buffers. However, using

(misusing) statement batching it is possible to force the drivers to create large buffers.

When the application calls PreparedStatement. setXXX to set a parameter value, the driver stores

the value. This takes little memory; just a reference for arrays and Object types like String, 8 bytes

for long and double, and 4 bytes for all others. When the PreparedStatement is executed, the

driver must send those values to the database as SQL types, not Java types. The driver creates a

byte[] and a char[] buffer. The parameter data is converted to SQL representation which is stored

in the buffers. Then the driver ships the bytes across the network. Since the driver has the actual

data size before the buffer is allocated, it can create a minimum size buffers. If a statement is

executed multiple times, the driver tries to reuse the buffers. If the new data values need a larger

byte[] or char[] buffer, a larger buffer is allocated. With any reasonable amount of memory, it

would be impossible to run out of memory executing a single statement. With statement batching

however, things are different.

Both JDBC standard and Oracle statement batching execute a single statement many times in

one operation. To do this the driver must send all the parameter values for all of the

PreparedStatement executes at once. This means the driver must convert all of the parameter