Some of my SQL worksheets contain queries that I may need to run on 2 databases in a row; when this happens, I may switch connections using the “Choose Db Connection" pop-up menu in the upper-right corner of the worksheet, going back and forth between connections as needed.
Today I noticed that the worksheet didn't respond immediately: in fact, the pop-up menu remained highlighted, indicating that the switch was in progress, and the switch was slow enough that I had enough time to move the mouse pointer away from the popup menu, back into the text area of the worksheet, click into that area, and… wait…, because the GUI was still busy, and therefore, it would miss my clicks into the text area. It was only after a couple of seconds that the worksheet would be responding again. Needless to say, I hate that!
Visualvm confirmed that the AWT-EventQueue-0 thread (the “main” thread processing user events in the GUI) was abnormally busy while I was switching connections; ideally that thread should spent most of its time waiting, rather than working. Otherwise the GUI becomes slow and unresponsive, as it was.
CPU sampling could track it all the way down, apparently to the following:
Which means that, as part of the work which normally happens when switching connections—there has to be some work behind the scenes: I have switched from one connection to another one, so I could be in a totally different universe now (database-wise), reflecting on the validity of the worksheet—a significant time was being spent in the constructor of an instance of the SyntaxAdvice class, which apparently involved heavy work of maintaining one or more Java collections.
…
Trying to reproduce it, if finally found the culprit: in the end of that SQL worksheet, after of few dozens of lines of SQL (4 queries in all, plus a couple of SQL*Plus commands), was a comment which read somewhat like this:
/*===========================================================*/
-- Use the following script for blah, blah, blah...
--@@some_script
*/
Can you spot the syntax error?
Well SQL Dev's internal parser did: it was that lone end-of-comment marker (*/) on the last line, of course, without a matching begin-of-comment marker. After editing a worksheet again and again, it's easy to commit this kind of mistake.
Fortunately, the Code Outline (right-click into the worksheet, then “Code Outline” in the context menu) can help to locate such errors, thanks to the “Syntax error; partial parse tree” red warning, which can be clicked upon to go to the location after which the internal parser cannot make sense of subsequent tokens.
So I just removed that last line, and voilà ! the syntax error was gone, and switching connections worked smoothly again! :-)
I'm sharing it, just in case…
Regards,