Performance Enhancement Samples

This section provides sample applications for performance enhancement features such as update batching:

• Standard Update Batching--BatchUpdates.java

• Oracle Update Batching with Implicit Execution--SetExecuteBatch.java

• Oracle Update Batching with Explicit Execution--SendBatch.java

• Oracle Row Prefetching Specified in Connection--RowPrefetch_connection.java

• Oracle Row Prefetching Specified in Statement--RowPrefetch_statement.java

• Oracle Column Type Definitions--DefineColumnType.java

The sample applications for Oracle-specific performance enhancements are located in the following directory on the product CD:

[Oracle Home]/jdbc/demo/samples/oci8/basic-samples

The standard update batching sample is located in the jdbc20-samples directory.

Standard Update Batching--BatchUpdates.java

This sample shows how to use standard update batching as specified by JDBC 2.0. For more information, see "Standard Update Batching".

For comparison and contrast between the standard and Oracle-specific update batching models, see "Overview of Update Batching Models".

/**
 * A simple sample to demonstrate standard JDBC 2.0 update batching.
 */

import java.sql.*;

public class BatchUpdates
{
  public static void main(String[] args)
  {
    Connection          conn = null;
    Statement           stmt = null;
    PreparedStatement   pstmt = null;
    ResultSet           rset = null;
    int                 i = 0;

    try
    {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

      conn = DriverManager.getConnection(
               "jdbc:oracle:oci8:@", "scott", "tiger");

      stmt = conn.createStatement();
      try { stmt.execute(
            "create table mytest_table (col1 number, col2 varchar2(20))");
      } catch (Exception e1) {}

      //
      // Insert in a batch.
      //
      pstmt = conn.prepareStatement("insert into mytest_table values (?, ?)");

      pstmt.setInt(1, 1);
      pstmt.setString(2, "row 1");
      pstmt.addBatch();

      pstmt.setInt(1, 2);
      pstmt.setString(2, "row 2");
      pstmt.addBatch();

      pstmt.executeBatch();

      //
      // Select and print results.
      //
      rset = stmt.executeQuery("select * from mytest_table");
      while (rset.next())
      {
        System.out.println(rset.getInt(1) + ", " + rset.getString(2));
      }
    }
    catch (Exception e)
    {
      e.printStackTrace();
    }
    finally
    {
      if (stmt != null)
      {
        try { stmt.execute("drop table mytest_table"); } catch (Exception e) {}
        try { stmt.close(); } catch (Exception e) {}
      }
      if (pstmt != null)
      {
        try { pstmt.close(); } catch (Exception e) {}
      }
      if (conn != null)
      {
        try { conn.close(); } catch (Exception e) {}
      }
    }
  }
}

Oracle Update Batching with Implicit Execution--SetExecuteBatch.java

This sample shows how to use Oracle update batching, with the batch being executed implicitly when the batch value (the number of statements to collect before sending them to the database) is reached.

For information about Oracle update batching, see "Oracle Update Batching".

For comparison and contrast between the standard and Oracle-specific update batching models, see "Overview of Update Batching Models".

/*
 * This sample shows how to use the batching extensions.
 * In this example, we set the defaultBatch value from the
 * connection object. This affects all statements created from
 * this connection. 
 * It is possible to set the batch value individually for each 
 * statement. The API to use on the statement object is setExecuteBatch().
 *
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;

// You need to import oracle.jdbc.driver.* in order to use the
// API extensions.
import oracle.jdbc.driver.*;

class SetExecuteBatch
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Default batch value set to 2 for all prepared statements belonging
    // to this connection.
    ((OracleConnection)conn).setDefaultExecuteBatch (2);

    PreparedStatement ps =
      conn.prepareStatement ("insert into dept values (?, ?, ?)");
    
    ps.setInt (1, 12);
    ps.setString (2, "Oracle");
    ps.setString (3, "USA");

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

    ps.setInt (1, 11);
    ps.setString (2, "Applications");
    ps.setString (3, "Indonesia");

    // The number of batch calls to executeUpdate is now equal to the
    // batch value of 2.  The data is now sent to the database and
    // both rows are inserted in a single roundtrip.
    int rows = ps.executeUpdate ();
    System.out.println ("Number of rows updated now: " + rows);      
  
    ps.close ();
    conn.close();
  }
}

Oracle Update Batching with Explicit Execution--SendBatch.java

This sample shows how to use Oracle update batching, with the batch being executed explicitly with a sendBatch() call.

For information about Oracle update batching, see "Oracle Update Batching".

For comparison and contrast between the standard and Oracle-specific update batching models, see "Overview of Update Batching Models".

/*
 * This sample shows how to use the batching extensions.
 * In this example, we demonstrate the use of the "sendBatch" API.
 * This allows the user to actually execute a set of batched
 * execute commands.
 *
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;

// You need to import oracle.jdbc.driver.* in order to use the
// API extensions.
import oracle.jdbc.driver.*;

class SendBatch
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    Statement stmt = conn.createStatement ();

    // Default batch value set to 50 for all prepared statements belonging
    // to this connection.
    ((OracleConnection)conn).setDefaultExecuteBatch (50);

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

    // this execute does not actually happen at this point
    System.out.println (ps.executeUpdate ());     

    ps.setInt (1, 33);
    ps.setString (2, "Applications");
    ps.setString (3, "Indonesia");

    // this execute does not actually happen at this point
    int rows = ps.executeUpdate ();  
    
    System.out.println ("Number of rows updated before calling sendBatch: "
                                       + 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 ();
    conn.close ();
  }
}

Oracle Row Prefetching Specified in Connection--RowPrefetch_connection.java

This section demonstrates how to use Oracle row prefetching-functionality, setting the row prefetch value in the connection object and thereby affecting every statement produced from that connection.

Note that Oracle row prefetching is fundamentally similar to JDBC 2.0 fetch size functionality.

For information about Oracle row prefetching, see "Oracle Row Prefetching". For information about JDBC 2.0 fetch size and some comparison with row prefetching, see "Fetch Size".

/*
 * This sample shows how to use the Oracle performance extensions
 * for row-prefetching. This allows the driver to fetch multiple
 * rows in one round-trip, saving unecessary round-trips to the database.
 *
 * This example shows how to set the rowPrefetch for the connection object,
 * which will be used for all statements created from this connection.
 * Please see RowPrefetch_statement.java for examples of how to set
 * the rowPrefetch for statements individually.
 *
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;

// You need to import oacle.jdbc.driver in order to use the oracle extensions.
import oracle.jdbc.driver.*;

class RowPrefetch_connection
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // set the RowPrefetch value from the Connection object
    // This sets the rowPrefetch for *all* statements belonging
    // to this connection.
    // The rowPrefetch value can be overriden for specific statements by
    // using the setRowPrefetch API on the statement object. Please look
    // at RowPrefetch_statement.java for an example.

    
    // Please note that any statements created *before* the connection
    // rowPrefetch was set, will use the default rowPrefetch.

    ((OracleConnection)conn).setDefaultRowPrefetch (30);
    
    Statement stmt = conn.createStatement ();
    
    // Check to verify statement rowPrefetch value is 30.
    int row_prefetch = ((OracleStatement)stmt).getRowPrefetch ();
    System.out.println ("The RowPrefetch for the statement is:  "
                                         + row_prefetch + "\n");

    ResultSet rset = stmt.executeQuery ("select ename from emp");
    
    while(rset.next ())
    {
      System.out.println (rset.getString (1));
    }
    rset.close ();
    stmt.close ();
    conn.close (); 
  }
}

Oracle Row Prefetching Specified in Statement--RowPrefetch_statement.java

This section demonstrates how to use Oracle row prefetching functionality, setting the row prefetch value in a particular statement object to override the value in the connection object producing the statement.

Note that Oracle row prefetching is fundamentally similar to JDBC 2.0 fetch size functionality.

For information about Oracle row prefetching, see "Oracle Row Prefetching". For information about JDBC 2.0 fetch size and some comparison with row prefetching, see "Fetch Size".

/*
 * This sample shows how to use the Oracle performance extensions
 * for row-prefetching. This allows the driver to fetch multiple
 * rows in one round-trip, saving unecessary round-trips to the database.
 *
 * This example shows how to set the rowPrefetch for individual
 * statements.
 *
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;

// You need to import oracle.jdbc.driver in order to use the
// Oracle extensions
import oracle.jdbc.driver.*;

class RowPrefetch_statement
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // get the value of the default row prefetch from the connection object

    int default_row_prefetch =
      ((OracleConnection)conn).getDefaultRowPrefetch ();
    System.out.println ("The Default RowPrefetch for the connection is: "
                                        + default_row_prefetch);

    Statement stmt = conn.createStatement ();

    // set the RowPrefetch value from the statement object
    // This sets the rowPrefetch only for this particular statement.
    // All other statements will use the default RowPrefetch from the
    // connection.

    ((OracleStatement)stmt).setRowPrefetch (30);
    
    // Check to verify statement rowPrefetch value is 30.
    int row_prefetch = ((OracleStatement)stmt).getRowPrefetch ();
    System.out.println ("The RowPrefetch for the statement is: "
                                         + row_prefetch + "\n");

    ResultSet rset = stmt.executeQuery ("select ename from emp");
    
    while(rset.next ())
    {
      System.out.println (rset.getString (1));
    }
    rset.close ();
    stmt.close ();
    stmt.close ();
  }
}

Oracle Column Type Definitions--DefineColumnType.java

This sample shows how to use Oracle extensions to predefine result set column types to reduce round trips to the database for a query.

For information about column type definitions, see "Defining Column Types".

/*
 * This sample shows how to use the "define" extensions.
 * The define extensions allow the user to specify the types
 * under which to retrieve column data in a query. 
 *
 * This saves round-trips to the database (otherwise necessary to
 * gather information regarding the types in the select-list) and
 * conversions from native types to the types under which the user
 * will get the data.
 *
 * This can also be used to avoid streaming of long columns, by defining
 * them as CHAR or VARCHAR types.
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;

// You need to import oracle.jdbc.driver.* in order to use the
// API extensions.
import oracle.jdbc.driver.*;

class DefineColumnType
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    Statement stmt = conn.createStatement ();

    // Call DefineColumnType to specify that the column will be
    // retrieved as a String to avoid conversion from NUMBER to String
    // on the client side.  This also avoids a round-trip to the
    // database to get the column type.
    //
    // There are 2 defineColumnType API.  We use the one with 3 arguments.
    // The 3rd argument allows us to specify the maximum length
    // of the String.  The values obtained for this column will
    // not exceed this length.

    ((OracleStatement)stmt).defineColumnType (1, Types.VARCHAR, 7);

    ResultSet rset = stmt.executeQuery ("select empno from emp");
    while (rset.next ())
    {
      System.out.println (rset.getString (1));
    }

    // Close the resultSet
    rset.close();

    // Close the statement
    stmt.close ();
 
    // Close the connection
    conn.close();
  }
}

Implicit Statement Caching--StmtCache1.java

This sample application uses implicit statement caching to create a result set from a database and then print out various information, including employee names.

For more information on implicit statement caching, see "Using Implicit Statement Caching" and the other pertinent sections in Chapter 14, "Statement Caching".

/*
 * This sample to demonstrate Implicit Statement Caching. This can be
 * enabled by calling setStmtCacheSize on the Connection Object.
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;
import oracle.jdbc.driver.*;

class StmtCache1
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    ((OracleConnection)conn).setStmtCacheSize(1);
    
    Connection sysconn = DriverManager.getConnection("jdbc:oracle:oci8:@", 
                                                     "system", "manager");
    String sql = "select ENAME from EMP";

    System.out.println("Beging of 1st execution");
    getOpenCursors (sysconn);

    // Create a Statement
    PreparedStatement stmt = conn.prepareStatement (sql);
    System.out.println("1. Stmt is " + stmt);

    // Select the ENAME column from the EMP table
    ResultSet rset = stmt.executeQuery ();

    // Iterate through the result and print the employee names
    while (rset.next ())
      System.out.println (rset.getString (1));

    // Close the RseultSet
    rset.close();

    // Close the Statement
    stmt.close();

    System.out.println("End of 1st execution");
    getOpenCursors (sysconn);

    System.out.println("Reexecuting the same SQL");

    stmt = conn.prepareStatement (sql);

    System.out.println("2. Stmt is " + stmt);

    // Select the ENAME column from the EMP table
    rset = stmt.executeQuery ();

    // Iterate through the result and print the employee names
    while (rset.next ())
      System.out.println (rset.getString (1));

    // Close the RseultSet
    rset.close();

    // Close the Statement
    stmt.close();
  
    System.out.println("End of 2nd execution");
    getOpenCursors (sysconn);

    // Close the connection
    conn.close();   

    System.out.println("After close of connection");
    getOpenCursors (sysconn);

    sysconn.close();
  }

  private static void getOpenCursors (Connection conn)
     throws SQLException
  {
     System.out.println("Open Cusrors are : ");
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery ("select SQL_TEXT from V$OPEN_CURSOR");
     while (rs.next())
       System.out.println("Cursor's sql text is " + rs.getString(1));
     rs.close();
     rs = null;
     stmt.close();
     stmt = null;
  }
}

Explicit Statement Caching--StmtCache2.java

This sample application uses explicit statement caching to create a result set from a database and then print out various information including employee names.

For more information on implicit statement caching, see "Using Explicit Statement Caching" and the other pertinent sections in Chapter 14, "Statement Caching".

/*
 * This sample to demonstrate Explicit Statement Caching. This can be
 * enabled by calling Oracle Specific calls like closeWithKey, 
 * prepareStatementWithKey etc.
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;
import oracle.jdbc.driver.*;

class StmtCache2
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    ((OracleConnection)conn).setStmtCacheSize(1);
    
    Connection sysconn = DriverManager.getConnection("jdbc:oracle:oci8:@", 
                                                     "system", "manager");
    String sql = "select ENAME from EMP";

    System.out.println("Beging of 1st execution");
    getOpenCursors (sysconn);

    // Create a Statement
    PreparedStatement stmt = conn.prepareStatement (sql);
    System.out.println("1. Stmt is " + stmt);

    // Select the ENAME column from the EMP table
    ResultSet rset = stmt.executeQuery ();

    // Iterate through the result and print the employee names
    while (rset.next ())
      System.out.println (rset.getString (1));

    // Close the RseultSet
    rset.close();

    // Close the Statement
    //stmt.close();
    ((OracleStatement)stmt).closeWithKey ("mysql");

    System.out.println("End of 1st execution");
    getOpenCursors (sysconn);

    System.out.println("Reexecuting the same SQL");

    stmt = ((OracleConnection)conn).prepareStatementWithKey ("mysql");

    System.out.println("2. Stmt is " + stmt);

    // Select the ENAME column from the EMP table
    rset = stmt.executeQuery ();

    // Iterate through the result and print the employee names
    while (rset.next ())
      System.out.println (rset.getString (1));

    // Close the RseultSet
    rset.close();

    // Close the Statement
    stmt.close();
  
    System.out.println("End of 2nd execution");
    getOpenCursors (sysconn);

    // Close the connection
    conn.close();   

    System.out.println("After close of connection");
    getOpenCursors (sysconn);

    sysconn.close();
  }

  private static void getOpenCursors (Connection conn)
     throws SQLException
  {
     System.out.println("Open Cusrors are : ");
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery ("select SQL_TEXT from V$OPEN_CURSOR");
     while (rs.next())
       System.out.println("Cursor's sql text is " + rs.getString(1));
     rs.close();
     rs = null;
     stmt.close();
     stmt = null;
  }
}

---