Configuring Java for ORDS and SQLcl

Updated 25 Nov 2024

Configuring Java for ORDS and SQLcl is slightly different than for our other offerings such as Oracle SQL Developer and SQL Developer Data Modeler.

The database tools team builds Java applications. But by and large, we don’t expect nor require our customers to be Java developers.

However, knowing a few things about running or managing Java applications can be extremely valuable.

Many java applications provide just the basic built in CLI that Java provides…

Bash

java -jar xyz.war -Xmx1024m

This would startup/run your XYZ application, and tell it to set the maximum amount of memory in the Java Virtual Machine to a gigabyte of RAM.

Different versions of Java have different defaults for the JVMs, but passing these flags allow you to deviate from those defaults.

However, both SQLcl and ORDS have specialized CLIs that don’t accommodate JVM flags on the call itself.

So what’s a user (SQLcl) or admin (ORDS) to do if they want to deviate from the defaults?

_JAVA_OPTIONS enters the room

Setting this environment variable will allow you to influence the JVM that starts up when either SQLcl or ORDS starts up using the ‘sql’ and ‘ords’ programs/scripts.

Let’s take a quick look at SQLcl –

Bash

set _JAVA_OPTIONS=-Xms128m -Xmx1024m

Configuring Java for ORDS and SQLcl with env var _JAVA_OPTIONS — SQLcl acknowledges that you have sent over the JVM flags at startup.

Xms says how much memory the JVM will start with.

Xmx says how much memory the JVM will be limited to.

I’m going to fire up VisualVM – a free Java utility you can download here.

With SQLcl running I can see what is happening on the JVM, and there’s a nice chart showing memory information.

So basically 128 and 1024 megabytes, respectively.

But wait, how do I know this actually worked?

OK, we’ll clear the ENV VAR, and start it up again and compare.

Bash

set _JAVA_OPTIONS=

Note there’s no message about picking up any Java settings at startup.

And let’s check our program in VisualVM, again.

Same basic starting size, but 2x as much memory to possibly consume down the road.

If you look in the startup script we provide for Linux/Macs, we’re doing this:

Bash

AddVMOption -Xms64M
AddVMOption -Xmx2G

Otherwise the default JVM will grab 10% of avail memory for startup and 25% machine’s overall physical memory for the max. These numbers may vary depending on version of Java…

So I’m able to give a higher starting value AND a lower cap of memory for SQLcl using _JAVA_OPTIONS.

You’ll want to set this specifically for the session running SQLcl or ORDS – setting it globally would impact every java program running on the machine, which is probably what you DON’T want to happen.

And ORDS?

Same dealio. Let’s say you have a Docker with a very small memory space, and you want to startup ords, so we’ll say you get 64MB of RAM at startup and a max of 256MB.

The environment variable will work…

A better option for ORDS

Better to be explicit about your runtime environment via the command itself vs relying on a environment variable that will impact any Java app in that area.

💡 Use the –java-options flag on the root ords command.

Bash