Monday, March 22, 2010

SQL Developer and Java memory use

First, this feature in java is not yet production. So if something goes wrong after setting this, back out the changes.

SQL Developer is a java application in case you didn't know :)  So, we do not manage the memory directly nor do we have the ability to release back to the operating system.  This is taken care of by java which does the garbage collection for us.  There's many things you can read about tuning the garbage collector (GC)  to get it to collect more efficiently.  There is a new GC being built and it's in 1.6 u14 +.  I've been testing it and unlike the older GC it releases memory back to the operating system.  I did a select and fetched a few 1000s rows which were brought into memory. Then I closed the worksheet tab and the Windows task manger showed the memory was released back and sql developer dropped in memory usage. This could help you and I'm told this will be production in a future update of the 1.6 jdk.  In the meantime, here's how to enable it.

edit the sqldeveloper.conf file and add these lines:

AddVMOption -XX:+UnlockExperimentalVMOptions
AddVMOption -XX:+UseG1GC
AddVMOption -XX:+G1ParallelRSetUpdatingEnabled
AddVMOption -XX:+G1ParallelRSetScanningEnabled

If you care to read more about the new GC, check this link out: http://java.sun.com/javase/technologies/hotspot/gc/g1_intro.jsp

Lastly, remember non-production.