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.