Query on largetables from remote Client hangs

Symptoms:

Particular queries are hanging when they are executed from remote Clients and also when using database links.
But the smaller queries are successful and run without issues.

Example:

select * from table; –> hangs
select count(*) from table; –> works

Cause:
Network setting prevents larger TCP packets from being transported and this causes client to wait on the Server for the data from the query, and the Server to wait on the Client (which thinks part of the packet is still on the way). The session in the target database shows the wait “SQL*Net message from client”:
Solution:
Lower the Session Data Unit(SDU) from default 8192 to 1400 
1. Add the following to the sqlnet.ora file on both source and target:
DEFAULT_SDU_SIZE = 1400
2. Restart the Listener(s) in source and target. Retest the issue.
3. If the issue is fixed by above steps then there is network setting causing alteration of SQL packets during transport.
  • January 5, 2019 | 21 views
  • Comments