Oracle UNIX/Linux Kernel Parameters Oracle Tips by Burleson Consulting

OS kernel parameters

Oracle’s OS specific installation instructions provide guidelines for the OS configuration, but the settings for the OS parameters can make an enormous difference in Oracle performance.

Because Oracle runs on over 60 different operating systems from a mainframe to a Macintosh, it is impossible to cover every single platform.  However, the common configuration issues for UNIX and Microsoft Windows platforms will be presented.

Server Settings for Windows Servers

Windows servers for Oracle are relatively simple when compared to UNIX-based servers.  There are only a few major points to cover to ensure that the Windows server is optimized for an Oracle database.  The larger Windows servers (e.g. the UNISYS ES7000 servers) can have up to 32 CPUs and hundreds of gigabytes of RAM.  They can support dozens of Oracle instances, but many third party applications can hog server resources, causing Oracle performance issues.

Kernel setting for UNIX and Linux servers

In UNIX and Linux, there is much more flexibility in configuration and hundreds of kernel setting that can benefit database performance.  Table 14.1 lists some of the most common kernel parameters that influence Oracle:

For details, the OS specific Oracle installation guide should be consulted for details.  One of the most common problems with Oracle server configuration is sub-optimal I/O.  For example, the most important thing with Linux is enabling direct I/O on the underlying file system. Without that being enabled, Linux will cache files both in the system buffer cache and in SGA. That double caching is unnecessary and will deprive the server of RAM resources.  The following section provides a closer look by outlining some of the important Oracle parameters for performance.

Swap Space

The amount of swap space available to the system helps to increase both the number of processes and the amount of memory they can acquire without exhausting the system’s RAM.  Remember that although swap is slower than RAM, the system is intelligent enough to move data which is less likely to be needed to swap, thus freeing up more RAM for data in higher demand.  In this way, adding swap, a slower resource, can increase performance.

If you find your current swap allocation is lower than the Oracle recommendation, you need to increase the size of the swap partition or create a new partition on a different disk.  This is covered in detail in Chapter 5.

Semaphores

Semaphores act as flags for shared memory. Semaphores are either set on or off. When an Oracle process accesses the SGA in shared memory, it checks for a semaphore for that portion of memory. If it finds a semaphore set on for that portion of memory, indicating another process is already using that portion, the process will sleep and check again later. If there is no semaphore set on for that portion of memory, it sets one on and proceeds with its operation.  When it is done, it switches that semaphore back to off.

Oracle specifies semaphore values for semmsl, semmns, semopm and semmni as 250, 3200, 100 and 128, respectively.  These can be found in the output of the sysctl command in this same order.

# /sbin/sysctl -a | grep sem

kernel.sem = 250 32000     32   128

The values for the semaphores represent the following:

• semmsl:  The number of semaphores per set
• semmns:  The total number of semaphores available
• semopm:  The number of operations which can be made per semaphore call
• semmni:  The maximum number of shared memory segments available in the system

The Oracle recommended values is a good starting point for these parameters, but when running multiple Oracle databases on a system, semmsl and semmns may need to be increased to accommodate the additional instances.

To change this setting, edit the /etc/sysctl.conf file.  If there is already a line for kernel.sem, edit the values given; otherwise, add a line in the same format as the output above.  The line should look like this:

kernel.sem = 250 32000 100 128

This line can be added anywhere in the file, but it is best to keep all the changes in one place within the file.  Comments can be added by starting a line with a # character.

Shared Memory Settings

The parameters shmall, shmmax, and shmmni determine how much shared memory is available for Oracle to use.  These parameters are set in memory pages, not in bytes, so the usable sizes are the value multiplied by the page size, typically 4096 bytes.  To confirm the page size, use the command getconf -a | grep PAGE_SIZE.

• shmall:  The total amount of shared memory (in pages) which can be allocated on the system
• shmmax:  The maximum size of a shared memory segment (in pages)
• shmmni:  The maximum number of shared memory segments available on the system

Given that the SGA for a database must be kept in shared memory, the value of shmmax needs to be as big as the largest SGA.  The value for shmall needs to be bigger than the sum of all your databases.  The value for shmmni needs to be at least as high as the number of databases that are intended to be put on the system, but in practice is generally much higher (Oracle recommends 4096.)

The quick install guide includes directions on checking these parameters.  If they need to be modified, they can be set in the /etc/sysctl.conf file with entries like the following:

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096

Keep in mind that shmall and shmmax are set in 4 KB pages, not in bytes.

Other System Settings

A few additional settings are needed for Oracle.  These are not directly related to the system memory, but are included in this section for completeness.

• fs.file-max:  Controls the total number of files which can be opened at once
• ip_local_port_range:  This controls the number of network connections which can be allocated at once.  These are unrelated to the network ports on which incoming connections are made
• rmem_default and rmem_max:  Represent the default and maximum size of the network receive buffer
• wmem_default and wmem_max:  Represent the default and maximum size of the network send buffer

Under most circumstances, the Oracle recommended values should be used for these parameters.  These can be set in the /etc/sysctl.conf file.

fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

While many of these settings can be changed dynamically, it is best to set them in the /etc/sysctl.conf file and restart.  After restart, confirm that the settings are working.

---

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts: