very slow closing of windows

[gad]

Hello,
For quite some time I have this strange problem of very slow closing of windows.
Closing a window might take 30 seconds. During this time plsqldev is frozen until the window finally closes.
If I have 10 windows on my windows list and I try to close plsqldev, it asks for every window if I want to save and then (no matter what I answer) it closes each window but it takes 30 agonizing seconds for each one.
Using the 'X' or using the right click menu in the window list gives the same result, so it's not how I close It's the closing itself that is the problem.
This behavior does not start immediately - if I open a window and close it inside of say 5, 10 minutes then it closes immediately. But let it seat open for more time and it closes slowly.
It happens for all kinds of windows (sql, test, program...)
A related phenomenon is that when re-running the sql or recompiling, re-testing (F8 for all) in a 'stale' window, I get a long 'Initializing' message it the window status bar, until it actually runs. I suspect it's the same thing.
It started happening in version 9 and still present for 10.
XP sp2 machine (for a long time now...).
I thought it was some connection problem, that would explain the pause ('initializing') for re-run but I don't understand how it can be the case for closing windows.

Please advice.

gad

 

[Marco Kalter]

To obtain some more diagnostic information, can you modify the shortcut and add the DebugSQL parameter? For example:

"C:\Program Files\PLSQL Developer\plsqldev.exe" DebugSQL

Reproduce the problem and send me the debug.txt file that is generated in the PL/SQL Developer directory or in the %APPDATA%\PLSQL Developer directory (e.g. C:\Users\\AppData\Roaming\PLSQL Developer).

 

[gad]

Hello Marco,
Please see below the relevant part of the debug file.
I checked it many times and the problem seems to be session end after some time.
When closing the window 'select...from dual' is executed and the response is 'ORA-03113: end-of-file on communication channel' after long time.
What would you recommend I check in order to find the cause for this.
I already checked that there is no definition in the server that closes idling sessions.

29/05/2013 19:56:44 UpdateChildList [timer]
29/05/2013 19:56:44 DoUpdateChildList
29/05/2013 19:56:44 Lock
29/05/2013 19:56:44 Lock
29/05/2013 19:56:44 UnLock
29/05/2013 19:56:44 UnLock
29/05/2013 19:56:48 TOracleQuery $0B5DCA70 Start Query.Execute
SQL = select 'x' from dual
29/05/2013 19:57:06 TOracleQuery $0B5DCA70 End
Result = ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 142 Serial number: 272
Duration = 18.97
29/05/2013 19:57:06 TOracleSession $03123C80 Start Session.LogOff

gad

 

[T-Gergely]

You may want to check alert.log on the server too see if there's a server side error (eg. ORA-600).
And make sure there's no firewall that blocks the TCP traffic of "idle" connections.

 

[gad]

Hello,
My problem still exists.
I did not see any errors in alert.log.
My firewall admin assured me that there is nothing that closes idle tcp connections. The only thing he could find is a time limit of 1 hour for all idle "services", but the staled connection problem may occur after 15 minutes, no where close to the limit.
I'm not sure he really understands the problem and knows where to look, but that's it for now.
I set PLSD to monitor the connection - "Check connection" on connection preferences.
Also the debug log is always on so I can see what's happening at all times.
Here is a snippet:
===================================================================
08/07/2013 12:28:48 TOracleQuery $07AA0690 Start Query.Execute
SQL = select 'x' from dual
08/07/2013 12:28:48 TOracleQuery $07AA0690 End (1 record processed)
Duration = 0
08/07/2013 12:29:48 TOracleQuery $07AA0690 Start Query.Execute
SQL = select 'x' from dual
08/07/2013 12:29:48 TOracleQuery $07AA0690 End (1 record processed)
Duration = 0
08/07/2013 12:30:48 TOracleQuery $07AA0690 Start Query.Execute
SQL = select 'x' from dual
08/07/2013 12:30:48 TOracleQuery $07AA0690 End (1 record processed)
Duration = 0
08/07/2013 12:31:27 Lock
08/07/2013 12:31:27 UnLock
08/07/2013 12:31:27 NavigationBookmarks.EnableButtons(Rebuild)
08/07/2013 12:31:27 NavigationBookmarks: Backward=0, Forward=0
08/07/2013 12:31:30 TOracleQuery $07AA0690 Start Query.Execute
SQL = select 'x' from dual
08/07/2013 12:31:49 TOracleQuery $07AA0690 End
Result = ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 138 Serial number: 902
Duration = 19.032
08/07/2013 12:31:49 TOracleSession $03088D80 Start Session.LogOff
08/07/2013 12:31:49 TOracleSession $03088D80 End
Duration = 0
08/07/2013 12:31:49 Destroying TSQLForm
08/07/2013 12:31:49 Lock
08/07/2013 12:31:49 UnLock
08/07/2013 12:31:49 Lock
08/07/2013 12:31:49 UnLock
08/07/2013 12:31:49 TSQLThread SQLThread Suspend ended
08/07/2013 12:31:49 Destroyed TSQLForm
08/07/2013 12:31:49 UpdateChildList starts
08/07/2013 12:31:49 UpdateChildList [timer]
08/07/2013 12:31:49 UpdateChildList ends
08/07/2013 12:31:49 TOracleQuery $07AA0110 Start Query.Execute
SQL = select 'x' from dual
08/07/2013 12:31:49 TOracleQuery $07AA0110 End (1 record processed)
Duration = 0
08/07/2013 12:31:49 DoUpdateChildList starts
08/07/2013 12:31:49 DoUpdateChildList Lock
08/07/2013 12:31:49 Lock
08/07/2013 12:31:49 UnLock
08/07/2013 12:31:49 DoUpdateChildList UnLock
08/07/2013 12:31:49 DoUpdateChildList ends
===================================================================

As can be seen at 12:30:48 a monitoring query succeeds.
The next one at 12:31:30 fails.
It shows what happened when I tried to close a SQL window.
This was the only open window and it was idle as of yesterday, for about 12 hours.

Marco what say you?
Is the monitoring done for every open window in a multi-session environment?
If so how it possible that connections become stale?

If someone can help with firewall knowledge I really would appreciate it.
I'm behind a fortigate 80c connected to another fortigate 80c located in a remote location, and oracle server is behind this fireall.
The connection is on a VPN channel.

thank you all,
gad

 

[jure]

gad,

try Session --> Log off, you will have to wait 30s just once. This worked for me when I had a similar problem.

Regards,
Jure

 

[T-Gergely]

I would start tracing this issue from the client with Wireshark. It should be done by an IP expert, preferably by a local network admin, though. I don't think we can help you remotely.

 

[gad]

Thank you to all for trying to help.
Jure: what do you mean by Session--> Log of? I didn't found something like this in preferences.
T-Gergely: Someone suggested this to me and I tried using wireshark just a few days ago. I realized that I don't know enough to proceed.
I managed to filter only what I thought I needed to check:
(ip.src==192.168.1.57 and ip.dst==192.168.3.17) or (ip.src==192.168.3.17 and ip.dst==192.168.1.57) where ...57 is my computer and ...17 is the oracle server.
A few pointers might help...

gad

 

[T-Gergely]

While using your filters, I'd expect a few retransmissions from your machine and TCP RST when the connection is lost. Then I'd choose "Follow TCP stream" on the RST packet to see when the connection was established (SYN packets) and when the last packet came from the server to check the idle socket blocked theory. But ICMP, routing protocols, etc can come into play depending on what you see.

"Session" is a menu and "Log off" is a menu item BTW.

 

[jure]

gad, I mean disconnect PL/SQL Developer from database (click menu Session and then Log off... All).
This is not actually a solution to your problem, it's just a workaround for not having to wait 30s for each window to close - instead you will just have to wait 30s once.

 

[gad]

Thank you T-Gergely and jure
The firewall at my office died suddenly and was replaced by a new one of the same model but with firmware/os updated to a newer version.
The behavior of stale/disconnected connections is now changed although not completely OK. I still gather info.
jure your suggestion of using session/log off/all is working great and saves quite a lot of time! like you said it's not really a solution but it helps.

gad