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. 

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.