This discussion is archived
12 Replies Latest reply: Feb 6, 2013 1:16 AM by user8719725 RSS

Memory problems with Oracle sql developer

729528 Newbie
Currently Being Moderated
Hello,

I have problems with my Oracle SQL developer Version 3.0.04. It makes windows unstable after the query tries to display too many rows of data (300k for example). Is there a way to address this issue besides increasing the amount of physical memory? I currently have only 1 GB of RAM on this machine. Thank you.
  • 1. Re: Memory problems with Oracle sql developer
    460522 Explorer
    Currently Being Moderated
    Hi,

    SQL Dev uses Java architecture which is very memory consuming.
    Since you need to display all 300K records, use SQL Dev to export them on csv format.
    Then the latest Excel should do the job for you.

    Regards,

    Buntoro
  • 2. Re: Memory problems with Oracle sql developer
    729528 Newbie
    Currently Being Moderated
    You may be right but I think the better decision is to buy more RAM and I did just that.
  • 3. Re: Memory problems with Oracle sql developer
    563021 Newbie
    Currently Being Moderated
    Please report back whether it helps. My work desktop has 2 GB but also has some performance issues with SQL Developer, not only with large result sets but also those containing blob/clob columns with a lot of text. I might be mistaken, but I remember it working better in earlier versions. It just seems unresponsive in general. If I don't use it for a day or so (but leave it running), reopen the window, and right-click a database to connect, it takes 10 or 20 seconds for the context menu to appear.
  • 4. Re: Memory problems with Oracle sql developer
    Gary Graham Expert
    Currently Being Moderated
    Hi,

    Upgrading hardware can be a very satisfying experience, especially as it is almost always inexpensive relative to the value of your time. Virtual memory/paging on Windows/PC hardware is a huge disappointment, so more physical memory on Windows is a real panacea. With 64-bit O/S becoming more prevalent, laptops and PCs are commonly available with 8G memory.

    That said, I suspect few users have a business case for viewing hundreds of thousands of rows through a graphical user interface.

    Getting back to the point about java memory management, I want to put in a plug for the new Garbage-First Garbage Collector. It will be production in JDK 1.7, but may be accessed in JDK 1.6 u14 & up as an experimental option. The more recent the update revision, the better. Just add something like this to your sqldeveloper.conf file:

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

    It does a much better job of actually freeing unused memory from the O/S perspective.
    To read more about it: http://www.oracle.com/technetwork/java/javase/tech/g1-intro-jsp-135488.html

    Gary Graham
    SQL DeveloperTeam
  • 5. Re: Memory problems with Oracle sql developer
    729528 Newbie
    Currently Being Moderated
    I will report back when I install the 2 additional GB of RAM in Monday.
    It's interesting to note that when using the PL SQL Developer I don't experience such problems on 1GB RAM machine running the same queries.
  • 6. Re: Memory problems with Oracle sql developer
    729528 Newbie
    Currently Being Moderated
    I increased the amount of virtual memory from 1 to 3 GB. This solved the problem.
  • 7. Re: Memory problems with Oracle sql developer
    900632 Newbie
    Currently Being Moderated
    Thanks Gary (@gggraham),

    Added the config changes that you mentioned and now Oracle SQL Developer behaves itself!

    Just to recall, I added the following lines in to my "sqldeveloper/bin/sqldeveloper.conf":

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

    Thanks!
    Ryan

    Edited by: 897629 on 16-Nov-2011 12:05

    Edited by: 897629 on 16-Nov-2011 12:06
  • 8. Re: Memory problems with Oracle sql developer
    473111 Newbie
    Currently Being Moderated
    Hi ,

    I thought i should post here a link http://java2go.blogspot.com/2007/06/jdeveloper-tips-2-fine-tuning.html which details more JVM params.

    The below one

    AddVMOption -Dsun.awt.keepWorkingSetOnMinimize=true

    is quite useful.

    HTH,

    Dani
  • 9. Re: Memory problems with Oracle sql developer
    archimede Newbie
    Currently Being Moderated
    On Fedora15 with jdk1.6.0_30 and 3.1EA3, the following options are Unrecognized:

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

    Is that normal?

    Alessandro
  • 10. Re: Memory problems with Oracle sql developer
    user8719725 Newbie
    Currently Being Moderated
    Dears,

    I added the following parameters in the config file:

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


    Still having the export problem. Export hanged at around 54k records.

    The AddVMOption -Xmx2048M helped to reach the +50k records from around 20k max before adding it

    I am running Win 7 + MS Office 64-bit, 8gb RAM notebook

    On task manager, sql developer is taking +2.3GB Ram
    Versions installed:
    Java(TM) Platform     1.7.0
    Oracle IDE     3.2.20.09.87
    Versioning Support     3.2.20.09.87

    Thanks in advance

    Edited by: user8719725 on Feb 5, 2013 12:52 AM
  • 11. Re: Memory problems with Oracle sql developer
    Jeff Smith SQLDev PM ACE Moderator
    Currently Being Moderated
    The only way to address the memory issue you see when doing an XLS export is to increase the memory a la AddVMOption -Xmx2048M

    We're looking to update how we read and generate Excel files in a future version to be more memory efficient.

    You can always generate a CSV file and open that in Excel. This will run much quicker and with less memory.
  • 12. Re: Memory problems with Oracle sql developer
    user8719725 Newbie
    Currently Being Moderated
    Thank you. csv is way faster and the export does finish

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points