Oracle SGA memory management on Linux

1. SGA auto sizing is not that dynamic. At startup, the oracle processes allocate memory up to the sga_max_size. Oracle keeps the unused sga memory under “Free SGA Memory Available”. For example, if we set sga_max_size = 600M and sga_target = 500M, then query v$sgainfo and we find that “Free SGA Memory Available” is 100M. I could not find a reason not to set sga_target equals to sga_max_size. (Well, only for very special needs.)
   
2. It is hard to know exactly how much memory is allocated to an Oracle instance at OS level. On Linux, you can use “ps aux” to find out how much memory is allocated to an oracle process in bytes by VSZ column. VSZ stands for “virtual memory size” and it quite misleading. In Linux, virtual memory is a memory map that can be point to physical memory, or a swap disk space, or a memory to be allocated. So it is more like a total memory allocated for the process. Secondly, the VSZ includes the memory dedicated to the process and shared memory with other processes as well. This is why the sum of VSZ for all processes from an Oracle instance is much larger that SGA and PGA combined because shared memory is counted multiple times. To find out what memory is shared and what is private, you can use “pmap –d ”. A sample output is as following:
   

   Address	Kbytes	Mode	Offset	Mapping
   ….
   0000003b27e13000	8	rw---	0000003b27e13000	[ anon ]
   0000003b2c500000	80	r-x--	0000000000000000	libnsl-2.3.4.so
   0000003b2c514000	1020	-----	0000000000014000	libnsl-2.3.4.so
   0000003b2c613000	8	rw---	0000000000013000	libnsl-2.3.4.so
   0000003b2c615000	8	rw---	0000003b2c615000	[ anon ]
   0000007fbfffa000	24	rwx--	0000007fbfffa000	[ stack ]
   ffffffffff600000	8192	-----	0000000000000000	[ anon ]

   
   mapped: 772456K writeable/private: 6824K shared: 620548K
   As it shows, pmap lists memory address, memory siz of all memory segments for a process. It also shows private memory and shared memory in last line. It is possible to write a program to find the total memory allocation for a set of processes based the memory address and size of pmap output from every single process in the set.
   
3. Not all SGA memory is in physical memory. Back to “ps aux” command and “RSS” column is for “resident set size” in KB. In English, it is the physical memory the process used, including shared and not shared. The following is the output of pmon process of an oracle instance:
   
   

   USER	PID	VSZ	RSS	COMMAND
   oracle	10108	764264	17696	ora_pmon_orcl

   
   The data indicates that only 2% of the memory is actually physical memory. Because the majority of memory for an oracle process is shared, the percentage of physical memory out of total memory for other oracle processes is similar. I still do not have a way to know what Oracle memory structure is on physical memory or what percentage of it is physical memory. Normally, it is not a problem since Linux OS would move the active memory into physical memory. But in some case, for example, server is doing swapping, virtual machine is used, or high performance system that even first time access of a stale memory page needs to be fast, DBA may needs to pin all Oracle memory into not swappable physical memory. If certain Oracle memory structure is not in physical memory, such as library cache, the database is unusable because of performance.
   
4. Pin SGA into physical memory. Oracle init parameter lock_sga can be set to true and set the ulimit parameter memlock to greater than sga_max_size. Otherwise, the instance failed to start with ORA-27102: out of memory. Let’s check the process again and interestingly, the physical memory allocated to oracle process is still very little.
   
   

   USER	PID	VSZ	RSS	COMMAND
   oracle	20420	764264	17696	ora_pmon_orcl

   
   init parameter lock_sga is OS dependent and looks like Linux is not supporting lock_sga to pin SGA into physical memory. Even the status of the shared memory is locked by “ipcs –m”. Next, let us try to set pre_page_sga = true. Run “ps aux” and see how much physical memory is used
   
   

   USER	PID	VSZ	RSS	COMMAND
   oracle	12278	764264	634348	ora_pmon_orcl

   
   The RSS is about 600M so sga is in physical memory now. However, sga in physical memory can be paged out by OS as needed. Again, the memory locking is not working in Linux even the flag is set.