Home > Programming / tutorials > JDBC performance tuning with fetch size

JDBC performance tuning with fetch size

Problem: For a Oracle (backend) – Java (frontend) system, a query  returning about 500 K rows was running very slow and is taking about 45 mins.  Oracle database is hosted on server (A), has a separate application server (B), and client can access database from client machine (C), using SQLPLUS.  Query is running slow from the clients machine (C) and from the java front end (B).

Basic Checks on dbase:

Make sure to check following with respects to the tables / database in question:

  • Latest Database statistics exists for the tables in question.
  • Indexes
  • Degree of Parallelism at Table Level:
  • Optimizer mode

Database configuration parameters  – sga_max_size,  pga_aggregate_target , memory_target, processes

Solutions tried

  • Multiple tests were performed, taken explain plan and auto trace, which showed that query is taking time in SQL Net and it has higher waits on this layer.
  • When we run query on the server hosting oracle (A) it is much faster as compare to running from the clients machine(C).
  • Use of optimizer hints and parallelism is not helping any speed-up in the query.
  • Changes in the database configuration parameters and tuning SQL Net does not help.

Resolution

  • From the clients machine, using SQLPLUS – Changed the “ARRAYSIZE” parameter to 5000 (from default 15) – This reduces the query time to about 1 min from 45 min.
  • For Java based front end – changed the “setdefaultrowfetch” to 5000 and query time is reduced to ~35s.

Understanding the issue

JDBC drivers’ default fetch size is 10. In normal JDBC programming if you want to retrieve 100 rows, it requires 10 network round trips between your application and database server to transfer all data. Definitely this will impact your application response time. The reason is JDBC drivers are designed to fetch small number of rows from database to avoid any out of memory issues.

Important : Do not make fetch size too big. The fetch size should be based on your JVM heap memory setting. Too large a fetch size will lead to out of memory issues on application server, esp if you are on hosted environment.

Here is sample code to explain fetch size implication

  1. Glenn
    July 30th, 2010 at 13:52 | #1

    great post.thanks

  2. Ara
    July 27th, 2010 at 15:29 | #2

    Nice tip. Of course, one might wonder why you need to fetch 500K rows in your client application, but that’s a different question. And, of course, I have no idea what your requirements were.

  1. July 27th, 2010 at 15:12 | #1