OutOfMemoryError GC overhead limit exceeded for java jdbc preparedStatement

Question

I'm writing a tool used for transferring the data from old schema to new schema in oracle database.

There are about twenty tables in my database. Only two of them are big, they may have four million records. Others are small ( maybe ten thousands or one hundred thousand).

Now I use one thread to process all small tables in serially, and divide big table into pieces, create some thread, and use one thread process one piece, every piece is one million record.

Now I have some problems. When I start the program, every is ok. But when my program run after a period of time, I will get some error info:

Exception in thread "Thread-8" java.lang.OutOfMemoryError: GC overhead limit exceeded
at oracle.jdbc.driver.OracleBlobInputStream.needBytes(OracleBlobInputStream.java:168)
at oracle.jdbc.driver.OracleBufferedStream.readInternal(OracleBufferedStream.java:178)
at oracle.jdbc.driver.OracleBufferedStream.read(OracleBufferedStream.java:147)
at oracle.jdbc.driver.OracleBufferedStream.read(OracleBufferedStream.java:137)
at oracle.jdbc.driver.BlobAccessor.getBytes(BlobAccessor.java:249)
at oracle.jdbc.driver.OracleResultSetImpl.getBytes(OracleResultSetImpl.java:714)
at oracle.jdbc.driver.OracleResultSet.getBytes(OracleResultSet.java:1625)
at datatransfer.processor.CProcessor.write(CProcessor.java:111)
at datatransfer.processor.Processor.process(Processor.java:77)
at datatransfer.thread.CThread.run(CThread.java:37)

I have checked my program, there is no closed loop, and I really close statement and resultset.

Every Thread has its own Connection.

How can I check the reason why my program cost memory? And is there any way to solve this problem?

    ResultSet rs = statement.executeQuery(sql);
    int count = 0;
    long start = System.currentTimeMillis();
    while(rsSrc.next()){
        preStatement.setString(1, rsSrc.getString(1)); 
        preStatement.setString(2, rsSrc.getString(2)); 
        preStatement.setString(3, rsSrc.getString(3)); 
        preStatement.setString(4, rsSrc.getString(4)); 
        preStatement.setString(5, rsSrc.getString(5)); 
        preStatement.setString(6, rsSrc.getString(6)); 
        preStatement.addBatch();
        count++;
        if (count % batchSize == 0){
            preStatement.executeBatch();
            preStatement.clearBatch();

        }
    }
    preStatement.executeBatch();
    preStatement.clearBatch();
    writeConn.commit();
    long end = System.currentTimeMillis();

statement and preStatement are created by different Connection, one is old schema, the other is new schema.

Is there something wrong in my code?

Answer 1

Try to analyse the instances / object creation in your RAM with jvisualvm, it usually tells you right away if you are leaking or not. (It's a GUI, do not panic ;-) )

Doc -> https://docs.oracle.com/javase/6/docs/technotes/tools/share/jvisualvm.html

It's a profiler, so it will show you where you spend time, how many instances of your classes you have and basically what's going on IN your app while it is running.

It's installed by default with the official oracle jdk on linux!

If the memory usage is almost constant but on the edge, try to increase you heap (-Xmx2G for example)