Observations on fetching data over a network with 100ms latency using Oracle ODBC driver

Dear Reader,

I was troubleshooting a SQLNet performance issue. The client server is located @ 60 to 100ms latency from the Oracle DB server. I was looking for ways to improve the performance.

Lab:

1. 100ms latency added to the Oracle Server Public interface (egress)

2. Client Server is windows with a dblink created in MSSQL server pointing to oracle using Oracle ODBC drive.

3. We are using tns entry in tnsnames.ora file in client to connect to the server.

Conducted 3 tests:

1. Fetch buffer size left ASIS in default value - 64000

2. Fetch buffer size increased to 6400000

3. Fetch buffer size @ 6400000 and SDU size adjusted to 64K (follow notes section on how to increase SDU size).

Results summary:

Inference: 

From the results, it looks neither fetch buffer size nor the SDU size makes any difference to the ODBC performance.

Notes: Steps to increase SDU size

Let us set SDU size to 65536

1. Server side:

    a. Listener - done (added SDU to the DESCRIPTION part)

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (SDU = 65536)

      (ADDRESS = (PROTOCOL = TCP)(HOST = vcentos79-oracle-sa1)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

    b. sqlnet.ora

DEFAULT_SDU_SIZE=65536

2. Client Side:

    a. sqlnet.ora

DEFAULT_SDU_SIZE=65536

    b. tnsnames.ora

OPDB1 =

  (DESCRIPTION =

    (SDU=65536)

Verify the SDU setting by making a test connection from sqlplus on the client:

lsnrctl show trc_level

lsnrctl set trc_level admin

Connect from client and see a message like below to confirm the new setting are reflecting fine.

=== SDU size visible properly for client and server

2023-07-09 18:43:15.617 : nsconneg:vsn=318, lov=300, opt=0xc41, sdu=65535, tdu=65535, ntc=0xc60e

2023-07-09 18:43:15.617 : nsconneg:Large sdu=65536,tdu=2097152,compression flg=0

2023-07-09 18:43:15.617 : nsconneg:after negotiation compression flag=0

2023-07-09 18:43:15.617 : nsconneg:vsn=318, gbl=0xc01, sdu=65536, tdu=2097152

===

lsnrctl show trc_level

lsnrctl set trc_level off (or whatever level it was set before).

lsnrctl show trc_level

In deed a lot of work has gone to test the arraysize using sqlplus. But this is specific to odbc driver.

Thanks