In a previous article about (network) latencies, I have presented the OracleStatement.setRowPrefetch() method to reduce round trips for SELECT statements. I must admit that I was a little bit wrong. Not in the essence; increasing the PreFetch size is still a great (read: simple) way to reduce latencies. However, there is a better approach to set the PreFetch size.

Since the raise of Java, many portability issues have vanished. There are most definitely new ones—think of mobile devices—and some others are still eminent—think of Internet Explorer versus all other browsers. Nevertheless, Java improved the situation quite notable—most credible: Unicode support from the beginning.

Still there are some people like me who just cast the portable JDBC class to whatever they believe it is, and do some cool things with that. Every now and then there is the need to use proprietary functions, but I should have checked the portable API first. Shame on me. In fact there is the standard JDBC method Statement.setFetchSize() that does exactly the same. Even more interesting, it’s there since ages (JDBC 2.1 at least).

Although the documentation seems to be exactly what I was looking for, I wrote a little test program to verify. Just because I know that Oracle can profit from an increased PreFetch size and the standard provides a method to set it, it doesn’t mean that it works. Especially not if the standards says that the method is a hint to the JDBC driver. So let’s double check.

import java.sql.*;
import oracle.jdbc.*;

class JDBCFetchSize {
  public static void main(String[] argv) throws Exception {
    if (argv.length != 4) {
      throw new RuntimeException("JDBCFetchSize <jdbc-connect url> " 
                               + "<username> <password> <FetchSize>");
    }
    Connection con = connect(argv[0], argv[1], argv[2]);
    int roundtripoffset = getRoundTripOffset(con);

    PreparedStatement ps = con.prepareStatement(seqsql);

    setFetchSize(ps, argv[3]);

    int start = getCurrentRoundTripCount(con);
    long startts = System.currentTimeMillis();

    int rowsfetched = executeTest(ps);

    int end    = getCurrentRoundTripCount(con) - roundtripoffset;
    long endts = System.currentTimeMillis();

    ps.close();

    System.out.println(rowsfetched + " rows fetched in " 
                       + (end-start) + " server round trips and " 
                       + (endts-startts) + " ms");
  }

  // oracle SQL to fetch round trip counter
  final static String rtsql = 
      " select value "
    + "   from v$mystat ms, v$statname sn" 
    + "  where ms.value > 0"
    + "    and ms.statistic#=sn.statistic#"
    + "    and sn.name IN ('SQL*Net roundtrips to/from client')";

  // sequence generator
  final static String seqsql = 
      " select level from dual connect by level <= ?";

  static PreparedStatement rtstm;

  private static Connection
    connect (String url, String user, String pass) 
    throws ClassNotFoundException, SQLException
  {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    return DriverManager.getConnection(url, user, pass);
  }

  private static int 
    getRoundTripOffset(Connection con) 
    throws SQLException
  {
    // The very first time we call it, it is not prepared. 
    // so we don't consider that at all.
    getCurrentRoundTripCount(con);

    int first = getCurrentRoundTripCount(con);
    return getCurrentRoundTripCount(con) - first;
  }

  static void 
    setFetchSize(Statement stm, String size)
    throws SQLException
  {
    System.out.println("getFetchSize(): " + stm.getFetchSize());

    stm.setFetchSize(Integer.parseInt(size));

    // uncomment the not portable OracleStatement 
    // variant for verification
    // ((OracleStatement)stm).setRowPrefetch(Integer.parseInt(size));

    System.out.println("getFetchSize(): " + stm.getFetchSize());
  }

  static int 
    executeTest(PreparedStatement ps)
    throws SQLException
  {
    int cnt = 0;
    ps.setInt(1, 100000);
    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
      ++cnt;
    }
    rs.close();
    return cnt;
  }

  static int
    getCurrentRoundTripCount(Connection con) 
    throws SQLException
  {
    if (rtstm == null) {
      rtstm = con.prepareStatement(rtsql);
    }
    ResultSet rs = rtstm.executeQuery();
    rs.next();
    int rv = rs.getInt(1);
    rs.close();

    return rv;
  }
}

The result proofs that the portable JDBC setFetchSize() is the better choice over Oracle’s proprietary setRowPrefetch():

$ java JDBCFetchSize jdbc:oracle:thin:@//host/service user password 10
getFetchSize(): 10
getFetchSize(): 10
100000 rows fetched in 10002 server round trips and 521 ms
$ java JDBCFetchSize jdbc:oracle:thin:@//host/service user password 20
getFetchSize(): 10
getFetchSize(): 20
100000 rows fetched in 5002 server round trips and 324 ms
$ java JDBCFetchSize jdbc:oracle:thin:@//host/service user password 50
getFetchSize(): 10
getFetchSize(): 50
100000 rows fetched in 2002 server round trips and 191 ms
$ 

Calling setRowPrefetch() affects the number of server round trips as expected.

And because consistency (=Quality) matters, I will right away update the previous article.

Recent oracle docs list some limitations.

After all, there is still some room for portability pitfalls. If you want to change the default value from 10 to some other value, Oracle has two solutions for that. Read again: Oracle has two solutions for that. JDBC has none (correct me if I am wrong).

When passing Properties to the DriverManager.getConnection() method, Oracle allows to set the defaultRowPrefetch property. The other way is to cast the JDBC Connection to OracleConnection and use the setDefaultRowPrefetch() method (hurray — cast again!). Disclaimer: I did not test any of those.

Please note that my tests were based on Oracle 11r2, other release might behave differently. During my research I found some Oracle 8.1.6 docs which also mention the portable setFetchSize() method, that makes me believe it works since then.