Leandro Abite
Dozens of DBA and SysAdmin do not know how to calculate the kernel’s parameters correctly and most of times they use the default value as indicated in Oracle Documentation, I would say that most of the DBA do not even know the meaning of those parameters.
In this article I will describe the meaning of each OS paramater and how to calculate/sizing them.
Shared Memory
Shared memory allows processes to access common structures and data by placing them in shared memory segment. It is the fastest form of interprocess Communication (IPC) available since no kernel involvement occurs when data is passed between the processes. In fact, data does not need to be copied between the processes.
Oracle uses shared memory segments for Shared Global Area (SGA) which is an area of memory that is shared by Oracle processes. The size of the SGA has a significant impact to Oracle’s performance since it holds database buffer cache and much more.
Basically we use three parameters to scale shared memory:
This parameter defines the maximum size in bytes of a single shared memory segment that a linux process can allocate.
This parameter sets the system wide maximum number of shared memory segments. Oracle recommends SHMMNI to be at least 4096.
This parameter sets the total amount of shared memory pages that can be used system wide.
Memory Sizing
The steps bellow help to scale the parameters SHMMAX and SHMALL:
Obtain the total memory from the system
Convert the value of $mem to bytes
Get the Hugepagesize from /proc/meminfo
huge=$(grep Hugepagesize /proc/meminfo|awk '')
Calculate what 75% of the total memory on the system for SHMMAX
Divide the SHMMAX value by the Hugepagesize to get SHMALL
Set the SHMMAX value in the /etc/sysctl.conf file
echo "kernel.shmmax = $max" >> /etc/sysctl.conf
Set the SHMALL value in the /etc/sysctl.conf file
echo "kernel.shmall = $all" >> /etc/sysctl.conf
Setting the maximum number of shared memory segments with SHMMNI.
echo "kernel.shmmni=4096" >> /etc/sysctl.conf
Memory Tuning
The linux kernel tunable parameter vm.swappiness (/proc/sys/vm/swappiness) can be used to define how aggressively memory pages are swapped to disk.
Linux moves memory pages that have not been accessed for some time to the swap space even if there is enough free memory available. By changing the percentage in /proc/sys/vm/swappiness you can control the swapping behavior, depending on the system configuration.
A high swappiness value means that the kernel will be more apt to unmap mapped pages. A low swappiness value means the opposite, the kernel will be less apt to unmap mapped pages. In other words, the higher the vm.swappiness value, the more the system will swap.
Swapping for Oracle is too bad
Maximum percentage of active memory that can have dirty pages
vm.dirty_background_ratio=3
Maximum percentage of total memory that can have dirty pages
How long data can be in page cache before being expired (hundreths of a second)
vm.dirty_expire_centisecs=500
How often pdflush is activated to clean dirty pages (hundreths of asecond)
vm.dirty_writeback_centisecs=100
Setting Hugepages
Without HugePages, the memory of the SGA is divided into 4K pages, which have to be managed by the Linux kernel. Using HugePages, the page size can be increased to anything between 2MB and 256MB, thereby reducing the total number of pages to be managed by the kernel and therefore reducing the amount of memory required to hold the page table in memory. In addition to these changes, the memory associated with HugePages can not be swapped out, which forces the SGA to stay memory resident.
Setup the oracle user to be able to use hugepages in /etc/sysctl.conf
vm.hugetlb_shm_group=`id -g oracle`
Calculating the recommended number of HugePages, use the following formula:
(SGA+PGA+(20KB * # of Oracle processes running)) / 2MB
(20GB SGA + 10GB PGA + (20KB * 1,000 Oracle processes)) / 2MB = 15369
Include the parameter vm.nr_hugepages in /etc/sysctl.conf
Setting Semaphores
Semaphores are counters which are used to provide synchronization between processes for shared resources like shared memory. Oracle’s process concur in order to access the SGA, which is allocated in a shared memory area, thereby the number of semaphores must be higher than the number of oracle’s process parameter.
The number of semaphores per set can be defined through the kernel parameter SEMMSL. In /etc/sysctl.conf the entry for semphores is:
kernel.sem="250 32000 100 142"
The first value, SEMMSL, is the maximum number of semaphores per semaphore set
The second value, SEMMNS, defines the total number of semaphores for the system
The third value, SEMOPM, defines the maximum number of semaphore operations per semaphore call
The last value, SEMMNI, defines the number of entire semaphore sets for the system
Network tuning
Receive socket buffer size
net.core.rmem_default=262144 net.core.rmem_max=4194304
net.core.wmem_default=262144 net.core.wmem_max=4194304
net.ipv4.tcp_rmem=4096 262144 4194304 net.ipv4.tcp_wmem=4096 262144 4194304
How often to send keep alive packets when a connection is unused
net.ipv4.tcp_keepalive_time=30
How long the kernel waits in between probes
net.ipv4.tcp_keepalive_intvl=60
How many probes are sent before a connection is considered broken
net.ipv4.tcp_keepalive_probes=9
How many times to retry before killing the connection
How many times to retry transmitting the syn packet
ORACLE DBA
SHMMAX and SHMALL are two key shared memory parameters that directly impact’s the way by which Oracle creates an SGA. Shared memory is nothing but part of Unix IPC System (Inter Process Communication) maintained by kernel where multiple processes share a single chunk of memory to communicate with each other.
While trying to create an SGA during a database startup, Oracle chooses from one of the 3 memory management models a) one-segment or b) contiguous-multi segment or c) non-contiguous multi segment. Adoption of any of these models is dependent on the size of SGA and values defined for the shared memory parameters in the linux kernel, most importantly SHMMAX.
So what are these parameters — SHMMAX and SHMALL?
SHMMAX is the maximum size of a single shared memory segment set in “bytes”.
The key thing to note here is the value of SHMMAX is set in «bytes» but the value of SHMMALL is set in «pages«.
As SHMALL is the total size of Shard Memory Segments System wide, it should always be less than the Physical Memory on the System and should also be less than sum of SGA’s of all the oracle databases on the server. Once this value (sum of SGA’s) hit the limit, i.e. the value of shmall, then any attempt to start a new database (or even an existing database with a resized SGA) will result in an “out of memory” error (below). This is because there won’t be any more shared memory segments that Linux can allocate for SGA.
So above can happen for two reasons. Either the value of shmall is not set to an optimal value or you have reached the threshold on this server.
Setting the value for SHMALL to optimal is straight forward. All you want to know is how much “Physical Memory” (excluding Cache/Swap) you have on the system and how much of it should be set aside for Linux Kernel and to be dedicated to Oracle Databases.
For e.g. Let say the Physical Memory of a system is 6GB, out of which you want to set aside 1GB for Linux Kernel for OS Operations and dedicate the rest of 5GB to Oracle Databases. Then here’s how you will get the value for SHMALL.
Convert this 5GB to bytes and divide by page size. Remember SHMALL should be set in “pages” not “bytes”.
Determine Page Size first, can be done in two ways. In my case it’s 4096 and that’s the recommended and default in most cases which you can keep the same.
Oracle makes use of one of the 3 memory management models to create the SGA during database startup and it does this in following sequence. First Oracle attempts to use the one-segment model and if this fails, it proceeds with the next one which’s the contiguous multi-segment model and if that fails too, it goes with the last option which is the non-contiguous multi-segment model.
So during startup it looks for shmmax parameter and compares it with the initialization parameter *.sga_target. If shmmax > *.sga_target, then oracle goes with one-segment model approach where the entire SGA is created within a single shared memory segment.
But the above attempt (one-segment) fails if SGA size otherwise *.sga_target > shmmax, then Oracle proceeds with the 2 nd option – contiguous multi-segment model. Contiguous allocations, as the name indicates are a set of shared memory segments which are contiguous within the memory and if it can find such a set of segments then entire SGA is created to fit in within this set.
But if cannot find a set of contiguous allocations then last of the 3 option’s is chosen – non-contiguous multi-segment allocation and in this Oracle has to grab the free memory segments fragmented between used spaces.
So let’s say if you know the max size of SGA of any database on the server stays below 1GB, you can set shmmax to 1 GB. But say if you have SGA sizes for different databases spread between 512MB to 2GB, then set shmmax to 2Gigs and so on.