4 Replies Latest reply on Sep 12, 2012 1:57 PM by Aleksander Stopar

    Reduce SQLDeveloper memory footprint with JDK 1.7

    Aleksander Stopar

      Some time ago in another thread (Re: Memory problems with Oracle sql developer there was a suggestion to try the new Garbage-First Garbage Collector. which should be production in JDK 1.7.

      I use SQLDeveloper with JDK 1.7 on 64bit Linux with good results:
      - everything feels faster, snappier
      - fonts rendering is different, but it is OK
      - the bugs noted in other threads are not a showstopper for me (the connections pane not showing up on startup, not being able to scroll more than 1 OCI array size of records in results grid)

      In the above mentioned thread there is a suggestion that the new garbage collector should improve memory footprint of SQLDeveloper, however, this is not my experience, since it behaves pretty much the same as with JDK 1.6 (resident size between 700 and 900 MB).

      Do I need to use these opotions (as per reffering thread) to enable the new garbage collector (see below) or is it switched on by default in JDK 1.7? The reduced memory footprint would be very welcomed, because I use Oracle Warehouse Builder at the same time (also a java app) and there is always much pressure on memory.

      AddVMOption -XX:+UnlockExperimentalVMOptions
      AddVMOption -XX:+UseG1GC
      AddVMOption -XX:+G1YoungGenSize=25m
      AddVMOption -XX:+G1ParallelRSetUpdatingEnabled
      AddVMOption -XX:+G1ParallelRSetScanningEnabled

        • 1. Re: Reduce SQLDeveloper memory footprint with JDK 1.7
          Gary Graham-Oracle
          Hi Aleksander,

          Good question. Reading the Java 7 documentation, I never see it mentioned that G1GC (Garbage-First) is the new default GC, only that it is the long-term replacement for the concurrent collector (UseConcMarkSweepGC). Reading the Java 6 documentation for tuning the HotSpot VM, it either recommends letting the VM automatically select a collector or explicitly naming a collector via a -XX parameter.

          So I would guess that Garbage-First is not a default, and that you need to include AddVMOption lines for those -XX JVM options in the conf file whether you use JDK 1.7 or JDK 1.6 (updates 14&up). Presumably you can skip the -XX:+UnlockExperimentalVMOptions.

          By the way, the memory footprint itself can still grow very large, just as before. After all, the main goal of Garbage-First is to improve concurrency of garbage collection rather than reduce the footprint.

          What is different, then, and why the recommendation to try this new collector for SQL Developer? When closing a connection after doing something that increases the footprint, Windows TaskManager actually will show the Mem Usage decrease! That is something I had never seen before until using Garbage-First. So if you leave SQL Developer open for an extended period, but close panels or connections from time to time, the total Mem Usage will be less likely to push up against the JVM memory limit.

          Gary Graham
          SQL Developer Team
          • 2. Re: Reduce SQLDeveloper memory footprint with JDK 1.7
            Aleksander Stopar

            I did put these lines in my sqldeveloper.conf:
            AddVMOption -XX:+UnlockExperimentalVMOptions
            AddVMOption -XX:+UseG1GC
            AddVMOption -XX:MaxGCPauseMillis=50
            AddVMOption -XX:GCPauseIntervalMillis=200

            and I guess I'm using the G1GC because I also did see the decrease of memory footprint, too (at first I thought I'm looking at the wrong process ;) ). However, it does not prevent the memory consumption to grow.

            Basically, I would like to trade (some) responsiveness of the SQLDeveloper (more garbage collections) for less memory usage. With introduction of the G1GC the slowdowns should be less noticable, even when running SQLDeveloper with considerably lowered memory parameters. Right now I have these memory parameters:
            AddVMOption -XX:MaxPermSize=128M
            AddVMOption -Xms50M
            AddVMOption -Xmx384M
            AddVMOption -XX:MinHeapFreeRatio=10
            AddVMOption -XX:MaxHeapFreeRatio=10

            As you can see, the max heap is quite lowered, but SQLDeveloper is not complaining. As I understand, the total memory usage should be Xms + MaxPermSize, which in this case is 512MB, but SQLDeveloper is using right now more than 650MB. How can this be? The responsiveness is OK, I noticed a bit higher CPU usage, but nothing excessive, no pauses or slowdowns. It seems that G1GC is doing a fine job.

            Do you have any suggestions for parameters regarding memory footprint - not to let it grow past i.e. 500MB? As I said, I'm willing to sacrifice (a little bit of) responsiveness for reduced memory usage.

            • 3. Re: Reduce SQLDeveloper memory footprint with JDK 1.7
              Gary Graham-Oracle
              Hi Aleksander,

              Glad to hear of your good report on Java 7's HotSpot VM regarding performance -- it has various enhancements, of which the new garbage collector is just one. In terms of interpreting memory footprints, take a look at:


              Note the diagram indicates total heap size does not include the permanent generation memory. Xmx limits the heap size (the young and tenured generation). MaxPermSize limits class and method metadata plus static variable content. (Apparently starting back in Java 5 there are even some cases where the permanent generation space can be shared by multiple VM instances to improve start-up time and reduce memory usage.) These two limits control distinct, non-overlapping areas of memory.

              When monitoring a Java application's heap consumption with a profiling tool, I doubt the reported usage will exceed the Xmx limit by much. Monitoring with Windows Task Manager, however, can be a bit misleading. I have read several critiques in years past on how Task Manager reports program memory consumption. "Mem Usage" is actually the working set size. "VM Size" is program private memory rather than the true virtual size. And who knows how it tracks the Java VM's permanent generation size. Will it depend on whether it is shared or not?

              So I cannot really recommend any additional parameters to you. Just trust in the Xmx setting and hope that SQL Developer keeps any memory leaks to a minimum.

              Hope this helps,
              • 4. Re: Reduce SQLDeveloper memory footprint with JDK 1.7
                Aleksander Stopar

                I see this thread is referred to many times, so I will add a few things I discovered from the last time this thread was active.

                As for keeping the SQLDeveloper memory footprint below certain treshold, the parameters and values mentioned above are fine. However, they are being overrulled by parameters found in the ide.conf file:


                Just comment out the following two lines in the ide.conf file (put # in front of them, to make it look like this):

                #AddVMOption -Xmx640M
                #AddVMOption -Xms128M

                so the values you put in sqldeveloper.conf can be applied.

                This way the SQLDeveloper is running with cca 600MB footprint after a week (no restarts) of heavy usage - 8 hours every day, thousands of executed queryies, worksheets with thousands of lines (I eagerly await "bookmarks in the worksheet" feature), 20+ open worksheets and connections. After a day or so you'll notice SQLDeveloper slows down when executing a query and returning resultset and a spike in CPU activity (for half a second to 1 second). That's the GC firing, freeing memory for your resultset. Now, if you do queryies which return hundreds of columns you may get the "java.lang.OutOfMemoryError: Java heap space". Just narrow down the number of columns and it continues to run fine. Or you may just increase your memory parameters and restart SQLDev.

                I'm using 3.1.07 with JDK 1.6.0_31, because JDK7 has some unresolved issues on Linux.

                Hope this helps