30 SQL Developer Tips in 30 Days, Day 7: The Array Fetch Size Preference

I have found a lot of users like to tweak this preference without really understanding what it does:

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. [DOCS]

So if you execute a query and the query returns 1,000 records and your fetch size is set to 100 – we will need to go to the database 10 times to get all of the records.

The larger the fetch size, the more resources are required to handle said data as it comes across the JDBC layer. We have found you get very diminishing returns after a setting of about 200 rows – hence the limit in the preferences.

Also, setting it very low, say to the default of 10 – which also isn’t allowed in SQLDev, doesn’t mean that you’d only see 10 rows after running a query. What it means is that the default set of results – however many rows it takes to populate the viewable grid on the screen – would be fetched in batches of 10 rows.

So when would I need to tune this preference?

Probably never.

But, if you are working with really wide or very fat rows – say a a bunch of LOBs, then you will want to tune this setting DOWN to decrease the memory load on the JDBC layer.