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
