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.

V$SGA_RESIZE_OPS.STATUS ERROR

In 10G version, the ASMM(Automatic Shared Memory Management) has been introduced to relieve DBAs from sizing some parts of the SGA by themselves.

ASMM can be configured by using the SGA_TARGET initialization parameter.

when set > 0, the ASMM is enabled

when set to 0, the ASMM is disabled

V$SGA_RESIZE_OPS displays information about the last 800 completed SGA resize operations.

ALTER SESSION SET nls_date_format = 'DD/MM/YYYY:HH24:MI:SS';

SET PAGESIZE 900

SET LINESIZE 255

COL COMPONENT FORMAT A25

COL INITIAL_SIZE FORMAT A10

COL TARGET_SIZE FORMAT A10

COL FINAL_SIZE FORMAT A10

COL OPER_TYPE FORMAT A10

select START_TIME, component, oper_type, oper_mode,status, initial_size/1024/1024 "INITIAL", target_size/1024/1024 "TARGET", FINAL_SIZE/1024/1024 "FINAL", END_TIME

from v$sga_resize_ops

order by start_time, component;

START_TIME COMPONENT OPER_TYPE OPER_MODE STATUS INITIAL TARGET FINAL END_TIME

------------------- ------------------------- ---------- --------- ---------- ---------- ---------- ---------- -------------------

16/11/2009:15:12:24 DEFAULT buffer cache SHRINK IMMEDIATE COMPLETE 5776 5760 5760 16/11/2009:15:12:24

16/11/2009:15:12:24 shared pool GROW IMMEDIATE COMPLETE 4416 4432 4432 16/11/2009:15:12:24

16/11/2009:19:22:34 DEFAULT buffer cache GROW DEFERRED ERROR 5760 5856 5792 16/11/2009:19:22:35

16/11/2009:19:22:34 shared pool SHRINK DEFERRED ERROR 4432 4336 4400 16/11/2009:19:22:35

My result found "ERROR" (sizing operation was unable to complete) status.

Use this error checked in metalink(556928.1) and they told to increase the SGA_TARGET.

So, I checked v$sga_target_advice ...

select * from v$sga_target_advice order by sga_size;

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR Estd Phys Reads

---------- --------------- ------------ ------------------- ----------------

2560 .25 68538927 2.6068 5,648,312,410

5120 .5 34072278 1.2959 1,863,613,370

7680 .75 28290588 1.076 1,204,264,183

10240 1 26292359 1 1,020,822,398

12800 1.25 25737600 .9789 973,149,992

15360 1.5 25416834 .9667 973,149,992

17920 1.75 25377404 .9652 847,180,508

20480 2 25377406 .9652 767,045,950

Idea???...Investigate & solve anything reduce SGA size... (perhaps increase SGA_TARGET)... or it's just Bug!