@Raja Sekhar Chintalapati - Sounds like the OS level TCP IP params are not tuned correctly.
What kind of OS are you using? For e.g. on Centos and RHEL, you can use these command to check the values -
There are other params that can be tuned as well but these 3 are kind of critical. For e.g. if the tcp_keepalive_time is set to a high value then the OS holds onto that port even after the transaction is done (SQL is processed and results are obtained).. This is not Ambari specific, as you can see.
If this was Ambari issue, there should be a much wider impact.
I recommend checking the TCP WAITTIME and other params on both, the source and destination servers.
Here are the recommended values -
net.ipv4.tcp_keepalive_time = 600
net.ipv4.tcp_keepalive_probes = 5
net.ipv4.tcp_keepalive_intvl = 15