Setting Oracle size of row fetches higher makes my app slower?

Question

As detailed here and confirmed here, the default number of rows Oracle returns at time when querying for data over JDBC is 10. I am working on an app that to has to read and compare lots of data from our database. I thought that if we just increase defaultRowPrefetch to be something like 1000, then surely our app would perform faster. As it turned out, it performed slower and by about 20%.

We then decided to just slowly increase the number from 10 and see how it performs. We've seen about a 10% increase by setting it somewhere between 100 and 200. I would have never guessed, however, that setting it higher would make our app perform more slowly. Any ideas why this might happen?

Thanks!

EDIT:

Just for clarification, I'm using Oracle 11g R2 and Java 6.

EDIT 2:

Okay, I wanna restate my question to be clear, because judging from the answers below, I am not expressing myself properly:

How is it possible that if I set a higher fetch size, my app performs slower? To me, that sounds like saying "We're giving you a faster internet connection, i.e. a fatter pipe, but your web browsing will be slower.

All other things being equal, as they have been in our tests, we're super curious about how our app could perform worse with only this one change.

Solution 1

Possible explanations:

1. Java is doing nothing, while Oracle is computing the first 1000 rows instead of first 10.

2. Oracle is doing nothing, while Java is computing the last 1000 rows instead of last 10.

3. Communication protocols (e.g. TCP/IP) wait a lot and then must handle more data at once, but the peak data transfer will be throttled down by hardware limits. This is countered by protocol's overhead, so there should be optimal fetch size and anything less or more would be slower ;))

4. It would get worse if the process of fetching is synchronous with other Java code, so that Java asks for more rows only after processing the previous data and Oracle does nothing in the mean time.

   Imagine there are 3 people:

   • 1st one folds A4 paper in half
   • 2nd one brings stacks of folded paper from one room to another
   • 3rd cuts some shape from the folded paper.

   How big should the stacks be, if the 1st one has to wait until the 2nd one returns and the 2nd one has to wait until the 3rd one finishes their job?

   Stacks of 1000 will not be better than stacks of 10 i guess ;))

Solution 2

As with everything, there is no FAST=TRUE setting. While the JDBC default fetch size of 10 is not ideal for your situation, it is OK for a "typical" OLTP application, and really isn't that bad for your case either, it seems. Apparently a large fetch size is not ideal for your situation either. But again, it isn't that bad to do 1000 at a time.

The other factor which you haven't mentioned is how WIDE the rows are that are being pulled. Consider that the chunk of data you are pulling from the database server across the network to the app server is the sum(WIDTH*ROWS). If your rows are 5000 bytes across, and you're pulling 1000 at a time, then each fetch is going to bring in 5 MB of data. In another case, perhaps your rows are "skinny" at only 100 bytes across. Then fetching 1000 of those is only shuttling 100K pieces around.

Because only YOU can know what the data will look like coming back, the recommendation is to set the fetch size system-wide for the "general" case, then adjust the oddball queries individually as needed.

In general, I too have found 100 to be a better setting for large data processes. That's not a recommendation, but relaying an observation.

Solution 3

BTW, at least with Oracle you need to be careful with the fetch size because Oracle driver sets aside an array for maximum possible size taken by each row, not the actual data size. So if you have a fat table, you memory footprint can suffer.

Take a look here - http://www.oracle.com/technetwork/database/enterprise-edition/memory.pdf

In Oracle you can find out maximum possible space taken by a column in user_tab_columns metadata table (data_length). It can be used to determine fetch size.

In rough tests, I found that 4 * 1024 * 1024 / sum(data_length for all columns of your table) is a reasonable fetch size.

Solution 4

The correct method is to use setFetchSize.

By default, when Oracle JDBC runs a query, it retrieves a result set of 10 rows at a time from the database cursor. This is the default Oracle row fetch size value. You can change the number of rows retrieved with each trip to the database cursor by changing the row fetch size value.

Standard JDBC also enables you to specify the number of rows fetched with each database round-trip for a query, and this number is referred to as the fetch size. In Oracle JDBC, the row-prefetch value is used as the default fetch size in a statement object. Setting the fetch size overrides the row-prefetch setting and affects subsequent queries run through that statement object.

Fetch size is also used in a result set. When the statement object run a query, the fetch size of the statement object is passed to the result set object produced by the query. However, you can also set the fetch size in the result set object to override the statement fetch size that was passed to it.

Solution 5

Pretty much what Adam H. said - there are no universal settings for every type of scenarios. It takes time for Oracle to fetch rows so time it spent waiting for rows on server side, before sending it to the client, could have been spent in your app should prefetch threshold be set lower, so performance suffers.

From what I recall Oracle also uses client side caching to provide scrollable cursors. It could help to set cursor to forward only, at least from memory prospective. At least it used to be helpful in older versions of JDBC driver, perhaps the behavior changed since.

Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY);

View more solutions

Comments

[daveslab]

For an individual query that makes sense, but setting the system property should just override the default, no?

[gpeche]

I agree about fetch size ~= 100 for large data processes.

[daveslab]

Finally! This is by far the clearest explanation. Your analogy makes a ton of sense. Thank you.

[Aprillion]

oh,, thanks for the award ;)) just a side note, i guess Oracle might precache the data that will be fetched next, so d) is not really an issue

[Sumedh]

So what will be this typical throttle size, aka practical size of pipe?

[Aprillion]

@Sumedh too many variables, you need to do your own tuning for your project, en.wikipedia.org/wiki/Performance_tuning

[techcraver]

Thanks for this link: oracle.com/technetwork/database/enterprise-edition/… perfectly explains how jdbc memory & fetch size stuff works.

[Adrian Shum]

Given that in most case, processing of results and fetching are synchronous, I am curious to know, between each fetching, is Oracle doing any work to prepare for subsequent fetches? If not, then it is simply like, 1st person only folds required number of papers that 3rd person ask for, and when 3rd person is cutting shape, 1st person just stand still without doing anything. Then apart from point 3 about TCP/IP throttling, setting a fetch size of 1000 is not going to be worse than 10. Is my understanding correct?

[Aprillion]

@AdrianShum I guess so, but that would not be my assumption that Oracle is not pre-fetching. There might be a different setting for how many rows are pre-fetched regardless of the last fetch size, in which case you would want to set the fetch size to the same number... I don't have any insight into the Oracle internals though.

[rogerdpack]

See also stackoverflow.com/q/28161049/32453 for a list of tips on how to not require so much RAM but still use prefetch

[rogerdpack]

Remember to profile with different numbers--for us we got improvements all the way up to 3200-4000 so try it out!

[rogerdpack]

You can set a system property, or configure it per connection: docs.oracle.com/cd/A97335_02/apps.102/a83724/…

[JRichardsz]

@techcraver your pdf link is wrong. Should be oracle.com/technetwork/database/enterprise-edition/memory.pd‌​f. Thanks!!