Optimizing 64bit Memory Use in Oracle

Optimizing System Memory Use

When Oracle is setup as part of the installation of Retailpro9 the setup utility allocates one half of the server's physical memory to Oracle for database processes and use. The specific parameter that controls the amount of memory Oracle uses is called "MEMORY_TARGET" and it is stored in the "INIT.ORA" file which is stored in the [drive]:\Oracle\admin\RproODS\pfile folder. This file is read by Oracle each time the database instance is started. The "MEMORY_TARGET" parameter, once set, tells Oracle to automatically manage the memory for PGA and SGA using the value given to "MEMORY_TARGET" as the upper limit of its pool for the instance.
(It is Oracle's recommendation to use this option for memory management as opposed to manually setting the PGA and SGA values)

In most cases where the server being installed to uses a 32 bit operating system half of the server RAM is fine. With the advent of 64 bit operating systems and the 64 bit edition of Oracle it is possible to take advantage of more memory and it is an important optimization to adjust this memory assignment if the server has more than 4GB of RAM.

For example: If you install the 64bit edition of Retailpro to a server that has 8GB of RAM setup would set the MEMORY_TARGET parameter to 4GB of RAM.. If the system is primarily the Retailpro9 Database Server you could increase this to 6GB. An example of how to do this is described in the instructions below.


1. Make a backup of the init.ora located in the [drive]:\Oracle\admin\RproODS\pfile folder
2. Edit the original init.ora in notepad.exe
3. Locate the "MEMORY_TARGET=" string
4. Change it to 6G from what it was so that it reads MEMORY_TARGET=6G
5. Restart the RPROODS service in Windows services.

When you go to processes under task manager in Windows and observe the Oracle process under load you will find that Oracle is now able to utilize up to 6GB of physical memory for processing. The more memory that Oracle can take advantage of for the SGA and PGA the faster it can perform most processing functions.

Never allocate all of the free memory on a server to the "MEMORY_TARGET". Setting the "MEMORY_TARGET" too high can be extremely problematic. If you dedicate all of the free memory (or too much of it) to Oracle the server will be forced to cache other application operations to the hard disk (or in some cases fail due to lack of memory). Be mindful of the memory requirements for the routine server activities and programs.

Once the "MEMORY_TARGET" parameter is set it will not change. For example, if you install Retailpro9 one day onto a system with 4GB of RAM the "MEMORY_TARGET" will be set to 2GB. If the server is later upgraded to 16GB of RAM the "MEMORY_TARGET" parameter remains at 2GB until it is manually changed.

Additional Information:
For information on 32 bit systems and setting and optimizing memory on those operating systems go to the following knowledgebase item http://partners.retailpro.com/Support/Knowledgebase.php?a=17


Published on Mar 18, 2014 in Database, Performance


Find Another Article