How to limit RAM usage in Oracle 9

Question

I've got Oracle database that is used as a storage for web services. Most of the time data are in read-only mode and cached in RAM directly by the service. However during the system startup all data are pulled once from Oracle and the database tries to be smart and keeps the data in RAM (1GB).

How can I limit/control the amount of RAM available to the Oracle 9 instance?

Answer 1

There are several database parameters that control memory usage in Oracle. Here is a reasonable starting point - it's not a trivial exercise to get it right. In particular, you probably want to look at DB_CACHE_SIZE.

Answer 2

A short answer is modify SGA_MAX_SIZE. The long one follows.

If you are referring to the "data", you have to check the DB_CACHE_SIZE (size of the memory buffers) and related to this the SGA_MAX_SIZE (max memory usage for the SGA instance).

Because SGA_MAX_SIZE reffers to the SGA memory (buffers, shared pool and redo buffers) if you want to free up the size of buffers you also have to drecrease the SGA_MAX_SIZE.

Take a look to Setting Initialization Parameters that Affect the Size of the SGA or give more details.