21 Performance Extensions

Oracle Update Batching Characteristics and Limitations

Note the following limitations and implementation details regarding Oracle update batching:

• By default, there is no statement batch value and the connection batch value is 1.

• Batch values between 5 and 30 tend to be the most effective. Setting a very high value might even have a negative effect. It is worth trying different values to verify the effectiveness for your particular application.

• Regardless of the batch value in effect, if any of the bind variables of an Oracle prepared statement is of a data type which requires data to be streamed, for instance LOB, CLOB, then Oracle JDBC driver sets the batch value to 1 and sends any queued requests to the database for processing.

• Oracle JDBC driver implicitly runs the sendBatch method of an Oracle prepared statement in any of the following circumstances:

  • The connection receives a COMMIT request as a result of calling the commit method.

  • The statement receives a close request.

  • The connection receives a close request.

  Note:

  A connection COMMIT request, statement close, or connection close has an effect on a pending batch only if you use Oracle update batching. However, if you use standard update batching, then it has no effect on a pending batch.

• If the connection receives a ROLLBACK request before sendBatch has been called, then the pending batched operations are not removed. You must explicitly call clearBatch to do this.

Setting the Connection Batch Value

You can specify a default batch value for any Oracle prepared statement in your Oracle connection. To do this, use the setDefaultExecuteBatch method of the OracleConnection object. For example, the following code sets the default batch value to 20 for all prepared statement objects associated with the conn connection object:

((OracleConnection)conn).setDefaultExecuteBatch(20);

The connection batch value will apply to statement objects created after this batch value was set.

Note that instead of calling the setDefaultExecuteBatch method, you can set the defaultBatchValue Java property if you use a Java Properties object in establishing the connection.

Setting the Statement Batch Value

Use the following steps to set the statement batch value for a particular Oracle prepared statement. This will override any connection batch value set using the setDefaultExecuteBatch method of the OracleConnection instance for the connection in which the statement is processed.

1. Write your prepared statement, and specify input values for the first row, as follows:

   PreparedStatement ps = conn.prepareStatement("INSERT INTO dept VALUES (?,?,?)");
   ps.setInt(1,12);
   ps.setString(2,"Oracle");
   ps.setString(3,"USA");
   

2. Cast your prepared statement to OraclePreparedStatement, and apply the setExecuteBatch method. In this example, the batch size of the statement is set to 2.

   ((OraclePreparedStatement)ps).setExecuteBatch(2);
   

   If you wish, insert the getExecuteBatch method at any point in the program to check the default batch value for the statement, as follows:

   System.out.println(" Statement Execute Batch Value " +
                      ((OraclePreparedStatement)ps).getExecuteBatch());
   

3. If you send an execute-update call to the database at this point, then no data will be sent to the database, and the call will return 0.

   // No data is sent to the database by this call to executeUpdate
   System.out.println("Number of rows inserted so far: " + ps.executeUpdate ());
   

4. If you enter a set of input values for a second row and an execute-update, then the number of batch calls to executeUpdate will be equal to the batch value of 2. The data will be sent to the database, and both rows will be inserted in a single round-trip. Since this executeUpdate call is at the batch value boundary, and all the operations in the batch are executed on the server, it will return the total number of affected rows by the whole batch.

   ps.setInt(1, 11);
   ps.setString(2, "Applications");
   ps.setString(3, "Indonesia");
   
   int rows = ps.executeUpdate();
   System.out.println("Number of rows inserted now: " + rows);
   
   ps.close();
   

Checking the Batch Value

To check the overall connection batch value of an Oracle connection instance, use the OracleConnection class getDefaultExecuteBatch method:

Integer batch_val = ((OracleConnection)conn).getDefaultExecuteBatch();

To check the particular statement batch value of an Oracle prepared statement, use the OraclePreparedStatement class getExecuteBatch method:

Integer batch_val = ((OraclePreparedStatement)ps).getExecuteBatch();

Overriding the Batch Value

If you want to process accumulated operations before the batch value in effect is reached, then use the sendBatch method of the OraclePreparedStatement object.

For this example, presume you set the connection batch value to 20. This sets the default batch value for all prepared statement objects associated with the connection to 20. You can accomplish this by casting your connection to OracleConnection and applying the setDefaultExecuteBatch method for the connection, as follows:

((OracleConnection)conn).setDefaultExecuteBatch (20);

Override the batch value as follows:

1. Write your prepared statement, specify input values for the first row, and then process the statement, as follows:

   PreparedStatement ps =
      conn.prepareStatement("insert into dept values (?, ?, ?)");
       
   ps.setInt(1, 32);
   ps.setString(2, "Oracle");
   ps.setString(3, "USA");
   
   System.out.println (ps.executeUpdate ()); 
   

   The batch is not processed at this point. The ps.executeUpdate method returns 0.

2. If you enter a set of input values for a second operation and call executeUpdate again, then the data will still not be sent to the database, because the batch value in effect for the statement is the connection batch value, which is 20.

   ps.setInt(1, 33);
   ps.setString(2, "Applications");
   ps.setString(3, "Indonesia");
   
   // this batch is still not executed at this point
   int rows = ps.executeUpdate();
       
   System.out.println("Number of rows updated before calling sendBatch: "  + rows);
   

   Note that the value of rows in the println statement is 0.

3. If you apply the sendBatch method at this point, then the two previously batched operations will be sent to the database in a single round-trip. The sendBatch method also returns the total number of updated rows. This property of sendBatch is used by println to print the number of updated rows.

   // Execution of both previously batched executes will happen
   // at this point. The number of rows updated will be
   // returned by sendBatch.
   rows = ((OraclePreparedStatement)ps).sendBatch();
   
   System.out.println("Number of rows updated by calling sendBatch: " + rows);
   ps.close();
   

Committing the Changes in Oracle Batching

After you process the batch, you must still commit the changes, presuming auto-commit is disabled as recommended.

Calling the commit method on the connection object in Oracle batching not only commits operations in batches that have been processed, but also issues an implicit sendBatch call to process all pending batches. So, the commit method effectively commits changes for all operations that have been added to a batch.

Update Counts in Oracle Batching

In a nonbatching situation, the executeUpdate method of an OraclePreparedStatement object returns the number of database rows affected by the operation.

In an Oracle batching situation, this method returns the number of rows affected at the time the method is invoked, as follows:

• If an executeUpdate call results in the operation being added to the batch, then the method returns a value of 0, because nothing was written to the database yet.

• If an executeUpdate call results in the batch value being reached and the batch being processed, then the method returns the total number of rows affected by all operations in the batch.

Similarly, the sendBatch method of an OraclePreparedStatement object returns the total number of rows affected by all operations in the batch.

Example 21-1 illustrates the use of Oracle update batching.

...
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:oci);
ods.setUser("HR");
ods.setPassword("hr");

Connection conn = ods.getConnection();
conn.setAutoCommit(false);

PreparedStatement ps = 
  conn.prepareStatement("insert into dept values (?, ?, ?)"); 
     
//Change batch size for this statement to 3 
((OraclePreparedStatement)ps).setExecuteBatch (3);
 
ps.setInt(1, 23); 
ps.setString(2, "Sales"); 
ps.setString(3, "USA"); 
ps.executeUpdate(); //JDBC queues this for later execution 
 
ps.setInt(1, 24); 
ps.setString(2, "Blue Sky"); 
ps.setString(3, "Montana"); 
ps.executeUpdate(); //JDBC queues this for later execution 
 
ps.setInt(1, 25); 
ps.setString(2, "Applications"); 
ps.setString(3, "India"); 
ps.executeUpdate(); //The queue size equals the batch value of 3 
                    //JDBC sends the requests to the database

ps.setInt(1, 26); 
ps.setString(2, "HR"); 
ps.setString(3, "Mongolia"); 
ps.executeUpdate(); //JDBC queues this for later execution
 
((OraclePreparedStatement)ps).sendBatch(); // JDBC sends the queued request
conn.commit();

ps.close();
...

UPDATE emp SET name = "Sue" WHERE name = "Bob";
SELECT name FROM emp WHERE name = "Sue";

Error Reporting in Oracle Update Batching

If any one of the batched operations fails to complete successfully or attempts to return a result set during an executeBatch call, then the processing stops and a java.sql.BatchUpdateException is generated.

If the exception is raised, you can call the getUpdateCounts method on the BatchUpdateException object to retrieve the update count. This method returns an int array of update counts, just as the executeBatch method does.

Prior to Oracle Database 11g Release 1, the integer array returned contains n Statement.EXECUTE_FAILED entries, where n is the size of the batch. However, this does not indicate where in the batch the error occurred. The only option you have is to rollback the transaction.

Starting from Oracle Database 11g Release 1, the integer array returned contains n Statement.SUCCESS_NO_INFO entries, where n is the number of elements in the batch that have been successfully executed.

Limitations in the Oracle Implementation of Standard Batching

This section discusses the limitations and implementation details regarding the Oracle implementation of standard update batching.

In Oracle JDBC applications, update batching is intended for use with prepared statements that are being processed repeatedly with different sets of bind values.

The Oracle implementation of standard update batching does not implement true batching for generic statements and callable statements. Even though Oracle JDBC supports the use of standard batching for Statement and CallableStatement objects, you are unlikely to see performance improvement.

Adding Operations to the Batch

When any statement object is first created, its statement batch is empty. Use the standard addBatch method to add an operation to the statement batch. This method is specified in the standard java.sql.Statement, PreparedStatement, and CallableStatement interfaces, which are implemented by the oracle.jdbc.OracleStatement, OraclePreparedStatement, and OracleCallableStatement interfaces, respectively.

For a Statement object, the addBatch method takes a Java String with a SQL operation as input. For example:

...
Statement stmt = conn.createStatement();

stmt.addBatch("INSERT INTO emp VALUES(1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO dept VALUES(260, 'Sales')");
stmt.addBatch("INSERT INTO emp_dept VALUES(1000, 260)");
...

At this point, three operations are in the batch.

For prepared statements, update batching is used to batch multiple runs of the same statement with different sets of bind parameters. For a PreparedStatement or OraclePreparedStatement object, the addBatch method takes no input. It simply adds the operation to the batch using the bind parameters last set by the appropriate setXXX methods. This is also true for CallableStatement or OracleCallableStatement objects, but remember that in the Oracle implementation of standard update batching, you will probably see no performance improvement in batching callable statements.

For example:

...
PreparedStatement pstmt = 
          conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");

pstmt.setInt(1, 2000);
pstmt.setString(2, "Milo Mumford");
pstmt.addBatch();

pstmt.setInt(1, 3000);
pstmt.setString(2, "Sulu Simpson");
pstmt.addBatch();
...

At this point, two operations are in the batch.

Because a batch is associated with a single prepared statement object, you can batch only repeated runs of a single prepared statement, as in this example.

Processing the Batch

To process the current batch of operations, use the executeBatch method of the statement object. This method is specified in the standard Statement interface, which is extended by the standard PreparedStatement and CallableStatement interfaces.

Following is an example that repeats the prepared statement addBatch calls shown previously and then processes the batch:

...
PreparedStatement pstmt = 
          conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");

pstmt.setInt(1, 2000);
pstmt.setString(2, "Milo Mumford");
pstmt.addBatch();

pstmt.setInt(1, 3000);
pstmt.setString(2, "Sulu Simpson");
pstmt.addBatch();

int[] updateCounts = pstmt.executeBatch();
...

Row Count per Iteration for Array DMLs

Starting from Oracle Database 12c Release 1 (12.1), the executeBatch method has been improved so that it returns an int array of size that is the same as the number of records in the batch and each item in the return array is the number of database table rows affected by the corresponding record of the batch. For example, if the batch size is 5, then the executeBatch method returns an array of size 5. In case of an error in between execution of the batch, the executeBatch method cannot return a value, instead it throws a BatchUpdateException. In this case, the exception itself carries an int array of size n as its data, where n is the number of successful record executions. For example, if the batch is of size 5 and the error occurs at the 4th record, then the BatchUpdateException has an array of size 3 (3 records executed successfully) and each item in the array represents how many rows were affected by each of them.

Committing the Changes in the Oracle Implementation of Standard Batching

After you process the batch, you must still commit the changes, presuming auto-commit is disabled as recommended.

Calling commit, commits nonbatched operations and batched operations for statement batches that have been processed, but for the Oracle implementation of standard batching, has no effect on pending statement batches that have not been processed.

Clearing the Batch

To clear the current batch of operations instead of processing it, use the clearBatch method of the statement object. This method is specified in the standard Statement interface, which is extended by the standard PreparedStatement and CallableStatement interfaces.

Keep the following things in mind:

• When a batch is processed, operations are performed in the order in which they were batched.

• After calling addBatch, you must call either executeBatch or clearBatch before a call to executeUpdate, otherwise there will be a SQL exception.

• A clearBatch or executeBatch call resets the statement batch to empty.

• The statement batch is not reset to empty if the connection receives a ROLLBACK request. You must explicitly call clearBatch to reset it.

  Note:

  • Oracle recommends not to use the clearBatch method with Oracle update batching. With Oracle update batching, binds can be implicitly flushed to the server at any point of time due to various conditions. This makes the behavior of the clearBatch method vary with each case and unpredictable at run time because the method cannot clear any bound data that is already flushed to the server. However, it is OK to use the clearBatch method after a rollback to clear any traces.

    Also, the clearBatch method displays this unpredictable behavior with only Oracle update batching and works correctly with standard update batching.

  • If you are using Oracle update batching in Oracle Database 12c Release 1 (12.1), then you do not have to clear your batches explicitly in the code after a rollback. However, if you are using Oracle update batching in an earlier release, then you have to invoke the clearBatch method to clear your batches explicitly after a rollback.

• Invoking clearBatch method after a rollback works for all releases.

• An executeBatch call closes the current result set of the statement object, if one exists.

• Nothing is returned by the clearBatch method.

Following is an example that repeats the prepared statement addBatch calls shown previously but then clears the batch under certain circumstances:

...
PreparedStatement pstmt = 
          conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");

pstmt.setInt(1, 2000);
pstmt.setString(2, "Milo Mumford");
pstmt.addBatch();

pstmt.setInt(1, 3000);
pstmt.setString(2, "Sulu Simpson");
pstmt.addBatch();

if (...condition...)
{
   int[] updateCounts = pstmt.executeBatch();
   ...
}
else
{
   pstmt.clearBatch();
   ...
}

Update Counts in the Oracle Implementation of Standard Batching

If a statement batch is processed successfully, then the integer array, or update counts array, returned by the statement executeBatch call will always have one element for each operation in the batch. In the Oracle implementation of standard update batching, the values of the array elements are as follows:

• For a prepared statement batch, the array contains the actual update counts indicating the number of rows affected by each operation.

• For a generic statement batch, the array contains the actual update counts indicating the number of rows affected by each operation. The actual update counts can be provided only in the case of generic statements in the Oracle implementation of standard batching.

• For a callable statement batch, the array contains the actual update counts indicating the number of rows affected by each operation.

In your code, upon successful processing of a batch, you should be prepared to handle either -2, 1, or true update counts in the array elements. For a successful batch processing, the array contains either all -2, 1, or all positive integers.

Example 21-2 illustrates the use of standard update batching.

conn.setAutoCommit(false);

PreparedStatement pstmt = 
          conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");

pstmt.setInt(1, 2000);
pstmt.setString(2, "Milo Mumford");
pstmt.addBatch();

pstmt.setInt(1, 3000);
pstmt.setString(2, "Sulu Simpson");
pstmt.addBatch();

int[] updateCounts = pstmt.executeBatch();

conn.commit();

pstmt.close();
...

Error Handling in the Oracle Implementation of Standard Batching

If any one of the batched operations fails to complete successfully or attempts to return a result set during an executeBatch call, then the processing stops and a java.sql.BatchUpdateException is generated.

After a batch exception, the update counts array can be retrieved using the getUpdateCounts method of the BatchUpdateException object. This returns an int array of update counts, just as the executeBatch method does. In the Oracle implementation of standard update batching, contents of the update counts array are as follows, after a batch is processed:

• For a prepared statement batch, in case of an error in between execution of the batch, the executeBatch method cannot return a value, instead it throws a BatchUpdateException. In this case, the exception itself carries an int array of size n as its data, where n is the number of successful record executions. For example, if the batch is of size 5 and the error occurs at the 4th record, then the BatchUpdateException has an array of size 3 (3 records executed successfully) and each item in the array represents how many rows were affected by each of them.

• For a generic statement batch or callable statement batch, the update counts array is only a partial array containing the actual update counts up to the point of the error. The actual update counts can be provided because Oracle JDBC cannot use true batching for generic and callable statements in the Oracle implementation of standard update batching.

  For example, if there were 20 operations in the batch, the first 13 succeeded, and the 14th generated an exception, then the update counts array will have 13 elements, containing actual update counts of the successful operations.

  You can either commit or roll back the successful operations in this situation, as you prefer.

In your code, upon failed processing of a batch, you should be prepared to handle either -3 or true update counts in the array elements when an exception occurs. For a failed batch processing, you will have either a full array of -3 or a partial array of positive integers.

Intermixing Batched Statements and Nonbatched Statements

You cannot call executeUpdate for regular, nonbatched processing of an operation if the statement object has a pending batch of operations.

However, you can intermix batched operations and nonbatched operations in a single statement object if you process nonbatched operations either prior to adding any operations to the statement batch or after processing the batch. Essentially, you can call executeUpdate for a statement object only when its update batch is empty. If the batch is non-empty, then an exception will be generated.

For example, it is valid to have a sequence, such as the following:

...
PreparedStatement pstmt = 
          conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");

pstmt.setInt(1, 2000);
pstmt.setString(2, "Milo Mumford");

int scount = pstmt.executeUpdate();   // OK; no operations in pstmt batch

pstmt.setInt(1, 3000);
pstmt.setString(2, "Sulu Simpson");
pstmt.addBatch();                    // Now start a batch

pstmt.setInt(1, 4000);
pstmt.setString(2, "Stan Leland");
pstmt.addBatch();

int[] bcounts = pstmt.executeBatch();

pstmt.setInt(1, 5000);
pstmt.setString(2, "Amy Feiner");

int scount = pstmt.executeUpdate();   // OK; pstmt batch was executed
...

Intermixing nonbatched operations on one statement object and batched operations on another statement object within your code is permissible. Different statement objects are independent of each other with regard to update batching operations. A COMMIT request will affect all nonbatched operations and all successful operations in processed batches, but will not affect any pending batches.