Oracle Automatic Memory Management: real memory usage!

When using Automatic Memory Management for Oracle, it is sometimes difficult to monitor the memory usage and in particular to find the right tools to get the right information about currently allocated structures.

The instance which will be analyzed has been configured with AMM (Automatic Memory Management) on Oracle Enterprise Linux 6.1.
The current memory_target is set to 1 GB:

SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
memory_target                        big integer 1G

In order to use AMM, a tmps memory filesystem has been configured in the fstab with 3 GB.

If you are using another Linux (i. e. SLES), have a look at this post to configure your /dev/shm: https://www.dbi-services.com/index.php/blog/entry/configuration-of-tmpfs-on-sles-11-for-oracle-112-and-amm

oracle@srvora05:// [WSDBA1] cat /etc/fstab | grep shm
tmpfs                   /dev/shm                tmpfs   defaults,size=3G        0 0

While the instance is started, you will see that no “classical” shared memory has been allocated, at least not through the classical memory management mechanism (Oracle switched from SysV to POSIX-style shared memory). Therefore ipcs does not display any shared segment in the memory:

oracle@srvora05:/ORA-DBA-Essentials/030_Administration/CreateDatabase/ [WSDBA1] ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x00000000 294912     oracle     640        4096       0   
0x00000000 327681     oracle     640        4096       0   
0x073b35e4 360450     oracle     640        4096       0

To get the REAL FACTS about your current memory structure, first of all, refer to V$MEMORY_DYNAMIC_COMPONENTS:

select component , CURRENT_SIZE , USER_SPECIFIED_SIZE
from V$MEMORY_DYNAMIC_COMPONENTS
where CURRENT_SIZE > 0
/

COMPONENT                      CURRENT_SIZE USER_SPECIFIED_SIZE
------------------------------ ------------ -------------------
shared pool                       209715200                   0
large pool                          4194304                   0
java pool                           4194304                   0
streams pool                        4194304                   0
SGA Target                        700448768                   0
DEFAULT buffer cache              465567744                   0
PGA Target                        373293056                   0

7 rows selected.

We currently have the following distribution:

• 668 MB (700448768 bytes) allocated for the SGA (sga_target)
  • 200 MB for the shared pool
  • and 444 MB for the database buffer cache
  • the rest for the other SGA pools
• The PGA target has been sized by Oracle to 356 MB (373293056 bytes)
• The sum of these components corresponds to 1024 MB (1 GB), set through the memory_target

On the Operating System, the consumed space can be checked checked on the /dev/shm filesystem, it confirms the size of the SGA:

oracle@srvora05:// [WSDBA1] du -sh /dev/shm
668M    /dev/shm

The PGA is private to each server process connecting to the instance, therefore it is the sum of all background processes (check with top, or ps).
We can also check the currently allocated PGA through SQL (currently 160MB, less than half of the target):

select name , value from v$pgastat
where name = 'total PGA allocated'
/

NAME                           VALUE
------------------------- ----------
total PGA allocated        168211456

 
Be careful , “show sga” lies! The “Total System Global Area” considers the 668 MB of shared pool plus (!) the total amount of PGA. We have “variable size = current SGA + what could be stolen to the PGA”. Indeed, it considers that the variable size might grow up to 595592376 bytes, leading to a PGA reduction to 0! The information “Total System Global Area” must be interpreted with care!

SQL> show sga

Total System Global Area 1068937216 bytes
Fixed Size                  2235208 bytes
Variable Size             595592376 bytes
Database Buffers          465567744 bytes
Redo Buffers                5541888 bytes

I hope this posting has helped you getting useful information on Automatic Memory Management for Oracle (AMM) and interpreting it correctly.