Skip to Main Content
  • Questions
  • Very slow network interaction between Oracle DB and java application

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Daniil.

Asked: January 10, 2018 - 10:23 am UTC

Last updated: January 10, 2018 - 4:11 pm UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 10K+ times! This question is

You Asked

I have a problem with my Oracle DB network speed.

First of all, what's the essence of the problem. There are java application on my computer and Oracle DB on a remote server. Connection speed between them is about 2,5MB/s. I execute in my java app a very simple query like "select id, name from table_name", result set contains ~60K rows (size is about 1,5 Mb) and transfers to my app for ~80 seconds. Accordingly to the profiler the most of the time application spends in oracle.net.Packet.recieve method.

For comparison the same query executes in SQL Developer for 0,5-0,7 seconds for 5000 rows. Extrapolating to 60K rows we have about 6-8 seconds.

The result of excution of tcpdump for my application shows that data transfers in chunks with size about 200 bytes. On the other hand for SQL Developer tcpdump shows package size more than 2000 bytes.

Official Oracle documentations suggests to increase SDU and TDU parameters, unfortunately i can't change configuration of database, so i tried to determine them on client side in a such way:

jdbc:oracle:thin:@(DESCRIPTION=(SDU=11280)(TDU=11280)(ADDRESS=(PROTOCOL=tcp)(HOST=<host>)(PORT=1521)(SEND_BUF_SIZE=11784)(RECV_BUF_SIZE=11784))(CONNECT_DATA=(SERVICE_NAME=<db>)))

But this didn't bring any changes. Can database or ojdbc driver ignore this parameters? Or maybe i'm on the wrong way?

and Chris said...

TDU is deprecated and ignored since Oracle Net 8.0. SDU defaults to 8192. So it's unlikely this is why you only get 200 byte chunks. But ask your DBAs if they have set the SDU & TDU parameters and, if so, what to!

In the meantime check how many rows you're fetching on each roundtrip. The getFetchSize methods tell you this and setFetchSize changes it:

https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setFetchSize(int )

This controls how many rows the client fetches on each request. The equivalent in SQL*Plus is arraysize. As the following example shows, when this is set to a low value (10), it takes a lot longer to get the results than when it's at 1,000 (1 minute vs 21+ minutes!):

SQL> create table t as
  2    select * from dba_objects;

Table created.

SQL>
SQL> select count(*) from t;

  COUNT(*)
----------
     79175

SQL>
SQL> set feed only
SQL> set timing on
SQL> set arrays 1000
SQL> select * from t;

79175 rows selected.

Elapsed: 00:01:00.03
SQL>
SQL> set arrays 10
SQL> select * from t;

79175 rows selected.

Elapsed: 00:21:40.63


If this doesn't help, check the net services troubleshooting guide:

https://docs.oracle.com/cd/E11882_01/network.112/e41945/trouble.htm#NETAG016

Also: do you really need to fetch all 60k rows in one hit? Surely some form of pagination is in order here?

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

SDU should be set both on client and server.

lh, January 10, 2018 - 2:24 pm UTC

Hi

Actual SDU values is least(client, server), so to increase it, one must update both of them.

It is only sqlnet.ora change...


Chris Saxon
January 10, 2018 - 4:10 pm UTC

True, though the OP said they didn't have access to change this on the DB server...

Daniil Solodukhin, January 10, 2018 - 2:45 pm UTC

Hello, Chris!
The reason was in fetch size. Increasing its value allows to decrease execution time at ~100 times. Thank you for your help!

P.S. Yes, I really need 60K rows in one query, unfortunately.
Chris Saxon
January 10, 2018 - 4:11 pm UTC

Great, glad this helped.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database