If I have a table with three columns,
NAME (VARCHAR2 20), AGE (NUMBER), LOCATION (VARCHAR2 50)
how much memory does Oracle JDBC driver allocate before fetching the result?
If the fetch size is set at 100, does the driver allocate memory for all 100 records even if the query returns only 10?
Thanks
Oracle Database JDBC driver, versions prior to 12:
The driver allocates the maximum size for each column times the number of rows in the fetchSize prior to executing the query.
For example for a VARCHAR(4000) column it will allocate 8k bytes times the fetchSize.
versions 12 (and later):
It allocates approximately 15 bytes per column per row in the fetchSize prior to executing the query. After execution, the driver in version 12 allocates only as much as needed to store the actual row data.
As a result, version 12 drivers typically use substantially less memory than the earlier versions' drivers.
Your example:
In your example a VARCHAR(20) can be as big as 40 bytes, a NUMBER can be as big as 22 bytes and a VARCHAR(100) as big as 100 bytes. With the fetchSize set to 100 the older drivers would allocate (40 + 22 + 100) * 100 = 16k. The version 12 driver would allocate 3 * 15 * 100 = 4.5k. There is additional overhead in both drivers that I am ignoring.
The Oracle driver allocates fechtsize * max. size of one row.
In your case this would be 40 bytes (=20 characters in UTF-16) + 22 bytes + 100 bytes (= 100 characters in UTF-16) * 100. Which gives a total of roughly 16kb (probably a bit more)
This is actually the only choice the driver has: if you request the driver to send 100 rows through the network, it must allocated enough memory to be able to hold those 100 rows in memory. Which means it has to assume the worst case and reserve the maximum length for each column.
Once the data is received from the server, it will only take up as much memory as required once it has been converted into the proper Java objects.
You can picture the fetch size to allocate a buffer that is used during the retrieval operations. Similar to e.g. a BufferedInputStream
You can dive into: http://www.oracle.com/technetwork/database/application-development/t-12c-1964666.pdf
As you can see it's depends of version of JDBC Driver, Driver configuration (enabled of Prepare Statement Cache or not, parameter you passed by -D option) and many many others factors.
It depends even on others SQL statements that you have in your application.
In best case Driver will not allocate any memory but just reuse buffers from previous invocation of the same or different statement (depend of many factors of course).
In worst scenario will allocate as much memory amount as you need for fetch data by the other SQL Statement invocation that you have in Prepare Statement Cache already (for Oracle JDBC < 11.2)
