12 Replies Latest reply on Oct 17, 2017 4:52 PM by Gary Graham-Oracle

    Poor performance after DB-Diff

    SebastianKoell

      When I am finished with a complete DB Diff between two schemas (~2400 Objects) SQL Dev gets really really slow.

      The result is computed and there shouldnt be anything wrong with performance at this stage.

       

      E.g. scrolling in the result diff list takes ~8secs for the UI to actually do something. Same with everything else.

       

      Windows Taskmanager looks normal regarding sqldeveloper (No high CPU or RAM).

        • 1. Re: Poor performance after DB-Diff
          thatJeffSmith-Oracle

          You know have 2400 objects worth of meta data and ddl diffs loaded into memory. It's going to bog things down a bit. You could give the JVM more memory and see if that helps.

          • 2. Re: Poor performance after DB-Diff
            SebastianKoell

            The resulted diff was only ~200 objects . Do you say that even then it will have all the meta data / dll of the 2200 identical objects loaded?

            I know there is this checkbox to show same objects too but should that really consume so much memory?

            • 3. Re: Poor performance after DB-Diff

              I know there is this checkbox to show same objects too but should that really consume so much memory?

              Did you actually TRY what Jeff suggested?

              You could give the JVM more memory and see if that helps.

              • 4. Re: Poor performance after DB-Diff
                SebastianKoell

                rp0428 schrieb:

                Did you actually TRY what Jeff suggested?

                Yes I did. But that doesnt solve the problem. To give JVM more memory fights the symptom and not the cause of the problem.

                You would also question a mechanic who wants to install more fueltanks in your car when it only goes 10 miles per fill.

                 

                I did some analysis on the memory with Java visual VM to monitor the heap size. There is definitely a problem there:

                - After completing a diff and closing the diff tab the memory is not freed. If you do multiple diffs (and close each one before doing the next) your memory will fill up easily.

                (Attached image: left of the purple line = first Diff; right = second diff with closed first diff )

                Unbenannt.png

                - The memory actually consumed doesnt make sense for me. For comparing 800 objects it takes up 1GB of heap size (even when finished comparing). Dont know if you store a mp3 file with every compare but thats way too much.

                Lets say that the average Sourcefile has 5000 characters (very mild estimate), so 10.000 in order to compare two files. Then each object has probably an overhead - lets say 4096bytes ( -> 84.100 Bytes for one object comparison).

                Now multiply by 800 gets you 67280000 which is 67 MB. I dont think the metadata for a diff is even close to 1MB per file which it would have to be in order to make sense of the 1GB memory allocation.

                And I didnt even account for identical files! Only 10% of the objects are acutally different. So its 1GB metadata for 80 files.

                • 5. Re: Poor performance after DB-Diff
                  thatJeffSmith-Oracle

                  If we're not releasing the resources after the compare has been closed, that would be a bug.

                  • 6. Re: Poor performance after DB-Diff
                    SebastianKoell

                    memory.PNG

                    Final test: Everything closed after diff but still 1.3GB more heap than "normal".

                    Maybe look into optimizing heap size as well when working on this bug. Because the diff from the screenshot is only about 100 actual different objects (out of 900). Shouldnt be so much memory imho.

                    • 7. Re: Poor performance after DB-Diff
                      thatJeffSmith-Oracle

                      if you want file a bug, please submit a Service Request to My Oracle Support

                      • 8. Re: Poor performance after DB-Diff
                        Peter Stewart

                        I tried running a diff on two schemas with 57033 objects each and it just never returns, also bumped up the heap size to more than 1gb. After a certain point more heap size has no benefit, I found. Have you logged a bug for it? One cannot log an SR without a CSI and it is possible there are many SQL developer users without one in which case bugs they discover will go unfixed, so reporting them here seems to be the sensible thing to do.

                         

                        Doing comparisons between schemas is quite a common thing to do for Apps DBA types and the other option is to use the competing product with the name of a synonym for a frog as it is very good at it but not free and have been trying to wean myself off it for years. I do appreciate the free nature of SQL Developer and all the hard work that has gone into improving it and it is just about open all day on my desktop in spite of a couple of niggles.

                        • 9. Re: Poor performance after DB-Diff
                          SebastianKoell

                          Yes, here is the SR I made. But I agree, adding bugs shouldnt be limited to SR:

                           

                          SR 3-15504234361

                          • 10. Re: Poor performance after DB-Diff
                            thatJeffSmith-Oracle

                            Sorry, but to log bugs, that has to go through My Oracle Support. We try to catch and handle things here too, but the community forums aren't a replacement for Support.

                            • 12. Re: Poor performance after DB-Diff
                              Gary Graham-Oracle

                              Thanks for filing the bug.  Memory leaks are always a concern in any application.  Apart from the investigation of the leak , or leaks, and the verification testing being performed on potential fixes, I would like to make a couple of comments.

                               

                              1. SQL Developer relies on code from Enterprise Manager for the object-by-object diff processing.  The leak may be in EM.

                              2. Regarding your comment "To give JVM more memory fights the symptom and not the cause".  I agree, but as a practical matter...

                               

                              Here are some images from Java Visual VM's Monitor tab during DbDiff tests. My tests used a JVM memory limit of 2048 MB.

                              DbDiffVisualVMHeapMon17_3_1-B.jpg

                              Note how the dark blue spikes in used heap (which occur as objects get diffed by the EM  code) tend to extend over a range of more than 1/2 GB at times, and result in a overall used heap values higher than the default JVM memory limit of 800 MB (as set in the product.conf file).  This test used 17.3 and was approximately at mid-point of a 8,000+ object DbDiff. 

                               

                              Here is another test using 17.4, a complete run of the same test.

                              DbDiffVisualVMHeapMonitor.jpg

                              Note that object/sec here was about 1.2 objects/sec while in the 17.3 test it was 1.74 objects/sec up to the point I stopped tracking.  In neither case was there degradation in performance as the memory limit was high enough to avoid extra garbage collection overhead due to memory constraints.