ThatJeffSmith

Configuring Memory Usage in Oracle SQL Developer

Sometimes you need more memory.

Here’s an example: you want to model a database that has 1500 tables in it. What would that even look like?

If you zoom out far enough…

3800 views in one model - not very pretty

3800 views in one model – not very pretty

Now, ‘large’ is a subjective term. But lets put it this way – if you’re dealing with more than several hundred tables or entities – that’s larger than most other designs.

Oracle SQL Developer Data Modeler, and the modeling extension that runs inside of Oracle SQL Developer, consume a lot of resources to render the diagrams. Also, each model artifact is stored in its own XML file. That’s all loaded into memory when you open a design.

So, if you’re gonna work with a ‘large’ design, you’re going to need more memory than you get ‘out of the box.’

SQL Developer is a Java Swing application. It requires the JDK. There are several Java Virutal Machine (JVM) directives that tell it how to behave.

You can see how SQL Developer has configured its JVM settings and monitor its memory usage using a cool program called ‘Java VisualVM.’ It’s in your JDK/bin directory as jvisualvm.exe. If you open it and attach to the sql developer process ID, you get to see a lot of cool stuff.

How is Java configured to run?

How is Java configured to run?

Like, how much memory SQL Developer is ACTUALLY using – by the way, Windows Process Explorer will lie every time.

SQL Developer is using way less memory than Windows thinks it is.

SQL Developer is using way less memory than Windows thinks it is.

In older versions of SQL Developer, these were stored in the sqldeveloper.conf file in the bin directory – for Windows.

In versions 4.0 and higher, this is now stored per user in Windows.

For the Data Modeler, you want to find this file

C:\Users\jdsmith\AppData\Roaming\Oracle SQL Developer Data Modeler\4.0.2.840\product.conf

The italicized bits will vary per machine and version you’re running – but this should get you close.

You want to open the file with the application closed.

Edit the last bit

If you are getting the 'Low Memory Warning' Message Dialog while running
# the product, please increase the -Xmx value below from the default 800M to
# something greater, like 1024M or 1250M.  If after increasing the value,
# the product is no longer starting up because it fails to create a virtual
# machine, then please reduce the modified -Xmx value, or use a 64bit JDK
# which allows for very very large value for -Xmx.
#
# You can specify one value for any JDK using AddVMOption, OR you can specify
# separate values for 32-bit and 64-bit JDK's.
#
AddVMOption -Xmx800m
# Add32VMOption -Xmx800m
# Add64VMOption -Xmx800m

In particular the last bit. Instead of 800 megs of RAM, go higher like 2048. You should be pretty set at this point for even your really large designs. Don’t have that much memory to spare? Then you need a proper design and engineering machine.

Or just work with smaller designs.

And of course when you’re dealing with models that have so many objects, you really want to get familiar with SubViews.

If you’re not in Windows, this information is stored in your $HOME directory, in a hidden ‘.’ subdirectory.

For *NIX and Macs, look here’ish: $HOME/.sqldeveloper

In your user home directory...

In your user home directory…