1 2 Previous Next 16 Replies Latest reply on Oct 20, 2017 8:25 AM by Mint-Innit

    SQL Developer 4.1.5 - Comparing (package) Body subprograms

    Christoph Pfrommer

      I'm running SQL Developer 4.1.5.21 (Windows 64-bit with JDK 8 included) on a Windows 7 (64 bit) virtual machine (3 GB memory, 2 processors) against a Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production.

       

      Whenever I'm opening a large package spec or package body, SQL Developer 4.1.5 is hanging with the message "Comparing <packagename> subprograms" or "Comparing <packagename> Body subprograms". This means that one processor keeps running at 100% until SQL Developer is shut down.

      comparing_subprograms.PNG

       

      (However, SQL Developer 4.0.3 has no issues at all with the same large package and calculates the navigation arrows and the breadcrumb navigation at the footer in an instance.)

       

      Is this a known bug in 4.1.5?

      Of course, this feature can be switched off in the preferences, but this would mean giving up a useful feature, the arrows "Navigate to package (body)" etc.

        • 1. Re: SQL Developer 4.1.5 - Comparing (package) Body subprograms
          B.Delmée

          I thought the breadcrumbs had been disabled already (as they were unreliable) ? Anyway you can always view the code outline instead.

          • 3. Re: SQL Developer 4.1.5 - Comparing (package) Body subprograms
            Gary Graham-Oracle

            If you see that Show Breadcrumbs is truly working in 4.1.5, could you post a screen image?

             

            Regardless, if you are experiencing a slow down / hang, I suppose you could always try following the advice in

            Annoying Proccess "Comparing <package> body subprograms" Version 4.1.0.19 Build Main-19.07

            to disable all of Supported Sync Spec and Body, Supported Gutter Navigation, as well as Show Breadcrumbs.

            • 4. Re: SQL Developer 4.1.5 - Comparing (package) Body subprograms
              Christoph Pfrommer

              In order to clarify: In 4.0.3 I'm seeing the navigation arrows and the breadcrumbs. - In 4.1.5, there are the navigation arrows (for smalles packages) or the process for creating them is hanging (for large packages).

              I'm interested in the navigation arrows. Sure, one can switch off the feature if it doesn't work. But this is more of a workaround than a solution to me.

               

              Thanks.

              • 5. Re: SQL Developer 4.1.5 - Comparing (package) Body subprograms
                thatJeffSmith-Oracle

                we never saw an issue with the spec/body toggles (the arrows) that we saw with the breadcrumbs feature, which is why we left it intact

                • 6. Re: SQL Developer 4.1.5 - Comparing (package) Body subprograms
                  Gary Graham-Oracle

                  It is surprising that Gutter Navigation hangs in 4.1.5 for the same large package that performs well (or at least works) for you in 4.0.3.

                   

                  It would be helpful if you could post a full thread dump for the JVM when the hang occurs. Here are some tips...

                  There are a couple of ways to get a full thread dump for a Java application.

                   

                  If the application was launched from a Cmd window (Windows) or terminal (Linux)

                  just

                   

                  1. Ctrl-Break (Windows)

                  2. Ctrl-\     (Linux)

                   

                  and the dump will display there. You may need to increase the window or terminal

                  buffer size if the dump is large to avoid the first part scrolling off-screen.

                   

                  It is usually more convenient, regardless of how the application gets launched,

                  to use the Java jstack utility to get a dump. To use jstack just

                   

                  1. Open a Cmd window (Windows) or terminal (Linux)

                  2. Verify the PATH environment variable references a JAVA_HOME

                     The bin directory of the JAVA_HOME will contain the jps and jstack utilities

                  3. Use the jps utility to list the process ids (PID) of all Java applications,

                     then look for the process id of "OracleIdeLauncher"

                  4. Use the jstack utility to list a full thread dump.  For example...

                   

                     C:\Tools>echo %JAVA_HOME%

                     C:\Program Files\Java\jdk1.8.0_121

                   

                     C:\Tools>jps

                     8384 OracleIdeLauncher

                     5144 Jps

                   

                     C:\Tools>jstack 8384

                     2017-02-13 11:52:27

                     Full thread dump Java HotSpot(TM) 64-Bit Server VM (25.121-b13 mixed mode):

                     ...

                  • 7. Re: SQL Developer 4.1.5 - Comparing (package) Body subprograms
                    Gary Graham-Oracle

                    Also, it would be good to know exactly how large are the packages involved in the hangs.  Please run the following and post the results...

                    select * from user_object_size

                    where type like 'PACKAGE%'

                      and name = '<your_package>'

                    I am trying to test a fairly extreme example (an unwrapped version of SYS.DBMS_AWR_REPORT_LAYOUT from 11.2 XE).  That query shows...

                    NAME                                              TYPE                     SOURCE_SIZE   PARSED_SIZE  CODE_SIZE   ERROR_SIZE

                    -----------------------------------------    ----------------------   -------------------   ------------------   ---------------   -----------------

                    DBMS_AWR_REPORT_LAYOUT    PACKAGE BODY             1894445                    885       2617944                       0

                    DBMS_AWR_REPORT_LAYOUT    PACKAGE                            49342              117741           60047                       0

                    The spec is over 1,100 lines and the body over 44,000 lines.  In a recent internal build of 4.2,with Gutter Navigation enabled, these eventually open in the code editor (after several minutes), but the navigator arrows do not appear in the gutterFor the size of package I normally test with (under 500 lines), the arrows appears as expected.

                     

                    Edit:  Actually the navigator arrows appear in the code editor gutter for both 4.1.5 and 4.2.0, and for all packages regardless of size.  I was thrown off initially since the spec of the huge package begins with a long declaration section for constants and types which, of course, do not get assigned navigator arrows.  The package's procedures all came at the end of the spec.

                     

                    The difference between 4.1.5 and 4.2.0 for this extreme case appears to be the speed of one or more algorithms. I could be wrong, there may be other factors in play here, but opening both the package spec and body seemed to take 5 (or more) times longer in 4.1.5.  I would strongly recommend upgrading to the 4.2 production release whenever it comes out, and hope that future releases further improve on performance in this area.

                    • 8. Re: SQL Developer 4.1.5 - Comparing (package) Body subprograms
                      dotAge

                      Met the same issue in the Version 4.2.0.17.089 (build 17.089.1709).

                      I suppose that has something to do with the huge package body tab idled for a long time. It was resolved after I closed the current tab and opened a new one for the package.

                      It is welcome to provide a break button behind the comparing progress bar, otherwise, the whole windows will hang up and many changes in other tabs will be lost when user kills the application task.

                      • 9. Re: SQL Developer 4.1.5 - Comparing (package) Body subprograms
                        Gary Graham-Oracle

                        I suppose that has something to do with the huge package body tab idled for a long time.

                        Unless someone provides full thread dumps (as suggested in one of my prior posts) it will be impossible to know.

                         

                        Also, despite the long-running, extreme test case (an unwrapped version of SYS.DBMS_AWR_REPORT_LAYOUT from 11.2 XE) I found, few will claim that having and maintaining such large packages is a good idea (definitely not a best practice!).  Uncommon edge cases naturally will not get the priority that other more common issues receive, especially if no service request gets logged with My Oracle Support.

                        • 10. Re: SQL Developer 4.1.5 - Comparing (package) Body subprograms
                          Mint-Innit

                          I also have this problem in the latest version of SQL Developer - 17.2.0.188  Build 188.1159  on Windows 10

                          It's the first version I've ever had this issue on. The 'solution' is to disable gutter navigation as mentioned in other threads on here, but I've got used to using that so it's really annoying to no longer have it.

                          The symptoms are repeated delays of around 10 seconds where the interface is not responsive at all, it makes editing packages impossible.

                          The package I'm working on is just over 6000 lines in the body, so not huge, I've got a high spec machine so it's not lack of [hardware] processing resources.

                           

                          I guess I have to just revert to an earlier version and wait for a new release to see if that fixes it.

                           

                          Cheers.

                          • 11. Re: SQL Developer 4.1.5 - Comparing (package) Body subprograms
                            Gary Graham-Oracle

                            The 'solution' is to disable gutter navigation

                            A better description may be "the only reliable 'workaround'".  But there may be another alternative in your case -- increasing the virtual memory limit (AddVMOption -Xmx800m) in AppData\Roaming\sqldeveloper\17.2.0\product.conf.

                             

                            I re-ran my test for the 44K line package body mentioned in some earlier posts against 17.2.  Right away I realized I could not tell whether the poor performance was due to...

                            1. Comparing <package> subprograms processing done by the Supported Gutter Navigation code editor preference.

                            2. Garbage collection in the Java VM. 

                             

                            Comparing <package> subprograms allocates tons of virtual memory in the JVM, so I increased the limit from (the presumed default of) 800m to 4096m.  I recently upgraded to a Windows 10 machine, 64-bit OS with16GB of RAM.  Here are the results:

                             

                            1. With default virtual memory... performance was so poor I just killed the application

                             

                            2. With Supported Gutter Navigation disabled and 4096m virtual memory limit...

                               a) Open package spec...........:  00:08 (8 seconds)

                               b) Open package body...........:  01:05

                             

                            3. With Supported Gutter Navigation enabled and 4096m virtual memory limit...

                               a) Open package spec...........:  00:08 (8 seconds)

                               b) Comparing ... subprograms:  04:10

                               c) Open package body...........:  01:24

                               d) Comparing ... body subp....:  03:46

                             

                            SInce your package is "only" 6000 lines both the extra memory and CPU requirements required by gutter navigation may be small enough (after eliminating unnecessary garbage collection and JVM thrashing) that you can keep it enabled.

                            • 12. Re: SQL Developer 4.1.5 - Comparing (package) Body subprograms
                              Mint-Innit

                              Hi, thanks for that. I've enabled the option AddVMOption -Xmx800m in the file as suggested. The (commented out) values all seem to be 128m. Whether that's the default if nothing is specified or not I have no idea.

                               

                              I will report back on whether it  makes a difference (it is behaving itself fine now but I find it's good to leave it running under normal usage for a while to check how it works longer term).

                               

                              Cheers

                              • 13. Re: SQL Developer 4.1.5 - Comparing (package) Body subprograms
                                Gary Graham-Oracle

                                Read over those settings in product.conf carefully.  There is...

                                1. -Xms to control initial size of the memory allocation pool.  That is what I would expect to be set to 128m in the sample commented line.

                                2. -Xmx to control the maximum size of the memory allocation pool.  That should be increased from the 800m value and uncommented.

                                 

                                Edit: To check current JVM memory usage / settings, use Help > About > Properties, and search on "mem"...

                                AboutPropertiesMem.jpg

                                • 14. Re: SQL Developer 4.1.5 - Comparing (package) Body subprograms
                                  Mint-Innit

                                  Ah, thanks for that yes I'd mis-read and set the wrong value

                                   

                                  Have now adjusted the file and will see what happens under normal usage next week.

                                   

                                  Many thanks.

                                  1 2 Previous Next