In my personal experience, far more database performance problems are actually application side problems, particularly with Java and Hibernate.
A key goal is to minimize network traffic between Oracle and the application by setting appropriate fetch size..
What are your needs and expectations?
When working with a query, some basic questions that should be asked:
- How many rows do I expect to be returned to the application? 1 row, 10 rows, 1000 rows, 1,000,000 rows?
- For DML, (INSERT/UPDATE/DELETE/MERGE), how many rows do I expect to be affected?
- How often do I expect my query to run?
Fetch size and Round Trips
When an application is performing a SELECT and the number of rows to be returned is large, the application “fetches” rows in batches. Each batch is a “round trip” to Oracle to “fetch” the next batch of rows.
Depending on how the application code is written, the code may either a) fetch all of the rows or b) process each batch before requesting the next batch.
The number of rows retrieved by each fetch is called the “fetch size”. The Oracle JDBC driver default is 10 rows.
The amount of time required for each round trip is included in the total elapsed time for the query. As such, a query may appear to be slow when in fact Oracle is simply waiting for the next fetch. Or the total number of fetches may be excessively large, e.g. 20M rows = 2M round trips across the network if the fetch size = 10.
In Hibernate, you can specify the desired fetch size in the DB model class via:
org.hibernate.fetchSize (Long – number of records)
Hibernate provides the value of this hint to the JDBC driver to define the number of rows the driver shall receive in one batch. This can improve the communication between the JDBC driver and the database, if it’s supported by the driver
However, be aware that you should not automatically specify a large fetchSize as this results in pre-allocation of memory for the total possible number of rows in each fetch. This is less of a problem in 12c.