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.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/YAN_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)