1

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.

1 Answer 1

2

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.

1
  • If you get ALTER SYSTEM SET memory_target = 412M Error report: SQL Error: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 464M 02097. 00000 - "parameter cannot be modified because specified value is invalid" *Cause: Though the initialization parameter is modifiable, the modified value is not acceptable to the parameter. *Action: Check the DBA guide for range of acceptable values for this parameter. Then restart the OracleServiceXE to clear current ram usage.
    – Tim Abell
    Nov 24, 2011 at 10:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .