Any solution for Oracle TNS inefficiencies (many roundtrips, latency) from a Java app?

Question

I was looking at a very slow SQL query (originating from a Java app using Hibernate deployed in JBoss 5.1). This particular query returned about 10K records but still took 40s or more.

I ended up sniffing the traffic with the database (wireshark has a dissector for TNS) and found something unexpected. When data was coming from the server, each result row was in its own TNS packet. Furthermore, each TNS packet was acknowledged by the client (i.e. the app server) before the next one was sent from the database. For 10K records, there are 10K roundtrips to get a packet and acknowledge it. The impact on the performance is huge.

This is terribly inefficient. TCP allows larger packets and has a number of mechanisms (sliding windows, delayed ACKs) to reduce latency and increase throughput. However, in this case it's the TNS protocol on top that adds its own negotiation.

If I run the same query from the Oracle's SQL Developer, I don't see this pattern. The query completes in about 1/10 of the time, without thousands of round trips.

Short version: Oracle's wire protocol (TNS) seems to pass data in one TNS packet per query result row and requires each packet to be acknowledged by the client before the server sends the next one.

I've found some info about this [here][1] (scroll down until the section on 'The SDU and TDU parameters in the tnsnames.ora file').

And thus my question: is it possible to control the behaviour of the Oracle driver (I'm using 10.2.0.4.0) so the TNS protocol is more efficient? Again, this is a pretty standard J2EE app deployed in JBoss.

Thanks a lot!

Anklet

Hey, thanks for in depth Question with some hints to folks having similar situations.. Voted up.. – Grumous

Answer 1

Tune the SDU and TDU parameters in tnsnames.ora and listener.ora

To set the batch size to 100 for the current statment.

  ((OracleStatement)stmt).setRowPrefetch (100);

Note:

Setting the prefetch size can affect the performance of an application. Increasing the prefetch size will reduce the number of round-trips required to get all the data, but will increase memory usage. This will depend on the number and size of the columns in the query and the number of rows expected to be returned. It will also depend on the memory and CPU loading of the JDBC client machine. The optimum for a standalone client application will be different from a heavily loaded application server. The speed and latency of the network connection should also be consideredconnection should also be considered

(from Oracle Database JDBC Developer's Guide and Reference )

Available connection properties here.

Also have a look at Oracle UCP too.

Nutation

Thanks, after googling row prefetching I found the following "Standard JDBC receives the result set one row at a time, and each row requires a round trip to the database" at download.oracle.com/docs/cd/B19306_01/java.102/b14355/… which explains what I've seen. Hmm.. is it possible to set on the datasource level? The list of parameters at docs.jboss.org/hibernate/core/3.3/reference/en/html/… doesn't contain a setting for prefetch. – Anklet

In the meantime I found a driver property (defaultRowPrefetch) at download.oracle.com/docs/cd/B19306_01/java.102/b14355/… – Anklet

Are you using UCP? oracle.com/technetwork/database/features/jdbc/index-091264.html – Nutation

Answer 2

Try to increase the fetch size for your satement object.

I think the default is 10, so you might try to start with 100.

Statement stmt = connection.createStatement();
stmt.setFetchSize(100);
ResultSet rs = stmt.executeQuery("SELECT ...");

Bulley

Increasing the fetch size improved things by filling the TCP packets indeed. – Anklet