Any Oracle TNS inefficiency solution (many hits, latency) from a Java application?

Question

I watched a very slow SQL query (from a Java application using Hibernate deployed in JBoss 5.1). This particular query returned about 10 thousand records, but still took 40 or more.

I ended up sniffing traffic using a database (wirehark has a dissector for TNS) and found something unexpected. When the data came from the server, each row of results was in its own TNS package. In addition, each TNS packet was confirmed by the client (i.e. the application server) before the next was sent from the database. For 10K entries, there are 10K roundtrips to receive the packet and confirm it. The performance impact 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, the TNS protocol from above adds its own negotiations.

If I run the same query from Oracle SQL Developer, I do not see this template. The request completes approximately 1/10 time, without thousands of rounds.

Short version : Oracle wired protocol (TNS) seems to send data in one TNS packet packet for each request and requires that each packet be acknowledged by the client before the server sends the next one.

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

And so my question is: is it possible to control the behavior of the Oracle driver (I use 10.2.0.4.0) so that the TNS protocol is more efficient? Again, this is a pretty standard J2EE application deployed to JBoss.

Thank you so much!

Answer 1

Configure SDU and TDU parameters in tnsnames.ora and listener.ora

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

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

Note:

Setting the prefetch size can affect application performance. Increasing the prefetch size will reduce the number of round trips required to obtain all the data, but increase memory usage. This will depend on the number and size of the columns in the query and the number of expected rows. It will also depend on the memory and processor load of the JDBC client machine. The application that is optimal for a stand-alone client will differ from a heavily loaded application server. The speed and latency of the network connection should also be considered. Communication should also be considered.

(from Oracle Database JDBC Guide and Reference )

Available connection properties here .

See also Oracle UCP .

Answer 2

Try increasing the sample size for your satement object.

I think the default is 10, so you can try starting at 100.

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