Oracle Internals: Kernel parameters

Introduction:

As a Oracle DBA, when we install Oracle binaries; we have to take care of a number of prerequisites. One of such prerequisite is to set Kernel Parameters to the Oracle recommended values.

However, most of the time we are not bothered about; what actually these kernel parameters do and why is the requirement to reset it to the Oracle recommended values.

In today’s post, I would try to provide a brief overview of the Kernel parameters related to the Oracle installation and the role they play with respect to the Oracle database.

Kernel Parameters related to Oracle installation:

Based on functionality of the parameters, I have segreggated the parameters into the following list

1. Semaphore  Parameters:

SEMMSL
SEMMNS
SEMOPMN 
SEMMNI 
SEMVMX 

2. Shared Memory Parameters:

SHMMAX
SHMMIN
SHMMNI
SHMALL
SHMSEG
SHMMNS

3. File Handling Parameters

FILE_MAX

4. Port Range Parameters

IP_LOCAL_PORT_RANGE

5. Socket Size Parameters

RMEM_DEFAULT
RMEM_MAX
WMEM_DEFAULT
WMEM_MAX

 

Background:

To understand the functioning of the parameters, we have to first understand two important aspects of an Operating System called Shared Memory and Semaphore.

Shared Memory: A shared memory is a region or part of the physical memory (RAM), that can be accessed or shared by multiple processes on the Operating System (server).

Oracle uses Shared Memory for SGA (Shared Global Area) allocation.

Semaphore: A Semaphore is  a controlling signal or flag that can be either turned On or Off by a OS process. Semaphore controlls the access to Shared OS resources. If a signal is already turned On , process which tries to turned On the same signal has to wait and sleep until it is turned Off. On awakening, the process can re-attempt to turn On the signal and can eventually succeed or wait depending on the state of the signal.  Each OS process is associated with a Semaphore.

Oracle uses Semaphores to control concurrent access to SGA since it can be shared (writable) by the attached database processes.

 

Explanation and Impact on Oracle Database:

Shared Memory:

SHMMAX: Specifies the maximum allowable size of a single shared memory segment  (in bytes)
SHMMIN: Specifies the minimum size of a single shared memory segment (in bytes)
SHMMNI: Specifies the maximum number of shared memory segments on system (in numbers)
SHMALL: Specifies the sum of all shared memory segments in the system (in pages)
SHMSEG: Specifies the maximum number of shared memory segments that can be attached (i.e. used) by a single process.
SHMMNS: Specifies the amount of shared memory that can be allocated system-wide.

 

SHMMAX and SHMALL are the two important kernel parameters which affects a Oracle Database instance.

When a Oracle instance starts up, Shared Memory (SGA) is allocated to the instance from the available System Shared Memory segments. Oracle can use any of the forth mentioned methods for the Shared Memory allocation to the Instance.

By default, Oracle  uses one-segment allocation method and checks if a single Shared Memory segment can fit the SGA requirement by checking the size of SHMMAX kernel parameter and comparing it with SGA_TARGET. If “SHMMAX> SGA_TARGET”, Oracle uses the one-segment method for SGA allocation.

If one-segment allocation is not feasible, Oracle goes for the contiguous multi-segment allocation, where it tries to allocate contiguous Shared Memory segments to fulfill SGA requirement.

If  Oracle can’t find a contiguous set of segments, it finally  uses the non contiguous multi-segment allocation, where tries to allocate scattered Shared Memory segments to fulfill SGA requirement.

If sufficient Shared Memory segment (s) is not available, Oracle will throw “Out Of Memory” errors like below

ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device

While using oracle, it is recommended to allocate half of the size of RAM (.5 * RAM Size) to SHMMAX i.e.

SHMMAX= Size of Physical Memory (RAM) in bytes/ 2

For example:

If the available RAM on the server is 30 GB, it is recommended to set shmmax to 15 GB i.e 16106127360 bytes

vi /etc/sysctl.conf
kernel.shmmax=16106127360

 

SHMALL should be set as the Sum of SGA from all the Oracle Instances on the system devided by OS page size i.e.

SHMALL = SUM(all SGA) in bytes / OS page_size

For example:

If the size of the SGA from all of the Oracle Instances on a particular server 20 GB, then shmall should be configured as follows

a. Find the OS PAGE_SIZE

[oracle@labserver ~]$  getconf PAGE_SIZE
4096

b. Determine shmall using above mentioned formula

shmall = (20*1024*1024*1024)/4096=5242880 (pages)
vi /etc/sysctl.conf
kernel.shmall = 5242880

We can query the details of Shared Memory segments as follows

[oracle@labserver ~]$ ipcs -m -l
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 67108864
max total shared memory (kbytes) = 17179869184
min seg size (bytes) = 1

The status of allocated shared memory segments can be queried as follows

[oracle@labserver ~]$ ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x00000000 3506176    root      644        80         2
0x00000000 3538945    root      644        16384      2
0x00000000 3571714    root      644        280        2
0x54917120 8126468    oracle    640        4096       0
0x00000000 8159237    oracle    640        4096       0
0x5f600208 15106054   oracle    640        14680064   100
0x00000000 15138823   oracle    640        511705088  50

 

Where bytes: Size of the allocated Shared Memory segment and
nattache: Number of processes attached to the respective Shared Memory segment

 

Note: In any case the Shared Memory Parameters should not exceed the physical memory (RAM) size. Setting the values larger than available RAM may lead to errors.

 

Semaphores:

SEMMSL  : Specifies the maximum number of semaphores in a semaphore set
SEMMNS  : Specifies the maximum number of semaphores available on the system.
SEMOPMN : Specifies the maximum number of operations in a single semaphore call
SEMMNI  : Specifies the maximum number of semaphore sets available in the system
SEMVMX  : specifies the maximum value a semaphore set can have

 

SEMMSL and  SEMMNS are the two important Semaphore parameter that affect the Oracle database.

As we know, Semaphores are OS signals that control shared access; each database process must be associated with a Semaphore.

When a Oracle instance starts up Oracle allocates semaphores to all the PROCESSES (parameter file values) associated with that instance. Semaphore gets allocated in sets rather than as an individual one.

We can allocate all the semaphores to the database processes in single or multiple sets. If SEMMSL=PROCESSES, then Semaphores can be allocated in a single set to that particular database instance.

If the server is hosting multiple Oracle Instances, it is recommended to set the value of SEMMSL to the lowest value of PROCESSES from all the Instances. In this way, we can prevent the Semaphore being in the unused status.

For example:

If the server has 3 databases namely LABPA, LABPABR and LABCP; each having number of processes as 100 , 200 and 300 respectively.

If we set the value of SEMMSL=300, then each of the Database Instance would be allocated 300 Semaphores (as Semaphores get allocated in sets).

However, LABPA and LABPABR would be utilizing only 100 and 200 Semaphores respectively.

Therefore, we will have 200 and 100 unused Semaphores from LABPA and LABPABR instances respectively. These unused Semaphores can not be allocated to other processes.

This is why, it is recommended to set the value of SEMMSL to the lowest value of PROCESSES among all the Oracle Instances on a particular server.

In our example, we must set SEMMSL=100. In this case, it will allocate 1 (100 Semaphores), 2(200 Semaphores) and 3(300 Semaphores) sets of Semaphores to LABPA, LABPABR and LABCP Oracle Instances respectively.

The maximum number of semaphores that can be allocated will be the lesser than (SEMMSL*SEMMNI) or SEMMNS.

Current Semaphore configuration can be checked as follows

[oracle@labserver ~]$ ipcs -s -l

------ Semaphore Limits --------
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 100
semaphore max value = 32767

Current allocation of Semaphores can be queried as

[oracle@labserver ~]$ ipcs -s

------ Semaphore Arrays --------
key        semid      owner      perms      nsems
0x57e20798 131073     oracle    640        124
0x51e15128 262146     oracle    640        152
0x51e15129 294915     oracle    640        152
0x51e1512a 327684     oracle    640        152

Where nsems : Number of Semaphores allocated in the respective set

Other Kernel Parameters:

file-max : Specifies the maximum of number  of file handles available on the system. This number indicates the number of files that can be opened on the server at any given time

ip_local_port_range : Specifies the range of local ports on the system that can be used by TCP and UDP outgoing connections.

While specifying the IP range, you need skip the range of the ports that would be used as a various Listener ports like HTTP, FTP, Database Listener.

rmem_default: Specifies default OS receive buffer size for all types of connections. This is the default size of a network data packet that the system will receive.

rmem_max: Specifies max OS receive buffer size for all types of connections. This is the maximum size of a network data packet that the system can receive

wmem_default: Specifies default OS send buffer size for all types of connections. This is the default size of a network data packet that the system will send.
wmem_max: Specifies max OS send buffer size for all types of connections. This is the maximum size of a network data packet that the system can send.

The rmem and wmem parameters can be tuned and increased to handle large file transfer between servers.

 

Note: To make kernel paraameter changes permanent, system reboot is required