We are using Oracle 8i as DB Server, WebLogic 6.1 as App Server and IIS 5.0 as Web Server on the different physical m/c. We are using OCI8 Driver.
We have a utility that will act as a cache manager. We have to provide a query, it will hold the result in ArrayList.
We are measuring time taken by the DB Server to execute query and how much by App Server to fetch rows using while(rs.next()).
Now in non-peak hours Query Execution Time : 1000 Row Fetching Time : 100
But during the peak hours Query Execution Time : 2000 Row Fetching Time : 2000 (sometime 3000).
I understood that during the peak hours DB Server is very busy hence it is taking more time to execute query. And during the peak hours, network traffic is very high + DB Server is already busy hence Row Fetching Time is going very high.
During the peak hours our App Server is 40% ideal always.
I believe that when we do while(rs.next()), each time App Server make a network trip to DB Server for fetching a single row. So if my query is returning 100 rows, there will be 100 network trip from App Server to DB Server and hence performance is degraded.
Now ResultSet has setFetchSize() method that determine now many records will be fetched on a call by OCI8 driver.
So now I set fetch size as 10... (1) Will I gain a performance? (2) Will I have any performance loss by any other means across the application?
Chetan, According to the Oracle "JDBC User's Guide and Reference", the default fetch size is ten (10). In tests that I performed, I found that a fetch size of one hundred (100) gave optimal performance. But of-course your mileage may vary (YMMV).
Good Luck, Avi.
Joined: Sep 16, 2004
Thx Avi for you reply.
In fact I also done some testing here and I found that 100 can be the ideal.
Presently I have set fetch size 100 at single place, will get the performance report after 2 days, will update you about the same.