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
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.
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
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
The docs note this about lock_sga (for
Oracle AIX Tuning
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
$ /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
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.
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
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.