Performance Optimization

You can significantly enhance the performance of your JDBC programs by using any of these features:

• Disabling Auto-Commit Mode

• Prefetching Rows

• Batching Updates

Disabling Auto-Commit Mode

Auto-commit mode indicates to the database whether to issue an execute and commit after every SQL statement. Being in auto-commit mode can be expensive in terms of time and processing effort if, for example, you are repeating the same statement with different bind variables.

By default, new connection objects are in auto-commit mode. However, you can disable auto-commit mode with the setAutoCommit() method of the connection object (either java.sql.Conection or oracle.jdbc.OracleConnection).

In auto-commit mode, the commit occurs either when the statement completes or the next execute occurs, whichever comes first. In the case of statements returning a ResultSet, the statement completes when the last row of the ResultSet has been retrieved or when the ResultSet has been closed. In more complex cases, a single statement can return multiple results as well as output parameter values. Here, the commit occurs when all results and output parameter values have been retrieved.

If you disable auto-commit mode (setAutoCommit(false)), then the JDBC driver groups the connection's SQL statements into transactions that it terminates by either a commit() or rollback() statement.

Example: Disabling AutoCommit

The following example illustrates loading the driver and connecting to the database. Since new connections are in auto-commit mode by default, this example shows how to disable auto-commit. In the example, conn represents the Connection object and stmt represents the Statement object.

// Load the Oracle JDBC driver 
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
 
    // Connect to the database 
    // You can put a database hostname after the @ sign in the connection URL. 
    Connection conn = 
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger"); 
 
    // It's faster when auto commit is off 
    conn.setAutoCommit (false); 
 
    // Create a Statement 
    Statement stmt = conn.createStatement (); 
    ...

Prefetching Rows

Oracle JDBC drivers allow you to set the number of rows to prefetch into the client while the result set is being populated during a query. The default number of rows to prefetch is 10. Prefetching row data into the client reduces the number of round trips to the server. In contrast, standard JDBC fetches the result set one row at a time, where each row requires a round trip to the database.

You can set the row prefetching value for an individual statement or for all statements in your connection. For a description of row prefetching and how to enable it, see "Row Prefetching".

Batching Updates

The Oracle JDBC drivers allow you to accumulate inserts and updates of prepared statements at the client and send them to the server in batches once it reaches a specified batch value. This feature reduces round trips to the server. The default batch value is one.

You can set the batch value for any individual Oracle prepared statement or for all Oracle prepared statements in your Oracle connection. For a description of update batching and how to enable it, see "Database Update Batching".

---