5 Replies Latest reply on Jul 17, 2019 6:08 PM by Glen Conway

    Sql Developer CPU Hang

    Doug Phelps

      I have a sql developer query that seems to hang the gui.  We have tested it in Sql Developer 19.1 and 18.3.

      I am attaching a jstack dump at time it is hanging.

       

      The query has multiple with subqueries, large case statement with many strings, and select from ( select with multiple union all) ...

        • 1. Re: Sql Developer CPU Hang
          Glen Conway

          Is it really a hang in the sense that no CPU is being used?  Or CPU is being consumed and memory usage is high?  Your jstack full thread dump does not show memory statistics at the tail end of the listing like doing a Ctrl+Break in the Windows CMD console that launches SQL Developer would.

           

          The only BLOCKED threads in the dump involve either the parser or a "Service Thread" which seems to have something to do with memory management.  If your query produces a lot of rows and they are all going to a Query Results grid, then maybe you are bumping into the Xmx memory limit and the garbage collector working overtime.

          • 2. Re: Sql Developer CPU Hang
            Doug Phelps

            It is taking 95+ % CPU usage and only doing scrolling in the sql worksheet window.  The query actually runs in 11 seconds and only returns 3K rows.

            Sql Developer becomes non-responsive and the only way out is to kill the task.

            • 3. Re: Sql Developer CPU Hang
              Glen Conway

              Even with only 3K rows, could it be that the rows are "wide"?  Lots of columns, wide columns, CLOBs?  If you wish to examine what's happening with memory during the hang, read over my post about monitoring memory with Java Visual VM: Re: SQL Developer 19.1.0 Memory\CPU full

               

              Otherwise, what about error messages?  Principally under View > Log in the Logging Page tab.  If there is nothing interesting there, you should also try opening a CMD window, CD to the sqldeveloper\sqldeveloper\bin directory and run .\sqldeveloper.exe from there rather than an OS short-cut.  Errors messages will go to the console.  To be sure to get as many messages as possible, change the

              IncludeConfFile  sqldeveloper-nondebug.conf

              line in the sqldeveloper.conf file to

              IncludeConfFile  sqldeveloper-debug.conf

              before starting SQL Developer from the CMD window.

               

              If the issue is not memory, then I hope something interesting turns up in the messages.

               

              Cheers

              • 4. Re: Sql Developer CPU Hang
                Doug Phelps

                It only has about 15 columns returned, no CLOB .

                 

                I have changed the Xms to 1024M and Xmx to 8192M and the issue goes away....

                • 5. Re: Sql Developer CPU Hang
                  Glen Conway

                  Perfect.  So it must have been memory related.  It really is surprising how much memory putting stuff in a results grid can take.  Best to close any connections not currently in use and close pinned grids once you are done with them -- if left open, the JVM might eventually move up toward that new 8GB limit and start to affect other stuff running on your OS.