5 Replies Latest reply: Mar 1, 2013 2:58 PM by ERPDude RSS

    SQL Developer High Memory Consumption


      I have been using SQL developer for quite some time and had initially had problems in the past with high memory consuption when I use this tool.
      I have received and applied advice from this forum and while it had helped a little, the majority of the high memory consuption remained an issue.

      I finally got more time to dig around and try to specifically isolate where the problem seems to be coming from and here is what I found.

      *1)* I have removed the Check for Updates feature
      *2)* I have turned off many of the extensions except for DBA Navigator, Real Time SQL Monitoring, SearchBar and Snippet.
      *3)* When I start a fresh SQL Developer Session and initiate a Oracle Connection the application consumes roughly 148 meg fo RAM
      *4)* When I open my Windows Task Manager and watch the memory allocated to SQL Developer I notice it goes up when I move my mouse over the SQL Developer tool and When I run through menus at roughly 5k a second or so and the memory is never released back to the system.
      *5)* When I run a Large SQL to the grid the memory jumps by about 100 meg or so and will continue to do so every time I repeat the SQL until SQL Developer consumes roughly 748 meg of RAM.
      *6)* 748 Meg of RAM seems to be the number when SQL Developer (with one Oracle connection) no longer continues to consume more and then not return the memory to the system.

      Is there a way to have SQL Developer automatically clear up it's active memory usage without closing it down and restarting it?
      Why does SQL Developer continue to consume more and more memory just from moving your mouse over it and/or by navigating menus?

      Here is my About Detail;


      Oracle SQL Developer
      Build MAIN-09.87
      Copyright © 2005, 2012 Oracle. All Rights Reserved.
      IDE Version:
      Product ID: oracle.sqldeveloper
      Product Version:


      Component     Version
      =========     =======
      Java(TM) Platform     1.6.0_35
      Oracle IDE


      Name     Value
      ====     =====
      awt.toolkit     sun.awt.windows.WToolkit
      class.load.environment     oracle.ide.boot.IdeClassLoadEnvironment
      class.load.log.level     CONFIG
      class.transfer     delegate
      file.encoding     Cp1252
      file.encoding.pkg     sun.io
      file.separator     \
      ice.browser.forcegc     false
      ice.pilots.html4.ignoreNonGenericFonts     true
      ice.pilots.html4.tileOptThreshold     0
      ide.AssertTracingDisabled     true
      ide.bootstrap.start     109707460930968
      ide.build     MAIN-09.87
      ide.conf     C:\app\twilliams\product\11.2.0\client_3\sqldeveloper\sqldeveloper\bin\sqldeveloper.conf
      ide.config_pathname     C:\app\twilliams\product\11.2.0\client_3\sqldeveloper\sqldeveloper\bin\sqldeveloper.conf
      ide.debugbuild     false
      ide.devbuild     false
      ide.extension.search.path     sqldeveloper/extensions:jdev/extensions:ide/extensions
      ide.firstrun     true
      ide.java.minversion     1.6.0_04
      ide.launcherProcessId     3276
      ide.main.class     oracle.ide.boot.IdeLauncher
      ide.patches.dir     ide/lib/patches
      ide.pref.dir     C:\Users\twilliams\AppData\Roaming\SQL Developer
      ide.pref.dir.base     C:\Users\twilliams\AppData\Roaming
      ide.product     oracle.sqldeveloper
      ide.shell.enableFileTypeAssociation     C:\app\twilliams\product\11.2.0\client_3\sqldeveloper\sqldeveloper\bin\sqldeveloperW.exe
      ide.splash.screen     splash.gif
      ide.startingArg0     C:\app\twilliams\product\11.2.0\client_3\sqldeveloper\sqldeveloper\bin\sqldeveloperW.exe
      ide.startingcwd     C:\app\twilliams\product\11.2.0\client_3\SQLDEVELOPER\SQLDEVELOPER\BIN
      ide.user.dir     C:\Users\twilliams\AppData\Roaming\SQL Developer
      ide.user.dir.var     IDE_USER_DIR
      ide.work.dir     C:\Users\twilliams\Documents\SQL Developer
      ide.work.dir.base     C:\Users\twilliams\Documents
      ilog.propagatesPropertyEditors     false
      java.awt.graphicsenv     sun.awt.Win32GraphicsEnvironment
      java.awt.printerjob     sun.awt.windows.WPrinterJob
      java.class.path     ..\..\ide\lib\ide-boot.jar
      java.class.version     50.0
      java.endorsed.dirs     C:\app\twilliams\product\11.2.0\client_3\sqldeveloper\jdk\jre\lib\endorsed
      java.ext.dirs     C:\app\twilliams\product\11.2.0\client_3\sqldeveloper\jdk\jre\lib\ext;C:\Windows\Sun\Java\lib\ext
      java.home     C:\app\twilliams\product\11.2.0\client_3\sqldeveloper\jdk\jre
      java.io.tmpdir     c:\Temp\
      java.library.path     C:\app\twilliams\product\11.2.0\client_3\sqldeveloper\sqldeveloper\bin;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;C:\app\twilliams\product\11.2.0\client_3\bin;C:\app\twilliams\product\11.2.0\client_3;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Lenovo\Access Connections\;C:\Program Files\WinMerge;C:\Program Files\ThinkPad\Bluetooth Software\;.
      java.naming.factory.initial     oracle.javatools.jndi.LocalInitialContextFactory
      java.protocol.handler.pkgs     oracle.jdevimpl.handler
      java.runtime.name     Java(TM) SE Runtime Environment
      java.runtime.version     1.6.0_35-b10
      java.specification.name     Java Platform API Specification
      java.specification.vendor     Sun Microsystems Inc.
      java.specification.version     1.6
      java.util.logging.config.file     logging.conf
      java.vendor     Sun Microsystems Inc.
      java.vendor.url     http://java.sun.com/
      java.vendor.url.bug     http://java.sun.com/cgi-bin/bugreport.cgi
      java.version     1.6.0_35
      java.vm.info     mixed mode
      java.vm.name     Java HotSpot(TM) Client VM
      java.vm.specification.name     Java Virtual Machine Specification
      java.vm.specification.vendor     Sun Microsystems Inc.
      java.vm.specification.version     1.0
      java.vm.vendor     Sun Microsystems Inc.
      java.vm.version     20.10-b01
      jdbc.driver.home     /C:/app/twilliams/product/11.2.0/client_3/
      jdbc.library     /C:/app/twilliams/product/11.2.0/client_3/jdbc/lib/ojdbc6.jar
      line.separator     \r\n
      oracle.home     C:\app\twilliams\product\11.2.0\client_3\sqldeveloper
      oracle.ide.util.AddinPolicyUtils.OVERRIDE_FLAG     true
      oracle.jdbc.mapDateToTimestamp     false
      oracle.translated.locales     de,es,fr,it,ja,ko,pt_BR,zh_CN,zh_TW
      oracle.xdkjava.compatibility.version     9.0.4
      orai18n.library     /C:/app/twilliams/product/11.2.0/client_3/jlib/orai18n.jar
      os.arch     x86
      os.name     Windows 7
      os.version     6.1
      path.separator     ;
      reserved_filenames     con,aux,prn,lpt1,lpt2,lpt3,lpt4,lpt5,lpt6,lpt7,lpt8,lpt9,com1,com2,com3,com4,com5,com6,com7,com8,com9,conin$,conout,conout$
      sqldev.debug     false
      sun.arch.data.model     32
      sun.boot.class.path     C:\app\twilliams\product\11.2.0\client_3\sqldeveloper\jdk\jre\lib\resources.jar;C:\app\twilliams\product\11.2.0\client_3\sqldeveloper\jdk\jre\lib\rt.jar;C:\app\twilliams\product\11.2.0\client_3\sqldeveloper\jdk\jre\lib\sunrsasign.jar;C:\app\twilliams\product\11.2.0\client_3\sqldeveloper\jdk\jre\lib\jsse.jar;C:\app\twilliams\product\11.2.0\client_3\sqldeveloper\jdk\jre\lib\jce.jar;C:\app\twilliams\product\11.2.0\client_3\sqldeveloper\jdk\jre\lib\charsets.jar;C:\app\twilliams\product\11.2.0\client_3\sqldeveloper\jdk\jre\lib\modules\jdk.boot.jar;C:\app\twilliams\product\11.2.0\client_3\sqldeveloper\jdk\jre\classes
      sun.boot.library.path     C:\app\twilliams\product\11.2.0\client_3\sqldeveloper\jdk\jre\bin
      sun.cpu.endian     little
      sun.cpu.isalist     pentium_pro+mmx pentium_pro pentium+mmx pentium i486 i386 i86
      sun.desktop     windows
      sun.io.unicode.encoding     UnicodeLittle
      sun.java2d.ddoffscreen     false
      sun.jnu.encoding     Cp1252
      sun.management.compiler     HotSpot Client Compiler
      sun.os.patch.level     Service Pack 1
      user.country     US
      user.dir     C:\app\twilliams\product\11.2.0\client_3\SQLDEVELOPER\SQLDEVELOPER\BIN
      user.home     C:\Users\twilliams
      user.language     en
      user.name     twilliams
      user.timezone     America/Los_Angeles


      Name     Identifier     Version     Status
      ====     ==========     =======     ======
      Check For Updates     oracle.ide.webupdate     Loaded
      Code Editor     oracle.ide.ceditor     Loaded
      Component Palette     oracle.ide.palette1     Loaded
      Data Miner     oracle.dmt.dataminer     Disabled by user
      Database Connection Support     oracle.jdeveloper.db.connection     Loaded
      Database Object Explorers     oracle.ide.db.explorer     Loaded
      Database UI     oracle.ide.db     Loaded
      Diagram Framework     oracle.diagram     Loaded
      Diagram Javadoc Extension     oracle.diagram.javadoc     Loaded
      Diagram Thumbnail     oracle.diagram.thumbnail     Loaded
      Diff/Merge     oracle.ide.diffmerge     Loaded
      Extended IDE Platform     oracle.javacore     Loaded
      External Tools     oracle.ide.externaltools     Loaded
      File Support     oracle.ide.files     Loaded
      Help System     oracle.ide.help     Loaded
      History Support     oracle.jdeveloper.history     Loaded
      Import/Export Support     oracle.ide.importexport     Loaded
      Index Migrator support     oracle.ideimpl.indexing-migrator     Loaded
      JDeveloper Runner     oracle.jdeveloper.runner     Loaded
      JViews Registration Addin     oracle.diagram.registration     Loaded
      Log Window     oracle.ide.log     Loaded
      Mac OS X Adapter     oracle.ideimpl.apple     Loaded
      Navigator     oracle.ide.navigator     Loaded
      Object Gallery     oracle.ide.gallery     Loaded
      Oracle IDE     oracle.ide     Loaded
      Oracle SQL Developer     oracle.sqldeveloper     Loaded
      Oracle SQL Developer - 3rd Party Database Browsers     oracle.sqldeveloper.thirdparty.browsers     Loaded
      Oracle SQL Developer - APEX Listener Administration     oracle.sqldeveloper.listener     Loaded
      Oracle SQL Developer - Change Mangement     oracle.sqldeveloper.em_cm     Loaded
      Oracle SQL Developer - DBA Navigator     oracle.sqldeveloper.dbanavigator     Loaded
      Oracle SQL Developer - Database Cart     oracle.sqldeveloper.dbcart     Loaded
      Oracle SQL Developer - Extras     oracle.sqldeveloper.extras     Loaded
      Oracle SQL Developer - File Navigator     oracle.sqldeveloper.filenavigator     Loaded
      Oracle SQL Developer - Migrations Antlr3 Translator     oracle.sqldeveloper.migration.translation.core_antlr3     Missing dependencies: oracle.sqldeveloper.migration
      Oracle SQL Developer - Migrations Application Migration     oracle.sqldeveloper.migration.application     Disabled by user
      Oracle SQL Developer - Migrations Core     oracle.sqldeveloper.migration     Disabled by user
      Oracle SQL Developer - Migrations DB2     oracle.sqldeveloper.migration.db2     Disabled by user
      Oracle SQL Developer - Migrations DB2 Translator     oracle.sqldeveloper.migration.translation.db2     Missing dependencies: oracle.sqldeveloper.migration, oracle.sqldeveloper.migration.translation.core_antlr3
      Oracle SQL Developer - Migrations Microsoft Access     oracle.sqldeveloper.migration.msaccess     Disabled by user
      Oracle SQL Developer - Migrations Microsoft SQL Server     oracle.sqldeveloper.migration.sqlserver     Disabled by user
      Oracle SQL Developer - Migrations MySQL     oracle.sqldeveloper.migration.mysql     Disabled by user
      Oracle SQL Developer - Migrations Sybase Adaptive Server     oracle.sqldeveloper.migration.sybase     Disabled by user
      Oracle SQL Developer - Migrations T-SQL Translator     oracle.sqldeveloper.migration.translation.core     Missing dependencies: oracle.sqldeveloper.migration
      Oracle SQL Developer - Migrations Teradata     oracle.sqldeveloper.migration.teradata     Disabled by user
      Oracle SQL Developer - Migrations Teradata SQL Translator     oracle.sqldeveloper.migration.translation.teradata_translator     Missing dependencies: oracle.sqldeveloper.migration, oracle.sqldeveloper.migration.translation.core
      Oracle SQL Developer - Migrations Translation UI     oracle.sqldeveloper.migration.translation.gui     Disabled by user
      Oracle SQL Developer - Object Viewer     oracle.sqldeveloper.oviewer     Loaded
      Oracle SQL Developer - Real Time SQL Monitoring     oracle.sqldeveloper.sqlmonitor     Loaded
      Oracle SQL Developer - Reports     oracle.sqldeveloper.report     Loaded
      Oracle SQL Developer - Scheduler     oracle.sqldeveloper.scheduler     Disabled by user
      Oracle SQL Developer - Schema Browser     oracle.sqldeveloper.schemabrowser     Loaded
      Oracle SQL Developer - SearchBar     oracle.sqldeveloper.searchbar     Loaded
      Oracle SQL Developer - Security     oracle.sqldeveloper.security     Disabled by user
      Oracle SQL Developer - Snippet     oracle.sqldeveloper.snippet     Loaded
      Oracle SQL Developer - Spatial     oracle.sqldeveloper.spatial     Disabled by user
      Oracle SQL Developer - TimesTen     oracle.sqldeveloper.timesten     Disabled by user
      Oracle SQL Developer - Tuning     oracle.sqldeveloper.tuning     Loaded
      Oracle SQL Developer - Unit Test     oracle.sqldeveloper.unit_test     Disabled by user
      Oracle SQL Developer - User Extensions Support     oracle.sqldeveloper.userextensions     Loaded
      Oracle SQL Developer - Worksheet v2     oracle.sqldeveloper.worksheet     Loaded
      Oracle SQL Developer - XML Schema     oracle.sqldeveloper.xmlschema     Loaded
      Oracle SQL Developer Data Modeler     oracle.datamodeler     Disabled by user
      Oracle SQL Developer Data Modeler - Reports     oracle.sqldeveloper.datamodeler_reports     Disabled by user
      PROBE Debugger     oracle.jdeveloper.db.debug.probe     Loaded
      Peek     oracle.ide.peek     Loaded
      Persistent Storage     oracle.ide.persistence     Loaded
      Property Inspector     oracle.ide.inspector     Loaded
      QuickDiff     oracle.ide.quickdiff     Loaded
      Replace With     oracle.ide.replace     Loaded
      Runner     oracle.ide.runner     Loaded
      VHV     oracle.ide.vhv     Loaded
      Versioning Support     oracle.jdeveloper.vcs     Disabled by user
      Versioning Support for Subversion     oracle.jdeveloper.subversion     Missing dependencies: oracle.jdeveloper.vcs
      Virtual File System     oracle.ide.vfs     Loaded
      Web Browser and Proxy     oracle.ide.webbrowser     Loaded
      XML Editing Framework IDE Extension     oracle.ide.xmlef     Loaded
      audit     oracle.ide.audit     Loaded
      classpath: protocol handler extension     oracle.jdeveloper.classpath     Loaded
      jdukshare     oracle.bm.jdukshare     Loaded
      mof-xmi     oracle.mof.xmi     Loaded
      oracle.ide.dependency     oracle.ide.dependency     Loaded
      oracle.ide.indexing     oracle.ide.indexing     Loaded
      palette2     oracle.ide.palette2     Loaded
      status     oracle.ide.status     Loaded

      Thanks in advance...

      Edited by: ERPDude on Feb 28, 2013 2:46 PM
        • 1. Re: SQL Developer High Memory Consumption
          Gary Graham-Oracle
          Hi Tom,

          The memory consumption you observe is characteristic of the Java VM and the garbage collection algorithm it uses. There are some tricks you can use to improve memory de-allocation in the JVM. See the following:
          Re: Reduce SQLDeveloper memory footprint with JDK 1.7
          Re: Memory Leak or Bad Java Garbage Collector

          Also, it is best to track the JVM's heap size and used memory via a memory profiler. Task Manager is said to overstate these.

          SQL Developer Team
          • 2. Re: SQL Developer High Memory Consumption
            Aces!!! You nailed it Gary...

            Thank you.

            I applied fixed noted in Re: Reduce SQLDeveloper memory footprint with JDK 1.7

            For others, to summarize my changes.

            AddVMOption -XX:+UnlockExperimentalVMOptions
            AddVMOption -XX:+UseG1GC
            AddVMOption -XX:MaxGCPauseMillis=50
            AddVMOption -XX:GCPauseIntervalMillis=200
            AddVMOption -XX:MaxPermSize=128M
            AddVMOption -Xms50M
            AddVMOption -Xmx384M
            AddVMOption -XX:MinHeapFreeRatio=10
            AddVMOption -XX:MaxHeapFreeRatio=10

            comment the following two lines as shown below
            #AddVMOption -Xmx640M
            #AddVMOption -Xms128M

            Now SQL Developer runs at roughly 500 meg.

            I guess the only thing I have left to ask others reviewing this would be if there is a way to get these memory values down without having much adverse impacts on SQL Developer based on newer JVM switches/functionalities.

            The posts that drove these changes are old from a technologoical perspective :) 2010.

            • 3. Re: SQL Developer High Memory Consumption
              Gary Graham-Oracle

              There is not much more I can contribute here -- perhaps others can. I will only say...

              In terms of current Java HotSpot VM tuning options, see:

              Also, if you are looking for sustained performance / quicker resumption after minimizing SQL Developer, don't forget this:
              AddVMOption -Dsun.awt.keepWorkingSetOnMinimize=true

              In terms of the G1GC algorithm, I would guess that the latest Java 7 update will be more finely tuned than the latest Java 6, but the recommendation is to stay with Java 6 until an upcoming SQL Developer release formally certifies on Java 7.

              • 4. Re: SQL Developer High Memory Consumption
                Thanks again Gary.

                I'll give switch a shot as well.

                Now, I only wish that the developers of OSD would provide a Preferences mechanism to apply these tweaks through the interface with a reboot option like some of the existing Preferences updates.

                This might be a good extension option.

                • 5. Re: SQL Developer High Memory Consumption
                  I wanted to add that I have been able to successfully get my OSD JVM Max Mem switch set down as far as 100M.
                  AddVMOption -Xmx100M

                  This works as long as I work with nicely tuned SQL and don't run huge selects returning unneeded data.
                  I have also found that dropping memory down this far has an impact on large XLSX exports.

                  However, if I need large exports, I try to use CSV to get around this, bump up my memory switch (would be nice to see extension for this) or use another tool.
                  I have found that the IDE disappears (can see it still in task manager) with a low memory setting if I do large dumps so I have to be carefull with that.

                  I'm even considering having two installations of OSD.
                  One Light (low mem) installation and One beast (high mem) of an installation where I can do large output dumps when occasionally needed.