Tanel Poder’s blog: Core IT for Geeks and Pros

This is my attempt for getting cheap popularity out of recent Oracle 11g release. This is not going to be another Oracle 11g new features list, I’ll be just posting any of my research findings here, in a semi-organized way.

The first post is is about Automatic Memory Management. AMM manages all SGA + PGA memory together, allowing it to shift memory from SGA to PGAs and vice versa. You only need to set a MEMORY_TARGET (and if you like, MEMORY_MAX_TARGET parameter).

You can read rest of the general details from documentation, I will talk about how this feature has been implemented on OSD / OS level (or at least how it looks to be implemented).

When I heard about MEMORY_TARGET , then the first question that came into my mind was that how can Oracle shift shared SGA memory to private PGA memory on Unix? This would mean somehow deallocating space from existing SGA shared memory segment and releasing it for PGA use. To my knowledge the traditional SysV SHM interface is not that flexible that it could downsize and release memory from a single shared memory segment. So I started checking out how Oracle had implemented this.

One option of course is not to implement it at all – just do not use the extra space in the extra SGA area and it will be soon paged out if there’s memory pressure (as long as you don’t keep your SGA pages locked – which can’t be used together with MEMORY_TARGET anyway). However should this “unneeded” memory be used again, all would have to be loaded back from swap area.

I started by checking what are the shared memory IDs for my instance:

$ sysresv

IPC Resources for ORACLE_SID "LIN11G" :
Shared Memory:
ID              KEY
1900546         0x00000000
1933315         0xa62e3ad4
Semaphores:
ID              KEY
884736          0x6687edcc
Oracle Instance alive for sid "LIN11G"

Ok, let’s look for corresponding SysV SHM segments:

$ ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x00000000 1900546    oracle    660        4096       0
0xa62e3ad4 1933315    oracle    660        4096       0

The segments are there, but wait a minute, they are only 4kB in size each! If there are no large shared memory segments used, where does Oracle keep its SGA?

The immediate next check I did was looking into the mapped memory for an Oracle instance process – as the SGA should be definitely mapped there!

$ pmap `pgrep -f lgwr`
29861:   ora_lgwr_LIN11G
00110000      4K rwx--    [ anon ]
00111000     32K r-x--  /apps/oracle/product/11.1.0.6/lib/libclsra11.so
00119000      4K rwx--  /apps/oracle/product/11.1.0.6/lib/libclsra11.so
...
...
49000000  16384K rwxs-  /dev/shm/ora_LIN11G_3997699_0
4a000000  16384K rwxs-  /dev/shm/ora_LIN11G_3997699_1
4b000000  16384K rwxs-  /dev/shm/ora_LIN11G_3997699_2
4c000000  16384K rwxs-  /dev/shm/ora_LIN11G_3997699_3
4d000000  16384K rwxs-  /dev/shm/ora_LIN11G_3997699_4
...
...
88000000  16384K rwxs-  /dev/shm/ora_LIN11G_3997699_63
89000000  16384K rwxs-  /dev/shm/ora_LIN11G_3997699_64
bfc1f000     88K rwx--    [ stack ]
 total  1225048K

pmap output reveals that Oracle 11g likes to use /dev/shm for shared memory implementation instead. There are multiple 16MB “files” mapped to Oracle server processes address space.
This is the Linux’es POSIX-oriented SHM implementation, where everything, including shared memory segments, is a file.

Thanks to allocating SGA in many smaller chunks, Oracle is easily able to release some parts of SGA memory back to OS and server processes are allowed to increase their aggregate PGA size up to the amount of memory released.
(Btw, if your MEMORY_MAX_TARGET parameter is larger than 1024 MB then Oracle’s memory granule size is 16MB on Linux, otherwise it’s 4MB).

Note that the PGA memory is still completely independent memory, allocated just by mmap’ing /dev/zero, it doesn’t really have anything to do with shared memory segments ( unless you’re using some hidden parameters on Solaris, but that’s another story ).
PGA_AGGREGATE_TARGET itself is just a recommended number, leaving over from MEMORY_TARGET – SGA_TARGET (if it’s set). And Oracle uses that number to decide how big PGAs it will “recommend” for sessions that are using WORKAREA_SIZE_POLICY=AUTO.

So how does Oracle actually release the SGA memory when it’s downsized?

Compare these outputs:

/dev/shm before starting instance:

$ ls -l /dev/shm/
total 0

Obviously there’s nothing reported as no /dev/shm segments are in use.

/dev/shm after starting instance with fairly large SGA (note that some output is cut for brewity).
See how some of the memory “chunks” are zero in size. These chunks are the ones which have been chosen as victims for destruction (or for not-even-creation) when space was needed or PGA areas. If you look into pmap output for any server processes you will still see this memory mapped into the address space, but it’s not just used because Oracle knows this memory is really freed.

$ ls -l /dev/shm
total 818840
-rw-r----- 1 oracle dba 16777216 Aug 20 23:29 ora_LIN11G_1900546_0
-rw-r----- 1 oracle dba        0 Aug 20 23:29 ora_LIN11G_1933315_0
-rw-r----- 1 oracle dba        0 Aug 20 23:29 ora_LIN11G_1933315_1
-rw-r----- 1 oracle dba        0 Aug 20 23:29 ora_LIN11G_1933315_10
-rw-r----- 1 oracle dba        0 Aug 20 23:29 ora_LIN11G_1933315_11
-rw-r----- 1 oracle dba        0 Aug 20 23:29 ora_LIN11G_1933315_12
-rw-r----- 1 oracle dba        0 Aug 20 23:29 ora_LIN11G_1933315_13
-rw-r----- 1 oracle dba        0 Aug 20 23:29 ora_LIN11G_1933315_14
-rw-r----- 1 oracle dba 16777216 Aug 20 23:37 ora_LIN11G_1933315_15
-rw-r----- 1 oracle dba 16777216 Aug 20 23:37 ora_LIN11G_1933315_16
-rw-r----- 1 oracle dba 16777216 Aug 20 23:37 ora_LIN11G_1933315_17
-rw-r----- 1 oracle dba 16777216 Aug 20 23:37 ora_LIN11G_1933315_18
-rw-r----- 1 oracle dba 16777216 Aug 20 23:37 ora_LIN11G_1933315_19
-rw-r----- 1 oracle dba        0 Aug 20 23:29 ora_LIN11G_1933315_2
-rw-r----- 1 oracle dba 16777216 Aug 20 23:37 ora_LIN11G_1933315_20
-rw-r----- 1 oracle dba 16777216 Aug 20 23:37 ora_LIN11G_1933315_21
-rw-r----- 1 oracle dba 16777216 Aug 20 23:37 ora_LIN11G_1933315_22
-rw-r----- 1 oracle dba 16777216 Aug 20 23:37 ora_LIN11G_1933315_23
-rw-r----- 1 oracle dba 16777216 Aug 20 23:37 ora_LIN11G_1933315_24
...

Another listing, taken after issuing “alter system set pga_aggregate_target=600M”
You can see from below that most of the /dev/shm files which were 16MB in previous listing, have also been zeroed out.

$ ls -l /dev/shm
total 408740
-rw-r----- 1 oracle dba 16777216 Aug 20 23:29 ora_LIN11G_1900546_0
-rw-r----- 1 oracle dba        0 Aug 20 23:29 ora_LIN11G_1933315_0
-rw-r----- 1 oracle dba        0 Aug 20 23:29 ora_LIN11G_1933315_1
-rw-r----- 1 oracle dba        0 Aug 20 23:29 ora_LIN11G_1933315_10
-rw-r----- 1 oracle dba        0 Aug 20 23:29 ora_LIN11G_1933315_11
-rw-r----- 1 oracle dba        0 Aug 20 23:29 ora_LIN11G_1933315_12
-rw-r----- 1 oracle dba        0 Aug 20 23:29 ora_LIN11G_1933315_13
-rw-r----- 1 oracle dba        0 Aug 20 23:29 ora_LIN11G_1933315_14
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_15
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_16
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_17
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_18
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_19
-rw-r----- 1 oracle dba        0 Aug 20 23:29 ora_LIN11G_1933315_2
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_20
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_21
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_22
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_23
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_24
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_25
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_26
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_27
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_28
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_29
-rw-r----- 1 oracle dba        0 Aug 20 23:29 ora_LIN11G_1933315_3
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_30
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_31
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_32
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_33
-rw-r----- 1 oracle dba        0 Aug 20 23:46 ora_LIN11G_1933315_34
-rw-r----- 1 oracle dba 16777216 Aug 20 23:29 ora_LIN11G_1933315_35
-rw-r----- 1 oracle dba 16777216 Aug 20 23:29 ora_LIN11G_1933315_36
-rw-r----- 1 oracle dba 16777216 Aug 20 23:29 ora_LIN11G_1933315_37
-rw-r----- 1 oracle dba 16777216 Aug 20 23:29 ora_LIN11G_1933315_38
...

So, in Linux (tested on OEL5) Oracle 11g is using a new mechanism for managing shared memory. Well this mechanism itself isn’t that new, but it’s unconventional considering the long history of Unix SysV SHM segment use for Oracle SGAs.

---

Does this all matter? Yes
Why does it matter? There are few administrative differences compared to the conventional implementation.First of all, ipcs -m doesn’t show the full size of these segments anymore. You need to list /dev/shm contents for that.Also, pmap always reports that the memory is mapped (because it is) even though it doesn not have physical backing storage on tmpfs on /dev/shm device.

One more important note is that if you have not configured your tmpfs size on /dev/shm properly, then Oracle fails to allocate new POSIX-style shared memory and will not allow you to use MEMORY_TARGET parameters (startup without those parameters will however succeed).

The error message you likely get looks like that:

SQL> ORA-00845: MEMORY_TARGET not supported on this system

And is accompanied by following entry in alert.log:

Sat Aug 18 12:37:31 2007
Starting ORACLE instance (normal)
WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 847249408 bytes. /dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 0 and used is 0 bytes.
memory_target needs larger /dev/shm

So you need to configure large enough tmpfs on /dev/shm device to fit all memory up to MEMORY_MAX_TARGET.

The configuration works roughly like that:
(run as root):

# umount tmpfs
# mount -t tmpfs shmfs -o size=1300m /dev/shm
# df -k /dev/shm
Filesystem           1K-blocks      Used Available Use% Mounted on
shmfs                  1331200         0   1331200   0% /dev/shm

This one allows /dev/shm to grow roughly up to 1300MB, allowing you to use MEMORY_MAX_TARGET (or MEMORY_TARGET) set to 1300MB. The Linux-specific Oracle 11g documentation has more details how to configure this.

---

Note that after resetting various parameters I played with I realized that finally Oracle has implemented the human-friendly way for resetting parameters in SPFILE:

Sys@Lin11g> alter system reset pga_aggregate_target;

System altered.

Sys@Lin11g> alter system reset sga_target;

System altered.

…no scope=spfile sid=’*’ is needed. This resets the parameter in spfile only, the values in memory persist.