This issue was resolved, Just thought to share so that it will be helpful for someone else. Apparently all the trouble was due to a new juniper firewall installed which would fragment and drop the traffic that would too big to handle. We have configured SDU to lesser size - 2048 at client side and all the queries works just fine with a minor performance hit. I am not sure what was the default SDU size, however looks like oracle connection negotiates and selects the lower SDU size among the server and client. ~Gidhin On 13 May 2010 00:17, Bill Ferguson <wbfergus@xxxxxxxxx> wrote: > I had a somewhat similar experience a few months ago. Without getting > actual confirmation from Oracle, it somewhat seemed like the security > on SQL Net traffic was tightened up some with 11g, whereby if > firewalls aren't specifically allowed to pass SQL Net traffic, they > seem to do some packet sniffing and this winds up in the traffic not > getting completely sent to the client (even though the server did send > it all), so everything gives the appearance of hanging simply because > the server is waiting on a confirmation from the client and the client > is still waiting for the data. > > However, my problem was the server is running 11.1.0.7 (on Windows), > while the clients that were experiencing problems were running the > 10.2 drivers. Even with the newer 11g drivers the problem still > persisted, but clients using 9.x drivers did not have the problem. > After several weeks of going around and around with the network folks > and sending them the Oracle notes from MOS stating exactly what to > look for and exactly what to do to fix it, I finally had to take a > computer over to their office and show them the exact error on my > machine, then let them watch the firewall traffic. After about 30 > minutes of them fiddling around, one guy finally clicked on a screen > in the firewall configuration menu that showed the exact setting > Oracle said need to be changed, they allowed SQL Net traffic, and > suddenly all of the problems disappeared. > > One of the "symptoms" of the incorrect firewall setting was that > something as simple as "select * from table_name" had the appearance > of hanging, while a similar connection doing a "describe table_name" > worked. > > I forget which MOS documents I found that explained the exact cuase > and solution to the problem, but I found them by researching the > TNS-12535 error that I found in my listener's log file. > > Hope this helps some. > > -- > -- Bill Ferguson >