Oracle 11g AMM: MEMORY_TARGET, MEMORY_MAX_TARGET and /dev/shm

文章来源： http://www.ora600.be/news/oracle-11g-memorytarget-memorymaxtarget-and-devshm

Original article Author: Oracle 48 
I knew in Oracle 11g with Automatic Memory Management (AMM), the database was able to give unused memory back to the OS, controlled by MEMORY_TARGET and MEMORY_MAX_TARGET.


This mechanism is present in 11g since day 1 (in 2007), but I never actually played with it.


MEMORY_TARGET and MEMORY_MAX_TARGET 
The Oracle documents state the following:


MEMORY_TARGET specifies the Oracle system-wide usable memory.
MEMORY_MAX_TARGET (…) decide on a maximum amount of memory that you would want to allocate to the database for the foreseeable future.


So my guess is, MEMORY_MAX_TARGET (static) is the maximum you can set MEMORY_TARGET (dynamic) to. A couple of days ago, I wanted to experiment a bit with these memory settings.


My Oracle Enterprise Linux (5.5) machine was set for MEMORY_MAX_TARGET=512M and MEMORY_TARGET=256M, but after starting the database, it showed the following:


SQL> startup pfile=init.ora ORACLE instance started. Total System Global Area 534462464 bytes Fixed Size 2215064 bytes Variable Size 473957224 bytes Database Buffers 50331648 bytes Redo Buffers 7958528 bytes Database mounted. Database opened. 
Total SGA, 534462464 bytes? That’s about 510M, certainly not what I had specified for MEMORY_TARGET…!?


Checking SGA in Enterprise Manager (yes, I use it sometimes), it showed 256M allocated for MEMORY_TARGET, containing SGA and PGA:




AMM SGA/PGA sizes


SGA was using 152M and PGA took the rest:




SGA size and contents


Also running ‘select sum(bytes) from v$sgastat’ showed me the SGA is taking 152M.


It seems ‘show sga’ shows the MEMORY_MAX_TARGET and ‘Variable Size’ included the memory it will not use.


Automatic Memory Memory Advisor 
Oracle keeps track of memory usage and is able to advise about the MEMORY_TARGET size:




AMM Advice


Clicking the graph will update the MEMORY_TARGET parameter.


One can also query V$MEMORY_TARGET_ADVICE for this information:


MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION ----------- ------------------ ------------ ------------------- ----------         256                  1          501                   1          0         320               1.25          501                   1          0         384                1.5          501               .9995          0         448               1.75          501               .9994          0         512                  2          501               .9994          0 What is /dev/shm? 
It is an in-memory mounted file system (tmpfs) and is very fast, but non-persistent when Linux is rebooted.


In Oracle 11g, it is used to hold SGA memory by storing the SGA structures in files with the same granule size. This granule size comes in 4M and 16M flavours, depending the MEMORY_MAX_TARGET smaller or larger than 1G.


When these MEMORY_TARGET and MEMORY_MAX_TARGET parameters are set, oracle will create as much as =(MEMORY_MAX_TARGET / granule size) files. For instance, when MEMORY_MAX_TARGET set to 512M, it will create 512/4 = 128 files (actually 129, the sneaky…).


The output of ‘ls -la /dev/shm’, will show you that not all the 128 files are taking the 4M of space:


shm> ls -la total 151780 drwxrwxrwt 2 root root 2620 Sep 10 11:13 . drwxr-xr-x 12 root root 3880 Sep 10 08:47 .. -rw-rw---- 1 oracle oinstall 4194304 Sep 10 14:17 ora_ianh_3768323_0 -rw-rw---- 1 oracle oinstall 4194304 Sep 10 14:11 ora_ianh_3768323_1 -rw-rw---- 1 oracle oinstall 0 Sep 10 11:13 ora_ianh_3768323_10 -rw-rw---- 1 oracle oinstall 4194304 Sep 10 14:17 ora_ianh_3768323_100 (...) -rw-rw---- 1 oracle oinstall 4194304 Sep 10 14:17 ora_ianh_3768323_127 -rw-rw---- 1 oracle oinstall 4194304 Sep 10 11:13 ora_ianh_3768323_128 -rw-rw---- 1 oracle oinstall 0 Sep 10 11:13 ora_ianh_3768323_13 -rw-rw---- 1 oracle oinstall 0 Sep 10 11:13 ora_ianh_3768323_14 -rw-rw---- 1 oracle oinstall 0 Sep 10 11:13 ora_ianh_3768323_15 -rw-rw---- 1 oracle oinstall 0 Sep 10 11:13 ora_ianh_3768323_16 -rw-rw---- 1 oracle oinstall 0 Sep 10 11:13 ora_ianh_3768323_17 -rw-rw---- 1 oracle oinstall 0 Sep 10 11:13 ora_ianh_3768323_18 -rw-rw---- 1 oracle oinstall 0 Sep 10 11:13 ora_ianh_3768323_19 -rw-rw---- 1 oracle oinstall 4194304 Sep 10 11:13 ora_ianh_3768323_2 
Now this is the trick Oracle is using. When you add up all the files that do take 4M of space, it will never take more space than MEMORY_TARGET. Therefor, Oracle does not allocate more memory than the MEMORY_TARGET and the sum of these files might even be smaller than MEMORY_TARGET.


When you look at the SGA memory size using ‘select ceil(sum(bytes)/(1024*1024*4)) from v$sgastat’, you will see it is near the sum of the files in /dev/shm (again, plus one…).


0 bytes in memory 
When a file in /dev/shm is 0 bytes, it does not use memory. That memory is ‘free’ to other applications. Now this is Oracle’s implementation of releasing memory back to the Linux OS, by cleaning up one or more of these in-memory files (will it do a ‘cat /dev/null > ora_sid_number_id’ ?).


Funny thing is, PGA is not stored in shared memory, because this is private memory. MEMORY_MAX_TARGET (used for SGA and PGA) is ‘allocated’ in /dev/shm, but PGA is not stored in /dev/shm. This means, when memory for PGA is allocated (and/or pga_aggregate_target is set), not all files in /dev/shm will get used!


Increase /dev/shm 
If you increase the MEMORY_MAX_TARGET above the available /dev/shm space (df -h), you will receive:


ORA-00845: MEMORY_TARGET not supported on this system


If you have enough memory on your Linux machine, but /dev/shm is mounted to small by default, one can increase this amount of memory by changing /etc/fstab for permanent changes. The default is half of your physical RAM without swap.


For temporary changes to at least start the database, execute the following (change the 1500m to your environment):


> umount tmpfs > mount -t tmpfs shmfs -o size=1500m /dev/shm 152M boundary 
When I was playing around with these settings, it seems 152M is an initial minimal memory target.


If you start oracle with a pfile setting of lower than 152M, it fails to start and you will get the following message:


ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 152M


Remarks 
When I changed MEMORY_TARGET to 152M in my pfile, after the bounce the PGA was set to Manual Mode. 
Oracle will devide SGA/PGA as 60/40% when enough memory is available. 
The PGA_AGGREGATE_TARGET and SGA_TARGET are not ignored, but act as a minimum when set. 
When SGA_MAX_SIZE is set, it will act as a maximum; when it’s not set it will show the MEMORY_MAX_TARGET value. 
/dev/shm must mounted for at least 384M bytes (You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 402653184 bytes). 
Conclusion 
With Automatic Memory Management, one can set the upper limit of the total SGA and PGA to use. It is using an in-memory file structure, so it can give back unused memory to the Linux OS, unlike 10g, setting SGA_MAX_TARGET will just use all the memory specified.


On the other hand, when problems arise, one still needs to dive into the memory structures and tune. The ‘automatic’ feature added is memory distribution between SGA and PGA, and Oracle and OS.


I would also like to thank my colleague joords for sharing his thoughts about this feature.


V$MEMORY_TARGET_ADVICE