Oracle XE Memory Tweaks

My machine at work is, shall we say, antiquated. I’m running Windows XP (yes, really) with 3GB or RAM.

Needless to say when I crank up ColdFusion and Oracle SQL Developer things slow down.

But I have JRun tuned fairly well so things generally hum along without impacting my ability to work (too much).

I recently installed Oracle XE so I could do development without relying on being on our work network.

The Oracle XE install was straightforward, and the web-based administration tool allowed me to quickly get a schema up and running.

I was happily coding along but things were sloooow. My laptop fan then came on and I knew something was eating my RAM.

Turns out my newly installed Oracle instance was consuming RAM like a cfObjective attendee at Fogo de Chão.

After a few Googles (most relating to Oracle 10g) I stumbled upon a fairly simple solution.

You can login to the web admin console and see the initial memory settings under ‘Parameters’:

This shows the value after adjusting things. It was originally 1073741824!

You can adjust this easily. Login to SQL as system and run the following:

:::sql
alter system set memory_target = 172m scope = spfile;

I read somewhere that the minimum you could set this was to 172m, and I haven’t tried anything lower. This reduced my RAM usage by about 50%.

After I ran that statement I restarted the Oracle services and RAM was significantly lower, and no longer constantly increasing.

You can also set:

:::sql
alter system set memory_max_target = 172m scope = spfile;

Supposedly this will set the max setting. You can then run the ‘set memory_target’ statement and adjust the memory ‘on the fly’ without restarting Oracle XE.

For my purposes I don’t need that level of adjustment but if you were doing load or unit testing maybe you could increase or decrease memory as needed.