Server Memory Settings Oracle Database Tips by Donald Burleson

The first step when tuning server memory is to review the kernel settings that relate to available memory. The kernel settings for memory usage (i.e., SHMMAX, SHMMNI, db_max_pct) are critical to effective Oracle performance, and you should double-check all of your kernel parameters to ensure that the server memory is properly configured.

We also must verify the configuration of the swap disk. As you may know, the swap disk is a special system disk that is reserved to accept memory frames that are paged-out from physical RAM. Most servers recommend that the size of the swap disk be set to double the amount of physical RAM.

Very Large Memory and Oracle

It is important to note that some servers are not capable of addressing ?high memory.? The high-memory boundary is a physical constraint that is determined by the bit-size of the application, and the only way to utilize above-the-line memory is to use special OS techniques. For example, in many 32-bit versions of Oracle, all memory over 1.7 gigabytes cannot be addressed regardless of the amount of RAM on the server. This can cause a very perplexing problem, since the database server will experience page-in operations, while top and glance utilities report that there is excess memory on the server. In short, the sum of all SGA memory for all of the Oracle instances on some servers, such as HP, cannot exceed 1.7 gigabytes. For some UNIX environments such as Solaris, there are special patches that can be applied on a 32-bit server to allow the DBA to create SGA regions in excess of 2 gigabytes.

In Oracle9i, memory management is further enhanced by the creation of a pga_aggregate_target parameter to hold all PGA RAM regions inside the Oracle SGA.  In the Oracle9i architecture, 80 percent of all RAM memory (leaving 20 percent for the UNIX kernel) on a dedicated database server can be allocated directly to the SGA, and you should not have any UNIX RAM swapping or paging.

If you cannot upgrade to 64-bit Oracle and you want to address memory above the line, operating system techniques can be used. For example, in HP/UX, special patches can be applied to allow Oracle regions to run above 1.7 gigabytes. HP calls this technique ?memory windows,? and it uses a SHARED_MAGIC executable to route application to above-the-line memory regions.

Bear in mind that all 32-bit applications are required to run in low memory. For example, Oracle applications are currently 32-bit and will not be able to address high memory, above the 1.7 gigabyte limit. Fortunately, all versions of 64-bit Oracle are capable of addressing high memory. However, you must ensure that your Oracle Database and any other applications are capable of addressing all of the available memory. For example, next we see a clear case of RAM overload, even though the CPU appears to be 99-percent idle:

L 6-12

Making Oracle Memory Nonswappable

Just like with CPU shortages, the best remedy to a RAM problem is to add additional RAM to the server. However, there are some short-term techniques that can be used to prevent the Oracle SGA memory from paging. On some operating systems, it is possible to use a memory-fencing technique to ensure that the Oracle SGA is never paged-out to the swap disk.

Memory fencing with the lock_sga Initialization Parameter

The lock_sga init.ora parameter will lock the entire SGA into physical RAM memory, making it ineligible for swapping. The lock_sga parameter does not work for Windows NT or AIX, and the setting for lock_sga will be ignored. For AIX 4.3.3 and above, you can set the shm_pin parameter to keep the SGA in RAM, and you can get details about this from your AIX documentation.

Solaris Memory Fencing

In Sun Solaris, you can set the use_ism parameter to invoke intimate shared memory for the Oracle SGA. In releases of Oracle prior to Oracle8i you can set the init.ora parameter use_ism=true. The use_ism init.ora parameter was obsoleted in 8.1.3, and in Oracle8i use_ism becomes a hidden parameter that defaults to True. Memory page locking is implemented in Solaris by setting some bits in the memory page's page structure. The page-out, which runs if free memory gets low, checks the status of the page's lock fields. If the field is nonzero, the page is considered locked in memory and thus not marked as a candidate for freeing.

CAUTION: There is a bug associated with use_ism on some versions of Solaris. For details, see MOSC for Note:1057644.6, Note:69863.1, Note:1055268.6, Doc ID 77604.1, Note:48764.1, and Note:1054590.6. You can access MOSC at: http://MOSC.oracle.com/home.html

 

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.