Hello.
Please if you can give any advice for a specific situation: a client (PL SQL Developer, Toad, or any other development tool) connects over a VPN to a server thru a satellite channel (quarantine and remote work). No big amounts of data transfered: this is an application development process, so I just execute some scripts, view object DDLS and so on.
Channel bandwidth varies from 2 Mbps to 20 Mbps.
But, since this is a satellite channel, ping is very high: from 0.5 to 1.5 seconds.
Also, Cisco Anyconnect VPN is used.
All other services work normally, even streaming video (with a little timeshift, but when buffered, its OK).
But working with database is extremely slow. Every movement means a delay from 5 seconds (sending a script to server for execution) up to 3-5 minutes (retrieve a table DDL).
I suppose this is something between TCP MTU, ORACLE SDU or something like that. I have read "Optimizing Performance" document and similar, but everywhere the main problem described - is how to tune performance for concurrent transfer of big amounts of data. How to manage slow connections on client side - is not a popular topic. I tried to make SDU = 512 bytes, it didn't help.
Can you please give an advice, in which direction should I move, what documentation to read, what should I try to tune.
C:\app\me\product\12.1.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (SDU=512) (ENABLE=BROKEN) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rmsdb)(PORT = 1521))) (CONNECT_DAT
A = (SERVER = DEDICATED) (SERVICE_NAME = IBPRD)))
OK (1500 msec)
C:\Users\me>
Great thanks,
Sergey Lotnikov
Ultimately this is all going to be about network trips. The SDU (and underlying OS parameters) are going to help you in some regard to this. 512 sounds way too low for me.
Some good information on how to set these is here:
https://www.oracle.com/technetwork/database/enterprise-edition/oow17netcon6718-3944720.pdf https://docs.oracle.com/database/121/NETAG/performance.htm#NETAG1476 But it is important to realise - nothing at database or OS level will fix a chatty *application*. If I have a 2MB SDU, but my app asks for 1 row at a time, then its going to be slow no matter what. So you need to the appropriate setting on the *tool*, eg
SQL Plus/SQL cl - set arraysize
SQL Dev - database preferences, rows fetched as high as possible
Dot.Net - the fetch size parameter on connection
Pro*C - prefetch parameter
Hope this helps