Q:

Why Oracle 11g on windows default installation keeps increasing memory usage even when idle?

I installed Oracle 11g on my Windows XP 64 bit desktop with pretty much the default options. Now when I start the database, it just keeps increasing the memory usage few hundred KB at a time even when the database is not in use. At the moment I am looking at Windows Task Manager and it is showing oracle.exe using more than 1.4 G and counting.
What's happening? How can I set a maximum limit on the memory usage?
It's a developer machine and database will not be huge in most cases. Even when they are huge, the number of concurrent users will be very few.

A:

What is your memory_target set to? If it's set high, then I wouldn't be surprised to see it grow to the max size over time. Take a look here for more information on the new memory_target parameter in 11g. That will put a cap on how much memory Oracle will attempt to get.
The memory_target parameter configures Oracle to self manage memory parameters that used to require individual attention:

DB_CACHE_SIZE
SHARED_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE
PGA_AGGREGATE_TARGET

A note is that the STATISTICS_LEVEL must be set to TYPICAL.
Two parameters are actually involved here: memory_max_target and memory_target. The first sets the maximum value for memory_target and requires an instance restart to change:
ALTER SYSTEM SET memory_max_target = 1500M SCOPE=SPFILE;

Once in place, the memory_target parameter can be set dynamically without an instance restart:
ALTER SYSTEM SET memory_target = 1000M;

This is a really nice feature for folks who want a basic "set it and forget it" memory management setup. Set the highest value you think you'll need for the memory_max_target, and then play with the value for memory_target to tune to your needs. Let Oracle manage the individual parameters. Oracle will use the instance statistics to manage these values, so as your instance evolves in terms of usage patterns, Oracle will take this into account.
Another nice reference on these parameters can be found at the Oracle FAQ's Oracle Wiki.

