Wednesday, November 16, 2011

Multiple Shared Memory Segments Created by Default on 11.2.0.3

There appear to be a change in the way shared memory segments are allocated in 11.2.0.3 when using automatic shared memory management (ASMM not AMM). Metalink note 15566.1 describes the three possible ways shared memory segments are allocated to depending on the SHMMAX value and the size of the SGA. They are "one-segment,contiguous multi-segment, and non-contiguous multi-segment."

When the SHMMAX value is larger than the SGA only one shared memory segment would be created. This was the behavior seen from 10gR2 upto 11.2.0.2. But as of 11.2.0.3 no matter how big the SHMMAX three segments get created.

Below is a comparison of segment creation for two databases one with 11.2.0.2 and other with 11.2.0.3. Both database reside in the same physical server.

The SHM* values on the server.
ipcs -lm

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 67108864
max total shared memory (kbytes) = 17179869184
min seg size (bytes) = 1
The maximum shared segment size possible is 64GB and the system has 12GB of RAM. Server is running RHEL 5 (2.6.18-194.el5). There are no other databases or applications running beside these two databases. Only one of these database will be up and running at one time. Server was rebooted after shutting down one database and before starting the other.

First the 11.2.0.2 database information
memory_max_target                    big integer 0
memory_target                        big integer 0
pga_aggregate_target                 big integer 1200M
sga_max_size                         big integer 3600M
sga_target                           big integer 3600M
Find the shared memory segments used by this database.
$ORACLE_HOME/bin/sysresv

IPC Resources for ORACLE_SID "ent11g2" :
Shared Memory:
ID              KEY
60751874        0x3b6207ac
Semaphores:
ID              KEY
10551318        0x74afa7cc
Oracle Instance alive for sid "ent11g2"
The shared memory ID and key will be used to match the key shown with ipcs command. Since this is the only database running at this time and also shmmax is larger than the sga size only one shared segment is expected
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x3b6207ac 60751874   oracle    660        3776970752  29
The key and shmid match confirming that this is the shared segment created for this database.

Now the 11.2.0.3 database information (similar size sga as 11.2.0.2)
memory_max_target                    big integer 0
memory_target                        big integer 0
pga_aggregate_target                 big integer 1200M
sga_max_size                         big integer 3600M
sga_target                           big integer 3600M
Finding the shared memory segment for the database as above
$ORACLE_HOME/bin/sysresv

IPC Resources for ORACLE_SID "ent11g3" :
Shared Memory:
ID              KEY
61079554        0x00000000
61112324        0x00000000
61145093        0xf9058128
Semaphores:
ID              KEY
10977302        0xf1843220
Oracle Instance alive for sid "ent11g3"
Three segments have been created. Looking at the ipcs command output
ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x00000000 61079554   oracle    640        33554432     28
0x00000000 61112324   oracle    640        3741319168   28
0xf9058128 61145093   oracle    640        2097152      28
Instead of one as before three shared segments are created. If the size of all these segments are added up it would be equal (33554432 + 3741319168 + 2097152 = 3776970752) to the size of the single segment created with 11.2.0.2 database.

This is the behavior shown on all 11.2.0.3 databases whether they were upgraded from 11.2.0.2 to 11.2.0.3 or created new as in this case. Another observation is in all cases the last segment is of 2M size and second segment is the largest and close to the actual SGA size while size of the first segment changes depending on the SGA size.

Unless this a bug introduced in 11.2.0.3, this signals the change in behavior for allocating shared memory segments when using ASMM.

Interestingly even with AMM the number of created shared memory segments between 11.2.0.2 and 11.2.0.3 are 1 to 3 (this may or may not be related to the above observations and ipcs is not a correct way of finding out about the shared memory segments when using AMM. More on metalink note 731658.1)

With AMM on 11.2.0.2
$ORACLE_HOME/bin/sysresv
IPC Resources for ORACLE_SID "ent11g2" :
Shared Memory:
ID              KEY
3244034         0x3b6207ac
Semaphores:
ID              KEY
3801110         0x74afa7cc
Oracle Instance alive for sid "ent11g2"
With ipcs -m
ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x3b6207ac 3244034    oracle    660        4096        0
With AMM on 11.2.0.3
$ORACLE_HOME/bin/sysresv
IPC Resources for ORACLE_SID "ent11g3" :
Shared Memory:
ID              KEY
3047426         0x00000000
3080196         0x00000000
3112965         0xf9058128
Semaphores:
ID              KEY
3506198         0xf1843220
Oracle Instance alive for sid "ent11g3"
ipcs -m output shows three segments
ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x00000000 3047426    oracle    640        4096       0
0x00000000 3080196    oracle    640        4096       0
0xf9058128 3112965    oracle    640        4096       0

Update 29 November 2011
Had raised a SR about this and oracle seem to be missing the point that 11.2.0.3 is creating more segments than 11.2.0.2. Instead focusing on shmmax values which was set to 64GB saying it's too much. This has no effect on the number of segments,system only creates segments of requested sizes. Only good thing to come out of this so far is a request for a strace output. Start the database with
strace -f -o start.out sqlplus "/ as sysdba"
once started exit the SQL prompt. This may get stuck and might require killing the process manually.
ipcs -m output
ipcs -m
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x00000000 8880130    oracle    640        33554432    25
0x00000000 8912900    oracle    640        3741319168  25
0xf9058128 8945669    oracle    640        2097152     25
sysresv output
$ORACLE_HOME/bin/sysresv

IPC Resources for ORACLE_SID "ent11g3" :
Shared Memory:
ID              KEY
8880130         0x00000000
8912900         0x00000000
8945669         0xf9058128
Semaphores:
ID              KEY
9502742         0xf1843220
Oracle Instance alive for sid "ent11g3"
From the strace output these segment creation system calls could be picked up. Size of the segment and segment id has been highlighted.
21293 shmget(IPC_PRIVATE, 33554432, IPC_CREAT|IPC_EXCL|0640) = 8880130
21293 mmap(NULL, 1048576, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b8cd1281000
21293 getrlimit(RLIMIT_STACK, {rlim_cur=32768*1024, rlim_max=RLIM_INFINITY}) = 0
21293 open("/proc/self/maps", O_RDONLY) = 11


21293 shmget(IPC_PRIVATE, 3741319168, IPC_CREAT|IPC_EXCL|0640) = 8912900
21293 getrlimit(RLIMIT_STACK, {rlim_cur=32768*1024, rlim_max=RLIM_INFINITY}) = 0
21293 open("/proc/self/maps", O_RDONLY) = 11

21293 shmget(0xf9058128, 2097152, IPC_CREAT|IPC_EXCL|0640) = 8945669
21293 shmget(0xf9058129, 4096, 0)       = -1 ENOENT (No such file or directory)
21293 shmget(0xf9058129, 4096, 0)       = -1 ENOENT (No such file or directory)
21293 shmget(0xf9058129, 0, 0)          = -1 ENOENT (No such file or directory)
21293 shmget(0xf905812a, 4096, 0)       = -1 ENOENT (No such file or directory)
21293 shmget(0xf905812a, 4096, 0)       = -1 ENOENT (No such file or directory)
21293 shmget(0xf905812a, 0, 0)          = -1 ENOENT (No such file or directory)
21293 shmget(0xf905812b, 4096, 0)       = -1 ENOENT (No such file or directory)
21293 shmget(0xf905812b, 4096, 0)       = -1 ENOENT (No such file or directory)
21293 shmget(0xf905812b, 0, 0)          = -1 ENOENT (No such file or directory)
21293 getrlimit(RLIMIT_STACK, {rlim_cur=32768*1024, rlim_max=RLIM_INFINITY}) = 0
21293 open("/proc/self/maps", O_RDONLY) = 11
However on 11.2.0.2 database you only see one segment creation system call on the strace output matching the entire sga size.
ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x3b6207ac 9142278    oracle    660        3776970752   30


21778 munmap(0x2af10d7c3000, 4096)      = 0
21778 shmget(0x3b6207ac, 3776970752, IPC_CREAT|IPC_EXCL|0660) = 9142278
21778 shmget(0x3b6207ad, 4096, 0)       = -1 ENOENT (No such file or directory)
21778 shmget(0x3b6207ad, 4096, 0)       = -1 ENOENT (No such file or directory)
21778 shmget(0x3b6207ad, 0, 0)          = -1 ENOENT (No such file or directory)
21778 shmget(0x3b6207ae, 4096, 0)       = -1 ENOENT (No such file or directory)
21778 shmget(0x3b6207ae, 4096, 0)       = -1 ENOENT (No such file or directory)
21778 shmget(0x3b6207ae, 0, 0)          = -1 ENOENT (No such file or directory)
21778 shmget(0x3b6207af, 4096, 0)       = -1 ENOENT (No such file or directory)
21778 shmget(0x3b6207af, 4096, 0)       = -1 ENOENT (No such file or directory)
21778 shmget(0x3b6207af, 0, 0)          = -1 ENOENT (No such file or directory)
21778 mmap(NULL, 1048576, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2af10d7c3000
21778 getrlimit(RLIMIT_STACK, {rlim_cur=32768*1024, rlim_max=RLIM_INFINITY}) = 0
21778 open("/proc/self/maps", O_RDONLY) = 5
Update 21 December 2011
Latest update is this issue could be related to following bug Bug 13360043 - ORA-27123: UNABLE TO ATTACH TO SHARED MEMORY SEGMENT STARTING 11.2.0.3 DATABASE
Oracle still investigating.

Update 28 December 2011
Oracle asked to comapre the db_block_numa value on both 11.2.0.2 and 11.2.0.3. On both databases it was the same value
select x.ksppinm, y.ksppstvl from x$ksppi x , x$ksppcv y where x.indx = y.indx and x.ksppinm like '\_db_block_numa%' escape '\' order by x.ksppinm;

KSPPINM KSPPSTVL
-----------------
_db_block_numa 1
Then asked to change the enable_NUMA_optimization to TRUE only on 11.2.0.3 and see if the same behavior exists. Logic was with _enable_NUMA_optimization=false and _db_block_numa=1 oracle would allocate only 1 shared segment for the SGA. (Couldn't understand why then change it to true). By default _enable_NUMA_optimization is false, could be found out with
select x.ksppinm, y.ksppstvl from x$ksppi x , x$ksppcv y where x.indx = y.indx and x.ksppinm like '\_enable_NUMA_optimization%' escape '\' order by x.ksppinm;
Changed it to true ask instructed but no change.

Following two metalink notes also listed
11G on HP Creates 6 Shared Memory Segments [Doc ID 601552.1]
Shared Memory Segment Is Split Up In Different Pieces With NUMA Setting [Doc ID 429872.1]

Update 06 January 2012
Oracle has finally reproduced the behavior in house. This will be logged as a bug. Blog will be updated with the findings.

Update 03 February 2012
Oracle has created a metalink note explaining this behavior. Why Multiple Shared Memory Segments are Created From 11.2.0.3 [ID 1399908.1]
This segment creation behavior change is the side effect of a fix for the unpublished bug 12654172. Not yet known if this side effect is localised to 11.2.0.3 and will be "fixed" with a PSU or in 11.2.0.4 or 12.1
It seem this is the expected behavior going forward from 11.2.0.3. This means that one segment model described in 15566.1 won't be there anymore.