and also...
Connor, August 15, 2001 - 12:33 pm UTC
Oracle on Solaris will use intimate shared memory (use_ism = true by default) which is "synonymous" (very loosely) with lock_sga.
LOCK_SGA in Windows NT/2000
Jayesh, June 11, 2004 - 12:30 am UTC
Hello Tom,
Will LOCK_SGA will take up SGA MEMORY completely from RAM in Windows NT/2000? If I enable LOCK_SGA noe memory will be needed from virtual memory?
June 11, 2004 - 7:59 am UTC
lock_sga is OS dependent and not available on windows.
Is use_ism a kernel parameter on Solaris? Thanks.
A reader, June 11, 2004 - 10:07 am UTC
June 11, 2004 - 4:22 pm UTC
yes.
I set it on Windows
Naveen.C, September 24, 2004 - 12:10 am UTC
Hi.
I have set lock_sga parameter to true in ORACLE 9iRel 2 on Windows. and started the database.
How do i Check whether its having some effect or not. ?
September 24, 2004 - 10:43 am UTC
i would have wanted that information before setting it -- because I would have wanted a reason (a goal in mind) for setting it. seems backwards "let set stuff and see what happens" -- doesn't it?
so i guess what I would do is see if my goal I had in mind when I decided to set lock_sga has been met...
(but
if ( the database started without error
AND lock_sga = TRUE
AND your OS supports it )
then
lock_sga is working
end if
lock_sga a bad idea?
A reader, May 13, 2013 - 3:27 pm UTC
Someone recently was frustrated the lock_sga needed to be taken out on a new server in order to start it.
They got the error
SQL> startup nomount;
ORA-27125: unable to create shared memory segment
IBM AIX RISC System/6000 Error: 1: Not owner
SQL>
So he takes the lock_sga parameter out and it works fine. Then he suggests that we should look into this because there is no reason NOT to use lock_sga. So this is the situation, there is nothing on the box but two oracle databases. And, the downside to using lock_sga is that it locks the SGA presumably for performance reasons at the EXPENSE of everything else (your words).. so what exactly is "everything else".. when nothing is on the box but Oracle and what could go wrong by locking the sga?
May 13, 2013 - 3:56 pm UTC
in order to get this going, please utilize support - you must have an OS setting not set correctly for this to not work.
Everything else is, well, anything else on that system. Your PGA memory you might need for sorting, the memory needed by the OS to work. The memory needed by the other database. The memory used by the OS to cache file systems. You know, everything that wants memory other than the SGA.
But is lock_sga really a good idea?
A reader, May 13, 2013 - 5:15 pm UTC
I do have a ticket open with support.. my question was more whether lock_sga is such a great idea. If someone says they can't think of a reason NOT to use it... aren't there quite a few actually?
May 13, 2013 - 5:27 pm UTC
I've listed above why you might not want to use it, it prevents paging.
paging can be good
paging can be bad
if the OS is at a point where it felt the need to page out parts of the SGA, you have squashed the machine, performance is going to start going downhill. However, PGA memory requests and other memory requests from various processes will continue to succeed.
if you prevent the paging out of the SGA, SGA access times will remain constant, but you might start getting ora-4030's and other related out of memory errors from processes.
If you know you have more than enough memory - locking the SGA would be harmless (in fact you might pin and lock the SGA on AIX). Otherwise locking the SGA will do what I just described, it would make SGA access times consistent, but could cause out of memory issues elsewhere.
(make sure you do not have memory_* parameters set - not that they are set to zero, but that they are NOT SET at all. Otherwise, if you have automatic memory management on - lock_sga will not work)
Thanks
A reader, May 14, 2013 - 5:55 am UTC
Thanks Tom.. helpful as always.