Saturday, 15 March 2008

JDBC Fetch Size

Fetch size is a performance hint which tells the JDBC driver how many rows should be fetched from the database when more rows are needed by the client. Increasing this value can improve overall query performance, especially with large result sets.

The required fetch size can be set on a Statement or a ResultSet object using the setFetchSize method. If used with a Statement then all ResultSets returned by that Statement will have the same fetch size. The default is ten.

Setting a JDBC fetch size that is too large or too small can degrade performance. Usually, a fetch size of one half or one quarter of the total expected result size is optimal.

Example

To see the effect of using different fetch sizes with Oracle, we first create a table containing a million rows using the CONNECT BY LEVEL clause.

CREATE TABLE one_million_rows AS
SELECT rownum id, rpad('x', 20, 'x') || rownum a, sysdate b
FROM dual
CONNECT BY LEVEL <= 1000000;
Table created.

Next, we call the following method several times with different fetch sizes and record the elapsed time taken to retrieve the data. SQL tracing is also enabled, allowing us to create a TKPROF report for each run.

public void testFetchSize(int fetchSize) throws SQLException {
  Connection con = ConnectionFactory.getNewConnection();
Statement stmt = con.createStatement();
  stmt.execute(
"ALTER SESSION SET tracefile_identifier = " +
"FETCH_SIZE_" + fetchSize);
stmt.execute(
"ALTER SESSION SET events " +
"'10046 trace name context forever, level 12'");
  ResultSet rs = stmt.executeQuery(
"SELECT /* FETCH SIZE " + fetchSize + " */ * " +
"FROM one_million_rows");
rs.setFetchSize(fetchSize);
  long start = System.currentTimeMillis();
while(rs.next());
System.out.printf("Fetch size %d took %4.2f seconds\n",
fetchSize,
(System.currentTimeMillis() - start) / 1000f);
  stmt.execute(
"ALTER SESSION SET events '10046 trace name context off'");

}

For this exercise we have used event 10046 to enable SQL tracing (rather than sql_trace) because we need to capture the wait events in addition to the execution statistics. We will see why later.

Note: Starting with Oracle 10g release 2, the preferred method of enabling SQL tracing is with the DBMS_SESSION package. The ALTER SESSION SET EVENTS command shown above can now be enabled with the following procedure call.

DBMS_SESSION.SESSION_TRACE_ENABLE(waits=>TRUE, binds=>TRUE);

Results

Here is a summary of the results.

Fetch SizeElapsed Time (secs)
1093.78
5029.48
10016.76
2009.58
5006.22
10004.86
50003.91
100003.79
500003.93
1000003.98

Clearly, the fetch size has a considerable impact on the overall performance of our query. The difference between the fastest, which was under four seconds, and the slowest, which was over a minute and a half, is certainly not trivial.

We can also see that performance starts to degrade slightly with a fetch size of somewhere between ten and fifty thousand. From these results, it would appear that ten thousand is a reasonable value for our fetch size.

TKPROF

We can also see the difference in performance with the TKPROF reports produced during the tests. Here, we compare a fetch size of ten to a fetch size of ten thousand.
SELECT /* FETCH SIZE 10 */ * 
FROM
one_million_rows

call count cpu elapsed query rows
------- ------ -------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0
Execute 1 0.00 0.00 0 0
Fetch 100001 7.40 6.77 101220 1000000
------- ------ -------- ---------- ---------- ----------
total 100003 7.40 6.77 101220 1000000
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
-------------------------------- Waited ---------- ------------
SQL*Net message to client 100001 0.00 0.37
SQL*Net message from client 100001 0.05 65.21
************************************************************************

SELECT /* FETCH SIZE 10000 */ *
FROM
one_million_rows

call count cpu elapsed query rows
------- ------ -------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0
Execute 1 0.00 0.00 0 0
Fetch 101 1.98 3.03 11573 1000000
------- ------ -------- ---------- ----------- -----------
total 103 1.98 3.03 11573 1000000
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
-------------------------------- Waited ---------- ------------
SQL*Net message to client 101 0.00 0.00
SQL*Net message from client 101 0.11 0.77
SQL*Net more data to client 17740 0.07 1.35
************************************************************************

Note: The report has been condensed to fit on the page.

Increasing the fetch size to ten thousand has more than halved the CPU and elapsed times and reduced consistent (logical) reads by almost ninety percent. However, the most significant gain is in the reduction of time spent waiting for a response from the client, from over a minute to less than a second.

7 comments:

Jean-Marc Autexier said...

Very good description, thanks Mark!
Like you blog a lot.

Mark Lishman said...

Nice of you to say so Jean-Marc. Thankyou. It's time I got some new content on here!

John Gardner said...

Good info!

Aren't there other hints to use to make this even faster if you're just retrieving the whole table?

How does this affect a real query that would return a subset of rows?

Anonymous said...

Thanks every for your information not to mention feed-back Respect.

Anonymous said...

Nice one! If I could write like this I would be well chuffed. The more I read articles of such quality as this (which is rare), the more I think there might be a future for the Net. Keep it up, as it were.

Michael-O said...

What did you pass to tkprof to get that table?

steve7876 said...

Great information about topic i am really like it. Specially in the middle of the article the points are too awesome.I like your Ibeacons points.