Tuesday, October 7, 2014

Optimized Shared Memory & MEMORY_MAX_TARGET in Solaris 11.2 + Oracle 12c

Starting with 12c, Oracle Database uses the Optimized Shared Memory (OSM) model of Oracle Solaris on Oracle Solaris 10 1/13 or later and Oracle Solaris 11 SRU 7.5 or later systems to implement Automatic Memory Management.

OSM allows dynamic resizing of System Global Area (SGA) without restarting the instance. It does not use the oradism utility and swap disk space. OSM is NUMA-optimized.

Total System Global Area 1258291200 bytes
Fixed Size                  3003176 bytes
Variable Size             905972952 bytes
Database Buffers          218103808 bytes
Redo Buffers               13770752 bytes
In-Memory Area            117440512 bytes

SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 1200M
SQL> show parameter memory_max_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 1200M


Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
Available system pagesizes:
  4K, 2048K
Supported system pagesize(s):
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
        4K       Configured               2               2        NONE
     2048K       Configured             601             601        NONE


oracle@solaris:~$ ipcs -dm
IPC status from <running system> as of Tuesday, October  7, 2014 08:39:35 PM SGT
T         ID      KEY        MODE        OWNER    GROUP      ALLOC
Shared Memory:
m         25   0x6a4bcd4  --rw-r-----   oracle oinstall           -
m         24   0x0        --rw-r-----   oracle oinstall   14680064
m         23   0x0        --rw-r-----   oracle oinstall   33554432
m         22   0x0        --rw-r-----   oracle oinstall 1123966976
m         21   0x0        --rw-r-----   oracle oinstall   83886080
m         20   0x0        --rw-r-----   oracle oinstall    4194304

If the column ALLOC shows an integer, it specifies that OSM is in use. If the column ALLOC shows a hyphen, it specifies that OSM is not in use.

oracle@solaris:~$ ipcs -im
IPC status from <running system> as of Tuesday, October  7, 2014 08:38:28 PM SGT
T         ID      KEY        MODE        OWNER    GROUP ISMATTCH
Shared Memory:
m         25   0x6a4bcd4  --rw-r-----   oracle oinstall       46
m         24   0x0        --rw-r-----   oracle oinstall       46
m         23   0x0        --rw-r-----   oracle oinstall       46
m         22   0x0        --rw-r-----   oracle oinstall       46
m         21   0x0        --rw-r-----   oracle oinstall       46
m         20   0x0        --rw-r-----   oracle oinstall       46

SQL> select (14680064+33554432+1123966976+83886080+4194304)/1024/1024 from dual;

(14680064+33554432+1123966976+83886080+4194304)/1024/1024
---------------------------------------------------------
                                               1201.89844

SQL> alter system set memory_max_target=1232M;
alter system set memory_max_target=1232M
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

                                          
SQL> alter system set memory_max_target=1232M scope=spfile;

System altered.

SQL> startup
ORACLE instance started.

Total System Global Area 1291845632 bytes
Fixed Size                  3003272 bytes
Variable Size             939527288 bytes
Database Buffers          218103808 bytes
Redo Buffers               13770752 bytes
In-Memory Area            117440512 bytes
Database mounted.

All SGA segments were allocated at startup
**********************************************************************
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
Available system pagesizes:
  4K, 2048K
Supported system pagesize(s):
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
        4K       Configured               2               2        NONE
     2048K       Configured             617             617        NONE
**********************************************************************

oracle@solaris:~$ ipcs -dm
IPC status from <running system> as of Tuesday, October  7, 2014 08:53:02 PM SGT
T         ID      KEY        MODE        OWNER    GROUP      ALLOC
Shared Memory:
m  268435459   0x6a4bcd4  --rw-r-----   oracle oinstall           -
m  268435458   0x0        --rw-r-----   oracle oinstall   14680064
m  268435457   0x0        --rw-r-----   oracle oinstall   33554432
m  268435456   0x0        --rw-r-----   oracle oinstall 1157496832
m  251658303   0x0        --rw-r-----   oracle oinstall   83886080
m  251658302   0x0        --rw-r-----   oracle oinstall    4194304

SQL> select (14680064+33554432+1157496832+83886080+4194304)/1024/1024 from dual;

(14680064+33554432+1157496832+83886080+4194304)/1024/1024
---------------------------------------------------------
                                                 1233.875

                                                
SQL> alter system set memory_target=1232M;

System altered.