Kernel parameters for Oracle Installation

Installing Oracle Database software is one of our regular activities as DBA. There might be supporting notes in each and every project we support, to complete it quicker and more efficiently. One of the pre-requisites we implement is to set up appropriate Kernel parameters at the operating system level on UNIX platforms. But, it would be really awesome to understand the reason behind using those parameters. Incorrect values of these parameters will lead to performance issues in the database as well. In the Oracle installation documents, it is clearly advised the list of parameters to set and their respective values.

This blog will explain you the purpose of Kernel parameters we set when installing database software and its side effects when not set correctly. It will help you to debug when you tune the performance at the OS level. 

List of Parameters:

Below are the list of parameters Oracle advises in the documents to set up on Linux 64-bit environment. We will take this set of parameters in this blog to understand them in detail.

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 2097152

kernel.shmmax = 4294967295

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

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 = 1048586

Categories:

These parameters can be categorized into 3 sections as the first part of the name says.

1. fs – File handles: All possible limitations in handling files.
2. kernel – Kernel specifics: Limitations on resource usage at kernel level like Memory, CPU etc…
3. net – Network specifics: Limitations on network usage.

Let us explore:

1.fs:

fs.aio-max-nr – This parameter defines the maximum number of ASYNCHRONOUS I/O calls that system can handle on the server. While aio-nr shows the number of calls that system has at that moment.

If this parameter value is insufficient for Oracle Database, then the possible error that you see in alert log will be:

ORA-27090: Unable to reserve kernel resources for asynchronous disk I/O

fs.file-max – This parameter defines the maximum number of file handles, meaning that how many number of opened files can system support at any instance.

It is recommended to have a minimum of 256 as value for every 4MB of RAM you have. So for 8GB RAM = 2048 * 4MB = 2048 * 256 = 524288.

So if you are growing your RAM on the server, then consider to re-check this parameter.

2.kernel:

SHMMNI, SHMALL, SHMMAX – Before we describe each one of these, all of them defines the limitations on using shared memory on the server. With respect to UNIX shared memory is just memory segments shared between multiple application processes on the server. So Oracle Database is one of them.

SHMMNI – It sets the maximum number of shared memory segments that server can handle. As Oracle recommends the value should be at least 4096, it says that we cannot find more than 4096 number of shared memory segments at any instance on the server. Note that SHMMNI value is in numbers.

SHMALL – It defines the total amount of shared memory PAGES that can be used system-wide. It means that to the use all the physical memory this value should be less than or equal to total physical memory size. For DBA’s, it means that sum of all SGA sizes on the server should be less than or equal to SHMALL value. Note that SHMALL value is a number of pages.

SHMMAX – It defines the maximum size that one shared memory segment that server can allocate. Note that SHMMAX value in bytes. Oracle recommends that this value should be greater than half of the physical memory on the server.

Case study:

Let us run through a case study to understand these parameter effects better.

Consider you have a server with 8GB physical memory(RAM). Let’s define the best possible SHMMNI, SHMALL, SHMMAX values for this system.

SHMMNI – No change it should be 4096. It must be increased if you have more than at least one fourth (1024) Oracle Databases running on the server. Which we never recommend.

SHMALL – By default the page size on Linux is 4KB. The total size of RAM is 8GB. Let us leave at least 1GB of RAM for Linux kernel to run, with which consider 7GB can be used for Oracle Databases. Now value of SHMALL can be:

(7*1024*1024)KB/4KB = 1835008

SHMMAX – If you want the maximum size of SGA on this server to be 5GB, then this parameter value should be 5*1024*1024*1024 = 5368709120 bytes. 

This, in turn, says that you should not have any database with more than 5GB of SGA. 

But you can have multiple databases with each 5GB of SGA or even less. 

This is the fact why Oracle recommends to have this value more than half of the memory to utilize it for SGA(s).

By chance, if your SGA size is more than 5GB say it is 7GB then 2 shared memory areas will be allocated to SGA with one of 5GB and two of 2GB sizes, which doesn’t perform well.

3.net:

net.ipv4.ip_local_port_range – This parameter defines the range of port numbers that system can use for programs which want to connect to the server without a specific port number.

Now, it makes sense if you have come across somebody advising you not to use port numbers for listener beyond 9000 😊. Also, just look back to documents on OEM installation, Oracle uses and advises all the default port numbers less than 9000 😊. As I observed.

net.core.rmem – This parameter defines the default  and maximum RECEIVE socket memory through TCP.

net.core.wmem – This parameter defines the default  and maximum SEND socket memory through TCP.

Oracle recommends to set these values as by default LINUX does not support to transfer or receive large files over TCP. These parameters are pretty important to set considering the amount of the data that flows between database and application – can be BLOB, CLOB or DataGuard redo transfers and so on!!!

“Watch the video below and see how tricky the effect of SHMMAX parameter value would be”, You will like it.

Conclusion:

• If you are creating a new oracle database instance, not just a free physical memory on the server to check; But also make sure your SHMALL, SHMMNI, SHMMAX parameters are re-configured
• When your data transfer between application and data is going high, run through the network parameters and see if receive and send sockets are the reason behind network delays.
• As your database grows, data files will do. Not just making sure DB_FILES parameter is set to support a number of data files; Verify kernel parameters on file handlers are also configured accordingly.

Configuring SHMMAX and SHMALL for Oracle in Linux

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”.

silicon:~ # cat /proc/sys/kernel/shmmax

536870912

SHMALL is the total size of Shared Memory Segments System wide set in “pages”.

silicon:~ # cat /proc/sys/kernel/shmall

1415577

The key thing to note here is the value of SHMMAX is set in “bytes” but the value of SHMMALL is set in “pages”.

What’s the optimal value for SHMALL?

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 be greater 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.

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

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”.

So here goes the calculation.

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.

myhost:~ # getconf PAGE_SIZE

4096

or

myhost:~ # cat /proc/sys/kernel/shmmni
4096

Convert 5GB into bytes and divide by page size, I used the linux calc to do the math.

myhost:~ # echo “( 5 * 1024 * 1024 * 1024 ) / 4096 ” | bc -l

1310720.00000000000000000000

Reset shmall and load it dynamically into kernel

myhost:~ # echo “1310720” > /proc/sys/kernel/shmall
myhost:~ # sysctl –p

Verify if the value has been taken into effect.

myhost:~ # sysctl -a | grep shmall
kernel.shmall = 1310720

Another way to look this up is

silicon:~ # ipcs -lm

—— Shared Memory Limits ——–
max number of segments = 4096 /* SHMMNI */
max seg size (kbytes) = 524288 /* SHMMAX */
max total shared memory (kbytes) = 5242880 /* SHMALL */
min seg size (bytes) = 1

To keep the value effective after every reboot, add the following line to /etc/sysctl.conf

echo “kernel.shmall = 1310720” >> /etc/sysctl.conf

Also verify if sysctl.conf is enabled or will be read during boot.

myhost:~ # chkconfig boot.sysctl
boot.sysctl on

If returns “off”, means it’s disabled. Turn it on by running

myhost:~ # chkconfig boot.sysctl on
boot.sysctl on

What’s the optimal value for SHMMAX?
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 2nd 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.

Like SHMALL, SHMMAX can be defined by one of these methods..

Dynamically reset and reload it to the kernel..

myhost:~ # echo “536870912” > /proc/sys/kernel/shmmax

myhost:~ # sysctl –p — Dynamically reload the parameters.

Or use sysctl to reload and reset ..

myhost:~ # sysctl -w kernel.shmmax=536870912

To permanently set so it’s effective in reboots…

myhost:~ # echo “kernel.shmmax=536870912” >> /etc/systctl.conf

Install doc for 11g recommends the value of shmmax to be set to “4GB – 1byte” or half the size of physical memory whichever is lower. I believe “4GB – 1byte” is related to the limitation on the 32 bit (x86) systems where the virtual address space for a user process can only be little less than 4GB. As there’s no such limitation for 64bit (x86_64) bit systems, you can define SGA’s larger than 4 Gig’s. But idea here is to let Oracle use the efficient one-segment model and for this shmmax should stay higher than SGA size of any individual database on the system.