Any Oracle TNS inefficient (many round trips, latency) solution from Java applications?

I'm working on a very slow SQL query (from a Java application using hibernate deployed in JBoss 5.1) This particular query returned about 10K records, but it still takes 40 seconds or more

I finally sniffed the traffic with the database (Wireshark has a TNS dissector) and found something unexpected When the data comes from the server, each result row is in its own TNS packet In addition, the client (i.e. app server) confirms each TNS packet before sending the next TNS packet from the database For 10K records, there are 10K round trips to get the packet and confirm it The impact on performance is enormous

This is very inefficient TCP allows larger packets and has many mechanisms (sliding window, delayed ACK) to reduce latency and improve throughput However, in this case, it is the top TNS protocol, which adds its own negotiation

If I run the same query from Oracle SQL developer, I can't see this pattern The query is completed in about 1 / 10 of the time without thousands of round trips

Short version: Oracle's wired protocol (TNS) seems to pass data in a TNS packet in each query result line, and requires the client to confirm each packet before the server sends the next packet

I've found some information about this [here] [1] (scroll down to the 'SDU and TDU parameters' section in the' tnsnames. Ora file)

So my question is: can I control the behavior of Oracle drivers (I use 10.2.0.4.0), so the TNS protocol is more effective? Again, this is a very standard J2EE application deployed in JBoss

Thank you.

Solution

Adjust tnsnames Ora and listener SDU and TDU parameters in ora

To set the batch size of the current regulation to 100

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

be careful:

(from Oracle database JDBC developer's guide and reference)

Available connection properties here

Also look at Oracle UCP