Automatic Memory Management in Oracle 11g

Below to post is to verify my understanding on how shared memory is managed in AMM. Thanks to Tanel poder Post on AMM internals.

Current setting for MEMORY_TARGET parameters in DB, not all 50GB allocated.

SQL> sho parameter memory

 ------------------------------------ -------------------------------- -----
 hi_shared_memory_address integer 0
 memory_max_target big integer 50G
 memory_target big integer 50G
 shared_memory_address integer 0

SGA parameters set in DB

SQL> sho parameter sga

 lock_sga boolean FALSE
 pre_page_sga boolean FALSE
 sga_max_size big integer 50G
 sga_target big integer 20G

In AMM , oracle manages shared memory as files in shared memory filesystem (/dev/shm)

cd /dev/shm

$ ls -ltr *TEST* |more
 -rw-r----- 1 oracle dba 134217728 Apr 26 18:42 ora_TEST_131235851_0
 -rw-r----- 1 oracle dba 134217728 Apr 26 18:42 ora_TEST_131203082_369
 Each files is of size 128M

ls -ltr *TEST* | awk '$5==134217728' |wc -l

170*128M=21.25 GB

21.25GB shared memory currently allocated to database TEST,Initially it will allocate (verify in /dev/shm for the files) the memory set for sga_target , if not set will be set based on memory_target value.

Other files with 0 bytes are (MEMORY_TARGET- SGA_TARGET) ,these are memory files mapped to server process (size 0 bytes), but its not used, if space needed for pga , these files are freed and memory allocated to pga.

$ ls -ltr *TEST* | awk '$5==0' |more
 -rw-r----- 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_230
 -rw-r----- 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_229
 -rw-r----- 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_228
 -rw-r----- 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_227
 -rw-r----- 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_226
 -rw-r----- 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_225

No of empty files which can freed whenever memory needs to be allocated to pga.

$ ls -ltr *TEST* | awk '$5==0' |wc -l

231*128M=28.8GB  ( MEMORY_TARGET-Current SGA Size)

 -------------------- ------------ ---------- ---------- ------------------- ---------- ------------- --------- --------- ------------
 SGA Target 2.2683E+10 2.2012E+10 2.2683E+10 2.1475E+10 5 GROW DEFERRED 02-MAY-16 134217728
SQL> select to_char(2.2683E+10)/1024/1024/1024 from dual;


sga_target current size is 21Gb which matches to the size of all shared memory files created for the DB in /dev/shm.
If /dev/shm not defined or properly configured, then setting AMM at instance will throw error message ORA-00845: MEMORY_TARGET not supported on this system.

ASMM can be configured without /dev/shm.


During instance startup , oracle will check if /dev/shm has free space left for the value specified in Memory_max_target(though not all is initialized/allocated) otherwise we get ORA-00845.

If space is sufficient then the virtual address space (in /dev/shm) is mapped to the value of memory_target, but only value sga_target + small for pga is allocated (check /dev/shm files with non zero bytes size) is initialized, rest of the files are mapped but will have 0 bytes. Due to this we can see /dev/shm has more free space than (total tmps – memory_target) value.

If instance started (by the value in memory_target) initially ,later if sga_target grows and no space left on /dev/shm to accommodate until memory_target , then the instance terminates and have core dump file with below exceptions.

Exception [type: SIGBUS, Non-existent physical address] [ADDR:0x1EDA76000] [PC:0x6080DD4, __intel_memset()+2660] [flags: 0x0, count: 1] 







Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s