Oracle Memory Parameters
With Oracle Database 11g’s Automatic Shared Memory Management (ASMM) feature, the management of Oracle’s various memory parameters has essentially come down to setting one parameter. And if there were no more 9i or 10g databases out there, or if all applications used memory in the optimal way, memory management would be simple. However, just as some SQL Server 2000 and 2005 servers are still in use, earlier versions of Oracle remain in service. So, you do need an understanding of how Oracle uses memory.
The two main memory areas for Oracle are the System Global Area (SGA) and the Program Global Area (PGA). Under the SGA, the memory is divided into other areas for handling the SQL statements, data blocks, and log buffers. The PGA is the workload area for server processes. Figure shows the memory parameters for the SGA and PGA.
Figure : Memory parameters for the SGA and PGA
In Oracle9i Database and Oracle Database 10g, the dynamic memory parameters allow the memory to adjust within the SGA. The SGA_MAX_SIZE and SGA_TARGET parameters are set, and then memory is adjusted between DB_CACHE_SIZE, SHARED_POOL_SIZE, and the other pools (such as LARGE_POOL_SIZE and JAVA_POOL_SIZE). This helps for systems that might have different types of workload at different times. Without manual intervention, the allocations could adjust based on the memory needs of the different areas. Of course, in setting the SGA_MAX_SIZE and SGA_TARGET parameters, the statistics must be at the typical level for the correct information to be collected to provide the details required to adjust the memory areas. But why not just set SGA_TARGET and SGA_MAX_SIZE to the same values, if you are allocating a maximum value of memory to Oracle? And, in that case, why not have just one parameter to set? In Oracle Database 11g using ASMM, you can simply set MEMORY_TARGET and let Oracle handle the rest. In this version, the memory allocation on the operating system side is divided into smaller chunks. Shared memory segments are available for Oracle to use for the SGA.
NOTE: Oracle Database 11g also has the parameter MEMORY_MAX_TARGET, which allows you to specify the maximum setting for the MEMORY_TARGET parameter. However, when you set MEMORY_TARGET, the MEMORY_MAX_TARGET parameter will be set to the same value automatically, so you don’t need to set MEMORY_MAX_TARGET directly.
On the Linux platform, Oracle uses shared memory in /dev/shm. Here is a typical error message that will come up if the operating system doesn’t have enough memory to mount the /dev/shm file system:
ORA-00845: MEMORY_TARGET not supported on this system
In the alert log:
Starting ORACLE instance (normal)
WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 4294967296 bytes. /dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 0 and used is 0 bytes. Using operating system memory in this way is a new shift in the Oracle Database 11g approach. Earlier versions used the System V-style shared memory, and you could verify the size of the shared memory used by Oracle using the operating system command ipcs –b which shows what semaphores have been allocated. To be able to view the memory allocated to Oracle with the POSIX-style shared memory, the OS commands for checking the space used in the file system are used, as in the following example.
$df –k /dev/shm
Filesystem 1K-blocks Used Available Use% Mounted on 32486028 180068 32305960 1% /dev/shm
Using the memory in Windows for Oracle is similar to using it for SQL Server. Address Windowing Extensions (AWE) and the Windows 4GB RAM Tuning feature are options available for the Oracle database, too. Using a Very Large Memory (VLM) configuration has been available for Oracle on Windows since Oracle8i.
Oracle Database 11g on Windows can take advantage of AWE to use more than 3GB of memory. Also, setting the /3GB switch in the boot.ini file will at least allow for using about 3GB of memory for Oracle. To use up to 64GB of memory, the /PAE switch needs to be enabled. Physical Address Extension (PAE) allows for mapping of a virtual addressable space above the 4GB of memory. Having both the /3GB and /PAE switches enabled at the same time will allow only 16GB of memory to be available, so the /3GB switch should be disabled to allow for more memory to be used by the PAE. The memory limitations are really applicable only on 32-bit Windows systems. With 64-bit systems, the limitations are measured in terabytes. Windows supports the use of large pages for systems using a large amount of memory. The parameter in the Oracle key of the registry needs to be set as ORA_LPENABLE=1 to enable the large pages. In order to use VLM on Windows, the oracle user needs the “Lock memory pages” privilege. The USE_INDIRECT_DAT_BUFFERS=TRUE parameter must be set in the parameter file for Oracle. Also, the DB_BLOCK_BUFFERS parameter must
be set for the database cache. The dynamic SGA parameters are not available for the very large memory settings. If the system doesn’t need more than the 3GB of memory for the SGA, you should consider just using the 4GB RAM Tuning feature, so the dynamic parameters are available.
Again, with Oracle Database 11g, you can simply set the MEMORY_TARGET parameter and have Oracle manage the rest. However, adjusting some of the other memory parameters may improve the performance of particular applications. When used in combination with ASMM, the settings of the individual parameters are implemented as minimum values.