A bug has already been logged for this: Bug 16101234 - CONNECTION BUSY WHILE OPEN THE DATA TAB
The behavior does seem to occur more frequently than in earlier releases, but it most likely will be addressed before the 4.0 production release.
SQL Developer Team
Actually that bug is not published and, in fact, cannot be published under Oracle policy since it was logged against a non-production release. If it were published you could track it via an Oracle Support account at http://support.oracle.com
As for making the dialog non-modal and providing an auto-retry capability, it is probably best to wait and see how good the bug fix is first. If you wish to make feature requests for those, that can be done on the SQL Developer Exchange: http://sqldeveloper.oracle.com/
Well, if a connection is busy, it is logical that any new request must wait until the active request finishes. There are some things that may be affecting how long that active request runs.
On the client:
1. Client machine is slow. Perhaps other processes are competing for resources (any of CPU, memory, I/O).
2. The JVM memory limit is too low in your sqldeveloper.conf file and the JVM is struggling with garbage collection to stay below it.
3. SQL Array Fetch Size (Tools -> Preferences -> Database -> Advanced) is too large. Use a smaller value.
4. You are running lots of processes on the connection. Try opening unshared connections. For example, see a suggestion in this old post:
On the server:
5. Database server response is slow. Perhaps the server is heavily loaded, or DBMS statistics are stale, or network latency is an issue, etc.
6. The database is configured to perform parallel processing in some cases where it is inappropriate, and just wastes resources.
So the best way to avoid the issue is to ensure everything on both the client and server run as fast as possible. If a new request cannot obtain the lock for a connection before the timeout limit, our code has long given us functionality to
1. Warn the user.
2. Give the user the chance to retry or abort.
What changes were made in this area for 4.0EA1? The retry/abort prompt now only occurs if the new request is running in the foreground using the UI -- any requests processed in the background will keep on trying without bothering the user.
What changes were made in this area for 4.0EA2? The /*+ NO_PARALLEL */ hint was added to SQL queries for the Data tab.
This is the current state of affairs. I have no further information at this time on what more might be done to progress bug 16101234.
SQL Developer Team
Thanks Gary for that thorough reply.
The preface of my comment yesterday -- reporting a "connection busy" experience yesterday -- was that SQLD was doing no work.
* All worksheets had been closed;
* Only simple DML SELECTs had been created and run during the session;
* I'd opened a package but made no changes to it;
* All this work had been done on one connection.
SQLD was in this state for near three hours before I simply used Force Quit to close it. That's what I do.
It is not always easy to know when reading old posts may be helpful, but if you had read the "Hanging sessions" thread noted above, it would have led you to another regarding issues with hanging sessions after attempting to cancel a long running query:
Otherwise I suggest you attempt to collect some debugging info. Here are some tips for that:
Well, if you are experiencing a hang (say, everything grinds to a halt due to an OutOfMemory in the JVM) or even a deadlock, I can only repeat my suggestion to collect some debugging info: