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
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
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
RUNQUE_WAITS PAGE_IN SYSTEM_CPU USER_CPU IDLE_CPU
---------------- ------------ ---------- ---------- ---------- ----------
06/02/2001 05:01 2 85 1 0 99
06/02/2001 13:47 2 193 0 0 99
06/03/2001 05:04 0 114 2 3 95
06/03/2001 22:31 1 216 0 1 99
06/04/2001 05:02 0 146 1 1 99
06/04/2001 22:34 1 71 1 8 90
06/05/2001 06:57 1 213 0 0 99
06/05/2001 07:25 1 113 0 0 99
06/05/2001 07:35 1 72 0 0 99
06/05/2001 11:06 1 238 0 1 99
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.