I am attempting to configure Oracle 9i Release 2 on a Windows 2000 Advanced Server as follows:
- DB_CACHE_SIZE= 1 Gigabyte
- SHARED_POOL_SIZE= 512 Megabytes
- Large Pool = Default
- Java Pool = Default
- PGA = 512 Megabytes
The server has 4 gigabytes in total memory with a dual processor. I want to assign 60% of the total physical memory for the Oracle databse, but it failed to configure the database properly, and it returned the messages, "Out of Memory," "Unable to grow cache memory", and "value is invalid." I would really appreciate it if you could help me.
If the error you are receiving is:
ORA-00384: Insufficient memory to grow cache
The solution is to increase your SGA_MAX_SIZE parameter and bounce the database. Query V$PARAMETER to see the current setting of the SGA_MAX_SIZE parameter. To change this value, use a command similar to the following:
ALTER SYSTEM SET sga_max_size=1500M SCOPE=SPFILE;
This will not take affect until you bounce the database.
In order for your SGA to exceed 2 gigabytes of memory, you will need the 64-bit version of Oracle. But just because Oracle recommends a certain percentage of your server's physical memory does not mean that you really need to use that much. On one of my servers, I've got 8 gigabytes of memory, but my SGA is a total of only 300 megabytes. Consider decreasing your Buffer Cache size as 1 gigabyte may be too much. Additionally, a Shared Pool size of 300 megabytes is adequate for many instances.
If you want to learn more about the SGA_MAX_SIZE parameter, please visit my web site (http://www.peasland.net) where I have a white paper describing how to set this parameter.
This was first published in November 2004