Memory management in Oracle JDBC 12c
Posted: Mon Jan 27, 2025 9:33 am
What is the fetch size and why is it relevant?
Let me start with a true story. But first a few brief words about what this is all about. By fetch size we mean the number of records that the client fetches from the database "in one go". The default fetch size in Oracle JDBC is 10, and it is set by a method of the (Prepared)Statement object: Statement.setFetchSize(n). This number is important because every fetch requires a network roundtrip between the client and the DB server - and every network roundtrip takes time.
A True Story
For an Oracle DBA, it's a pretty clear thing: increasing bulgaria telegram screening the fetch size can be an excellent tuning approach. Who hasn't seen one of those demos (eg with load testing tools like Swingbench, which we'll discuss below) where a huge throughput gain was achieved by increasing the fetch size?
This was also the case in the case of an (unnamed) DBA of an (unnamed) company who thought he had found a previously unnoticed approach to improving the performance of the application he operated in-house. He carried out systematic performance tests - with real statements from the application and realistic data sets - which showed that the optimal fetch size for this application was around 80. He reported this number to the developers and the code was expanded accordingly. The day of the test release and the release to the pre-prod environment arrived. The performance was good and everyone was happy.
Let me start with a true story. But first a few brief words about what this is all about. By fetch size we mean the number of records that the client fetches from the database "in one go". The default fetch size in Oracle JDBC is 10, and it is set by a method of the (Prepared)Statement object: Statement.setFetchSize(n). This number is important because every fetch requires a network roundtrip between the client and the DB server - and every network roundtrip takes time.
A True Story
For an Oracle DBA, it's a pretty clear thing: increasing bulgaria telegram screening the fetch size can be an excellent tuning approach. Who hasn't seen one of those demos (eg with load testing tools like Swingbench, which we'll discuss below) where a huge throughput gain was achieved by increasing the fetch size?
This was also the case in the case of an (unnamed) DBA of an (unnamed) company who thought he had found a previously unnoticed approach to improving the performance of the application he operated in-house. He carried out systematic performance tests - with real statements from the application and realistic data sets - which showed that the optimal fetch size for this application was around 80. He reported this number to the developers and the code was expanded accordingly. The day of the test release and the release to the pre-prod environment arrived. The performance was good and everyone was happy.