Oracle 11g and Automatic Memory Management (AMM)

I have been very busy for the last few months and haven't posted much on this blog but I thought I would share some notes on recent experiences installing and upgrading to Oracle 11gR1 and 11gR2 on Decipher,LLC projects. I plan to make this a series of postings.



Carrying forward the theme of improving and automating memory management that started with Oracle 9i, Oracle has included Automatic Memory Management (AMM) in 11g.
The purpose of AMM is to provide dynamic management of the PGA and SGA. The method by which this is done is to allocate one large chunk of memory at database configuration using two new initialization parameters: MEMORY_TARGET and MEMORY_MAX_TARGET. From Oracle 11g Documention:

"you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET). The instance then tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA). Because the target memory initialization parameter is dynamic, you can change the target memory size at any time without restarting the database. The maximum memory size serves as an upper limit so that you cannot accidentally set the target memory size too high, and so that enough memory is set aside for the Oracle Database instance in case you do want to increase total instance memory in the future. Because certain SGA components either cannot easily shrink or must remain at a minimum size, the instance also prevents you from setting the target memory size too low."

Before you configure your Oracle 11g instance, you should first make sure that you have enough shared memory on your server. On a Linux server, you do this by issuing:

# df -k /dev/shm
which will return the number of blocks in your shared memory filesystem, the number used, the usage percent, and the device on which it is mounted (/dev/shm).

# umount tmpfs
# mount -t tmpfs shmfs -o size=1200m /dev/shm
will adjust the shared memory filesystem size to that required size.

To make this change permanent you need to add the adjustment to the fstab file. To do this just edit the file /etc/fstab and add the line:

none /dev/shm tmpfs size=3000m 0 0
in this case, we set the size to 3GB

The next step is to optimally set your MEMORY_TARGET and MEMORY_MAX_TARGET initialization parameters.

The view V$MEMORY_TARGET_ADVICE provides tuning advice for the MEMORY_TARGET initialization parameter:

SQL> select * from v$memory_target_advice order by memory_size;

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
----------- ------------------ ------------ ------------------- ----------
180 .5 458 1.344 0
270 .75 367 1.0761 0
360 1 341 1 0
450 1.25 335 .9817 0
540 1.5 335 .9817 0
630 1.75 335 .9817 0
720 2 335 .9817 0

While installing Oracle 11g or using DBCA to create a new database, you have the option of using AMM or not.

The main point of this post is to inform you that Oracle 11g will require quite a bit more shared memory if you choose to use the new Automatic Memory Management feature.