Markus Winand's Blog

Oracle JDBC PreFetch Portability

In Portability on 2010-01-29 at 14:23

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.


Do not use offset for pagination. Learn why.

SQL Performance Tuning Book for developers
  1. Markus – Your sample code was very helpful but I replaced the sample query (seqsql ) with a call to stored proc and ran the program. I see that number of round trips are unchanged whatever fetch size I set. Do you know if it does not work with stored proc. I did verify that it works with sample query you provided.
    DB – Oracle 10g.
    Used ojdbc.jar driver that came with Weblogic 10.1.

    Thanks
    Vijay

    • Dear Vijay,

      Prefetching works for select statements only. Prefetching means to retrieve multiple rows (from the select) in one server roundtrip. That is, instead of every ResultSet.next() to contact the server, only every 10th (as per default) does. However, the saving is in the ResultSet.next() call.

      In other words: if you dont’ call ResultSet.next() within a loop, you can not reduce latency with prefetch.

      However, I guess, without verification, that you could benefit from prefetch if you fetch a ResultSet from a stored procedure (e.g, with a REFCURSOR or a table function).

      • Thanks Markus for the prompt reply. I am attaching part of the updated code I updated here. My stored procedure is creating a global temp table and it is being returned as OracleTypes.CURSOR.
        I see that about 5 seconds is taken in executing the proc and 20 seconds in ResultSet.next loop. I am expecting reduction from 20seconds by prefetch. Do you think my understanding is right? Thanks again.
        updated program-
        *************
        con.setAutoCommit(false);
        CallableStatement ps = con.prepareCall(“{call review_allocation_pkg.review_proc(?,?)}”);
        //statement.registerOutParameter(1, OracleTypes.CURSOR);
        ps.registerOutParameter(1, OracleTypes.CURSOR);
        ps.setString(2, “2267294”);
        setFetchSize(ps, argv[3]);
        ********************

        static int
        executeTest(CallableStatement ps)
        throws SQLException
        {
        int cnt = 0;
        //ps.setInt(1, 100000);
        ps.execute();
        ResultSet rs = (ResultSet) ps.getObject(1);
        while (rs.next()) {
        ++cnt;
        }
        rs.close();
        return cnt;
        }
        **********************
        Result1-

        getFetchSize(): 10
        getFetchSize(): 1
        31850 rows fetched in 3187 server round trips and 21446 ms
        *********************
        Result 2-

        getFetchSize(): 10
        getFetchSize(): 1000
        31850 rows fetched in 3187 server round trips and 20927 ms

      • Dear Vijay,

        I like to respond fast, if I can.

        As I mentioned in my previous comment, I have never tested that thing with REFCURSORS—however, might be an interesting topic. Unfortunately I do not have the timeto look into that in much detail.

        Regardless of that, the figures you sent made me doubt that your problem is related to the network latency—at least not within a LAN. A typical network latency in a LAN is 0.xx (zero-dot-something) milliseconds. Let’s assume it is 1 ms, that would sum up to about 3.2 seconds for all the server round trips—about 15% of the overall execution time. Unless you are doing this on a WAN or you have a really slow LAN, I’d guess that the performance bottleneck is the procedure itself, not the data transfer to the client.

        However, your observation is interesting. I hope I can have a closer look at it some other time.

  2. Thanks Markus. To eliminate any problem due to network, I was running these test from an unix box co-located with the DB server in the same Data Center. I am pretty confident there is very very minimal impact of network if any. Also I made another change to the code. So instead of returning cursor from proc. I just populate temp table using proc and then wrote PreparedStatement and ran seperate query (in same session) to fetch all results from temp table. Here I am seeing difference in response times based on fetchsize. (Results below). However by doing so I am incurring extra cost of running another query which I want to avoid and somehow make setFetchSize to work with Cursor and Stored procedure itself. I will try to follow up with Oracle to see if they can help looking at the Driver.

    final static String seqsql_new = “SELECT DISTINCT av.* FROM ab4p.alloc_review_vw av WHERE alloc_id = 2267294”;

    static int
    executeTest(CallableStatement ps, PreparedStatement ps_new)
    throws SQLException
    {
    int cnt = 0;
    //ps.setInt(1, 100000);
    // ps.execute(); — this is run from outside
    //Get Resultset from SP
    //ResultSet rs = (ResultSet) ps.getObject(1);

    //Get Result set from qury to Temp Table produced by SP.
    ResultSet rs = ps_new.executeQuery();

    while (rs.next()) {
    ++cnt;
    }
    rs.close();
    return cnt;
    }

    Results using PreparedStatement for Fetching data after Proc run
    —————————————————————-

    Time for ResultSet.next() loop
    FetchSize first run second run
    1 42784 42951
    10 11673 11863
    5000 8037 7645
    10000 8373 7985

    Note: I had to run the test with different data item this time.
    Again thanks a lot for all your comments.