Tuesday, November 17, 2009

LOCK_SGA can not use with AMM or ASMM

LOCK_SGA locks the entire SGA into physical memory. It is usually advisable to lock the SGA into real (physical) memory, especially if the use of virtual memory would include storing some of the SGA using disk space.

On 11gR2:
SQL> show parameter lock_sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
SQL> show parameter memory_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 776M
SQL> ALTER SYSTEM SET LOCK_SGA=TRUE SCOPE=SPFILE;

System altered.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00847: MEMORY_TARGET/MEMORY_MAX_TARGET and LOCK_SGA cannot be set together
On 10gR2:
SQL> show parameter lock_sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE

SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 168M
SQL> ALTER SYSTEM SET LOCK_SGA=TRUE SCOPE=SPFILE;

System altered.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory
If need to use LOCK_SGA -)
- To Disable the Use of Automatic Memory Management or Automatic Shared Memory Management and enable manual shared memory management
Or...
- Don't lock the SGA.

2 comments:

Pete Finnigan said...

Hey Surachart,

Have a look at my site http://www.petefinnigan.com/ramblings/lock_sga.htm from a few years ago where I came up with a different solution to locking the SGA in core.

cheers

Pete

Surachart Opun said...

Thank You..
Nice idea to use C code with shmctl() - SHM_LOCK, SHM_UNLOCK

$ ipcs -m | grep oracle
0x8895c820 393227 oracle 660 4096 0

0x94106560 458764 oracle 660 4096 0

$ ipcs -m | grep oracle | awk '{print $2'} | while read segment ; do ./fix_in_core $segment ; done
locked Segment 393227
locked Segment 458764

$ ipcs -m | grep oracle
0x8895c820 393227 oracle 660 4096 0 locked
0x94106560 458764 oracle 660 4096 0 locked

$ ipcs -m | grep oracle | awk '{print $2'} | while read segment ; do ./unfix_in_core $segment ; done
unlocked Segment 393227
unlocked Segment 458764

$ ipcs -m | grep oracle
0x8895c820 393227 oracle 660 4096 0

0x94106560 458764 oracle 660 4096 0