Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








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

---------------- ------------ ---------- ---------- ---------- ----------
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

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:


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

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.