4 Replies Latest reply: Mar 4, 2013 4:31 AM by gimbal2 RSS

    No more data to read from socket  with MERGE statement in executeBatch()



      I am trying to insert 5 millions of records into Oracle using batch but for some reason I am seeing either out of memory exceptions if we execute all at a time or no more data to read from socket exception (finally Closed Connection) when I execute 5000 at a time. I am using ojdb14.jar and Oracle 11g. I am using MERGE INTO statement. Is that causing No more data to read from socket? Below is my code and exceptions. Please suggest how to resolve this issue.How to avoid No more data to read from socket issue from data? Thanks and appreciate your help.

      try {     con = DriverManager.getConnection(jdbc_url, db_username, db_password);     if (!con.getMetaData().supportsBatchUpdates())         throw new SQLException("Batch updates are not supported.");                      con.setAutoCommit(false);                 String query = "MERGE INTO table_test USING DUAL ON (ip_address = ?) "+             "WHEN MATCHED THEN UPDATE SET location_id = ?, city = ?, country = ?, region = ?, latitude = ?, longitude = ?, metrocode = ?, areacode = ?, postalcode = ?, modified_tm = sysdate, modified_contact_id = ? " +             "WHEN NOT MATCHED THEN INSERT (ip_address,location_id,city,country,region,latitude,longitude,metrocode,areacode,postalcode,created_tm,created_contact_id,modified_tm,modified_contact_id) "+             "VALUES (?,?,?,?,?,?,?,?,?,?,sysdate,?,sysdate,?)";                 pstmt = con.prepareStatement(query);             Location l2 = null;     Vector used = getUsedIPV4Addresses();     for (int i=0;i<=255;i++)     {     for (int j=0;j<=255;j++)     {     for (int k=0;k<=255;k++)     {     for (int l=0;l<=255;l++)     {     String ip = i+"."+j+"."+k+"."+l;             if (cl != null)         l2 = cl.getLocation(ip);         if (l2 != null)         {         String city = l2.city;         String country = l2.countryName;         String region = l2.region;         float latitude = l2.latitude;         float longitude = l2.longitude;         int metrocode = l2.metro_code;         String postalcode = l2.postalCode;         int areacode = l2.area_code;                 if(city == null) city="NA";         if(country == null) country="NA";         if(region == null) region="NA";         if(postalcode == null) postalcode="NA";                 int location_id = getLocId(kloc,city,country);                         pstmt.setString(1,ip);         pstmt.setInt(2,location_id);     pstmt.setString(3,city);     pstmt.setString(4,country);     pstmt.setString(5,region);     pstmt.setFloat(6,latitude);     pstmt.setFloat(7,longitude);     pstmt.setInt(8,metrocode);     pstmt.setInt(9,areacode);     pstmt.setString(10,postalcode);     pstmt.setInt(11,contact_id);     pstmt.setString(12,ip);     pstmt.setInt(13,location_id);     pstmt.setString(14,city);     pstmt.setString(15,country);     pstmt.setString(16,region);     pstmt.setFloat(17,latitude);     pstmt.setFloat(18,longitude);     pstmt.setInt(19,metrocode);     pstmt.setInt(20,areacode);     pstmt.setString(21,postalcode);     pstmt.setInt(22,contact_id);     pstmt.setInt(23,contact_id);                 pstmt.addBatch();     if (cnt > 5000)     {     pstmt.executeBatch();     logger.info("PurgeTaskIP2LocationInserter: Executed another batch..");     pstmt.clearBatch();     cnt = 0;     }     cnt++;         }     }     }     }     }     pstmt.executeBatch();     }     catch(java.sql.BatchUpdateException e)         { e.printStackTrace();     int[] updateCounts = e.getUpdateCounts(); for (int i = 0; i < updateCounts.length; i++) {       if (updateCounts[i] >= 0) {         logger.debug("Successfully executed; updateCount=" + updateCounts);
      } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
      logger.debug("Successfully executed; updateCount=Statement.SUCCESS_NO_INFO");
      } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
      logger.debug("Failed to execute; updateCount=Statement.EXECUTE_FAILED");
      if (con != null)
      if (pstmt != null) pstmt.close();
      if (con != null) con.close();
      } catch(Exception er){}
      When executing all records at a time: 2013-03-02 15:41:15,622 ERROR [STDERR] (Thread-4) Exception in thread "Thread-4" java.lang.OutOfMemoryError: Java heap space 2013-03-02 15:41:15,623 ERROR [STDERR] (Thread-4) at java.util.regex.Pattern.compile(Pattern.java:1452) 2013-03-02 15:41:15,623 ERROR [STDERR] (Thread-4) at java.util.regex.Pattern.<init>(Pattern.java:1133) 2013-03-02 15:41:15,623 ERROR [STDERR] (Thread-4) at java.util.regex.Pattern.compile(Pattern.java:823) 2013-03-02 15:41:15,623 ERROR [STDERR] (Thread-4) at java.lang.String.split(String.java:2292) 2013-03-02 15:41:15,623 ERROR [STDERR] (Thread-4) at sun.net.util.IPAddressUtil.textToNumericFormatV4(IPAddressUtil.java:29) 2013-03-02 15:41:15,623 ERROR [STDERR] (Thread-4) at java.net.InetAddress.getAllByName(InetAddress.java:1050) 2013-03-02 15:41:15,623 ERROR [STDERR] (Thread-4) at java.net.InetAddress.getAllByName(InetAddress.java:1019 When executing 5000 at a time: 2013-03-02 21:55:09,723 ERROR [Purge Task] (Thread-27) java.sql.BatchUpdateException: No more data to read from socket 2013-03-02 21:55:09,723 ERROR [STDERR] (Thread-27) java.sql.BatchUpdateException: No more data to read from socket 2013-03-02 21:55:09,723 ERROR [STDERR] (Thread-27) at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:367) 2013-03-02 21:55:09,723 ERROR [STDERR] (Thread-27) at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9040) 2013-03-02 21:55:09,723 ERROR [STDERR] (Thread-27) at java.lang.Thread.run(Thread.java:662) 2013-03-02 21:55:09,723 DEBUG [Purge Task] (Thread-27) Closed Connection 2013-03-02 21:55:09,723 ERROR [STDERR] (Thread-27) java.sql.SQLException: Closed Connection 2013-03-02 21:55:09,723 ERROR [STDERR] (Thread-27) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125) 2013-03-02 21:55:09,723 ERROR [STDERR] (Thread-27) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162) 2013-03-02 21:55:09,723 ERROR [STDERR] (Thread-27) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227) 2013-03-02 21:55:09,723 ERROR [STDERR] (Thread-27) at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:1001) Edited by: 991474 on Mar 3, 2013 10:04 AM Edited by: 991474 on Mar 3, 2013 10:07 AM Edited by: 991474 on Mar 3, 2013 1:09 PM Edited by: 991474 on Mar 3, 2013 1:11 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
        • 1. Re: No more data to read from socket  with MERGE statement in executeBatch()
          Please edit your original text, remove the code you added and use \
           on the line before and the line after you paste formatted code.
          What you posted is virtually unreadable.
          You also have not posted the code that feeds the code that you did post so we can't tell how the variables are defined                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
          • 2. Re: No more data to read from socket  with MERGE statement in executeBatch()
            Thanks and I updated that. Please suggest.
            • 3. Re: No more data to read from socket  with MERGE statement in executeBatch()
              Since you continue to be stingy in posting the code and information (error counts and variable definitions) only generic help can be provided.
              I am trying to insert 5 millions of records into Oracle using batch but for some reason I am seeing either out of memory exceptions if we execute all at a time or no more data to read from socket exception (finally Closed Connection) when I execute 5000 at a time. I am using ojdb14.jar and Oracle 11g. I am using MERGE INTO statement. Is that causing No more data to read from socket? Below is my code and exceptions. Please suggest how to resolve this issue.How to avoid No more data to read from socket issue from data? Thanks and appreciate your help.
              Your process is way out of line with standard practices:

              1. Always use the latest JDBC jar version possible. Why are you still using the ancient ojdbc14.jar file with an unknown JDK version and 11g? Unless your unspecified reason is valid update to the latest driver.

              2. You should NEVER try to insert such large numbers of data in one batch. Even attempting 5 million of anything is absurd. There is NOTHING to be gained by accumulating such large amounts of data in memory and then trying to send it all at once over the wire.

              Even a batch size of 5000 is excessive and won't provide any real benefit. The recommended batch size is 10 to 20 (see the JDBC Developer Guide) and anything larger than 100 or so is unlikely to provide any benefit at all.

              Oracle allocates a buffer based on the maximum possible size of all data values times the number of rows needed for the batch size specified. So if a column like city (or country, region, etc) is defined as VARCHAR2(50) then it requires 50 bytes in the buffer; the actual number of data bytes may never exceed 20 but 50 is allocated.

              3. You don't appear to have even tried to isolate the problem by using a reduced set of data. You haven't provided any information about how many iterations are executed before the exception occurs. Does it blow up after the first batch, second batch, 1 millionth batch?

              4. You said you have 5 million records but your quad loop will execute 4 BILLION times (255 * 255 * 255 * 255). Don't you think that is a little excessive? Especially since you don't even have your code working yet.

              I suggest that you review the JDBC Developer Guide section on the batch performance extensions that the Oracle drivers can provide

              Using Oracle's update batching you can just set the batch size and Orace will do the rest. You don't need to manually submit the batch or count. The exception trace shows you are already using the OraclePreparedStatement under the covers. You didn't provide the code that declares your variables but you could easily define your var as the Oracle version and then use Oracle update batching.

              I can not find any documentation that indicates that Oracle supports the MERGE statement for update batching. The doc link above mentions
              •You can batch only UPDATE, INSERT, or DELETE operations. Processing a batch that includes an operation that attempts to return a result set will cause an exception.
              So MERGE is NOT specifically mentioned and I haven't tried using MERGE in a batch operation. I would actually expect it to work since UPDATE and INSERT are supported and MERGE can use a combination of those. But you don't know unless you test. It is unlikely but you may find that Oracle is really not batching at all.

              1. Scale back the loop iterations and the amount of data to a manageable level; only large enough to reproduce the problem.
              2. Gather more info about what iteration actually manifests the problem
              3. The batch exception is likely because the connection is already closed/broken when the last batch is submitted
              4. update to the latest JDBC driver possible.
              5. Use Oracle batch update extensions if possible.
              6. Verify that Oracle is actually using batching for the MERGE statement
              • 4. Re: No more data to read from socket  with MERGE statement in executeBatch()
                using ojdb14.jar and Oracle 11g
                My god why? ojdbc14 is not even built to support Oracle 11g, it goes up to 10. Unless you're running on an ancient version of Java, upgrade to OJDBC 6. Perhaps that already solves something.