# Oracle 11g internals part 1: Automatic Memory Management

147人阅读 评论(0)

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.

NB! After a 1.5 year break, this year’s only Advanced Oracle Troubleshooting training class (updated with Oracle 12c content) takes place on 16-20 November & 14-18 December 2015, so sign up now if you plan to attend this year!

0
0

【直播】机器学习&数据挖掘7周实训--韦玮
【套餐】系统集成项目管理工程师顺利通关--徐朋
【直播】3小时掌握Docker最佳实战-徐西宁
【套餐】机器学习系列套餐（算法+实战）--唐宇迪
【直播】计算机视觉原理及实战--屈教授
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之矩阵--黄博士
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之凸优化--马博士
【套餐】Javascript 设计模式实战--曾亮

* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
个人资料
• 访问：123047次
• 积分：2575
• 等级：
• 排名：第14228名
• 原创：99篇
• 转载：244篇
• 译文：0篇
• 评论：0条
阅读排行