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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle lock_sga parameter tips

Oracle Tips by Burleson Consulting

The lock_sga parameter is an operating system dependent (OSD) parm that is used to make the Oracle SGA region ineligible for swapping, effectively pinning the SGA RAM in memory.  This technique is also known as "page fencing", using lock_sga=true to guarantee that SGA RAM is never sent to the swap disk (during a page-out operation.

Because lock_sga is an OSD parm, it will not work on all platforms, and in any case, it's never an ideal solution unless you have no way to add RAM to stop the root cause, the RAM paging.  In Oracle, paging is noted by "page in" operations (as seen in the vmstat "pi" column), but you will get "false" page-ins as the part of normal program starting operations. 

To manage the memory segments, the UNIX and Linux kernel builds a memory map of the entire program when it starts. Included in this map is a note on whether the storage is ?in memory? or ?on swap disk?. As the program starts it begins accessing some of its pages that have never been loaded into RAM memory. Hence, you may see vmstat page-in?s when a large number of programs are starting and allocating their RAM memory.

See here for details on recognizing "real" server paging. 

SGA memory pinning

In HP/UX and Solaris it is possible to ?pin? the SGA so that it will never experience a page-in. This method is also known as memory fencing or memory pinning, depending on the UNIX vendor. Essentially, memory pinning marks the Oracle SGA as being non-swappable, and the memory region always resides at the most-recently used area of the RAM heap. Only that memory above and beyond the Oracle SGA is eligible for paging. On a dedicated Oracle UNIX server, this technique essentially prioritizes the Oracle SGA, telling UNIX to page-in only the RAM memory associated with individual connections to Oracle (PGA memory), and not the Oracle SGA region.

Please note that not all dialects of UNIX support RAM fencing and you cannot do RAM memory fencing on IBM-AIX, Linux and other dialects of UNIX.

In Solaris and HP/UX, the pinning is done by setting the following init.ora parameters.

lock_sga=true - for hp/ux
USE_ISM=true - Sun Solaris "Intimate Shared Memory"

When to use lock_sga

In practice, the lock_sga parameter is only needed when you have an over-stressed server with RAM paging, and it's always a better idea to add more RAM than to lock-in the SGA by making it non-swappable.  The lock_sga parameter is also useful in cases with multiple instances on an over-stressed server, where the lock_sga=true instance is to have priority over memory usage, at the expense of the other SGA's.

The docs note this about lock_sga (for Oracle AIX Tuning Optimization):

Shared memory can be pinned to prevent paging and to reduce I/O overhead. To perform this, set the LOCK_SGA parameter to true. On AIX 5L, the same parameter activates the large page feature whenever the underlying hardware supports it.

Run the following command to make pinned memory available to Oracle Database:

$ /usr/sbin/vmo -r -o v_pinshm=1

Oracle recommends specifying enough large pages to contain the entire SGA. The Oracle Database instance attempts to allocate large pages when the LOCK_SGA parameter is set to true. If the SGA size exceeds the size of memory available for pinning, or large pages, then the portion of the SGA exceeding these sizes is allocated to ordinary shared memory.

Many of the TPC benchmarks use lock_sga=true:

In Linux you can run the ulimit -l command to see the maximum lockable memory for any Linux user.

Reader Feedback:

Comment by Michael Wang:  3/4/2010
We are setting lock_sga=true on AIX. A couple of days ago we turned it off then we got some serious performance problem.
We turned it back on to resolve the problem. If we turn it off, Oracle will do a huge amount of swapping to disk even it has absolutely enough memory (32G physical memory and set SGA size-15G).
When the SGA <=4G, setting this parameter doesn't seem to affect paging much. But when the SGA is bigger, this parameter really works.

 
If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and 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.