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.
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.
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.
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:
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
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.
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":
Edited by: 897629 on 16-Nov-2011 12:05
Edited by: 897629 on 16-Nov-2011 12:06
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
is quite useful.
On Fedora15 with jdk1.6.0_30 and 3.1EA3, the following options are Unrecognized:
Is that normal?
I added the following parameters in the config file:
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
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
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.