loading
hello dummmy text

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
What happens to your #Oracle #Database with wrong #Kernel parameter values? Click To Tweet

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.

Appropriate #Kernel parameters for your #Oracle #Database Click To Tweet

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.

Was this useful? Like it and share it. Watch more videos.

I love to see your comments!

Feel free to give additional information in the comments, that I can validate and include in blog posts or update this as well.

24 Comment

  1. Ravi

    What about semaphores in kernal?.
    Semaphore means ‘ process ‘ .Can we call like this semaphore set ( set of processes called semaphore set ).

    In oracle recommended parameter are,
    semaphores = semmsl semmns semopm semmni
    kernel.sem = 250 32000 100 128

    250 – max number of semaphores (processes) per semaphore set ( or array ).
    32000 – Total number of processes oracle can use from server level. ( means whole oracle engine can use 32000 processes only ).
    100 – Max number of operations per semaphores( means per process).
    128 – Minimum number of semaphore sets ( means array ).
    It will vary on RAC machines.
    So that below is process calculation for preventing performance issue.

    in this value calculation,
    semmsl = 250 (per 1 set).
    semmni = 128 sets ( Minimum ) ( Bcz oracle engine need min 128 set for managing DBs and Cores).
    semmns = semmsl * semmni = 32000
    semopm = 100 ( you can set whatever you want based on number of processes you having ).

    1. Pawan Kumar Yaddanapudi

      Well said Ravi. Thanks for adding this to the blog. I missed to describe about semaphore parameter kernel.sem.
      But, I feel semaphore is defined differently by every other UNIX professionals. I don’t feel we can call it as PROCESS directly. But they are correlated. I feel every process will have a semaphore to access kernel resources like memory/storage. Hence, I am comfortable to refer it as a form of lock to the memory component for a process to access, it can be any process 🙂

  2. Navakanth

    When considering parameter fs.aio-max-nr.
    Hope storage should also support async I/O to accept calls, if storage doesn’t support async I/O performance is similar to sync I/O?

    1. Pawan Kumar Yaddanapudi

      Performance would not be same, as async I/O calls would be queuing in the memory leading to bad OS performance.

  3. Navakanth

    And good thing from 12c is ,installer doesn’t progress further , if it fails on pre requisite checks .:)

    1. Pawan Kumar Yaddanapudi

      Hope “ignore” option would be there to skip and continue. Is it not?

  4. David

    Hi Pawan,

    thanks for your great article. But actually I’ve problems to understand your calculation for fs.file-max. You say 8GB RAM = 2048 * 4MB = 2048 * 256 = 524288, but how you get to the 2048? From what?

    Can you get also an example for 30GB?

    Thanks and regards,
    David

    1. Pawan Kumar Yaddanapudi

      Hi David,

      As thumb rule is to have 256 for every 4MB, I have denominated 8GB into 2048 * 4 MB = 8192 MB = 8GB. Then replaced 4MB with 256 => 2048 * 256 = 524288.

      For 30 GB it would be – 7680 * 4MB = 7680 * 256 = 1966080

      1. Tamilarasan

        Thank you So much for this article Mr.Pawan,
        In another way to calculating the fs.file-max.
        For 4MB=256 and for 1MB it should be 64.
        So, below is the example for the different GB’s
        8 * 1024=8192MB * 64= 524288 –> 8GB
        15 * 1024= 15360MB * 64 = 983040 –>15GB
        30 * 1024= 30720 * 64=1966080 –>30GB

        Thanks & Regards,
        Tamil

  5. varun

    Hi Pawan,

    Thanks for the valuable information that is very rare.

    I have a question that : There is a little confusion in shared memory segment and pages. What exactly both of them and how they are co-related. Could you please explain in little detail and that would be really helpful if you draw a diagram as well to describe this co-relation.

    Please take an example of 5 gb RAM and then describe all these parameters in figures/bytes only if you are not tired of my question.

    Thanks and regards,
    Varun

    1. Pawan Kumar Yaddanapudi

      Hi Varun,

      As you read this blog, it says that pages are directly related to shared memory on the server. The only way a software or any tool/process on the UNIX uses memory is through pages. We have kernel parameters to control the maximum, minimum and sizes of each page size that Oracle database will support on that machine.

      To conclude: Shared memory is nothing but possible allocation of pages which are configured as per the kernel parameter settings.

  6. Ammrita

    Very well written pawan.ur blog contains the minute and very essential aspects of Oracle core dba.thank you.

    1. Pawan Kumar Yaddanapudi

      Thanks Ammrita.

  7. Chetan

    Very well explained Pawan. It has been very mix thoughts and articles on Kernels so far I have seen.
    I found yours to the point and informative. Thanks once again. 🙂

    1. Pawan Kumar Yaddanapudi

      Thanks Chetan.

  8. Ali

    Thank pawan, very well explained.
    Could you please add Linux huge pages, what is use in Oracle

    Thanks you.

    1. Pawan Kumar Yaddanapudi

      Sure Ali. Will probably add a new blog on this topic

  9. jai

    my memory target is less than shmmax,yet when we start the db,the no. of shared memory segments allocated is atleast 4 why?? I checked using ipcs -m command ,there 4 more shmid increased after db start and after shutting down db 4 shmid removed from the list.Did you check on this?? why still allocating multiple memory segment instead of big full size segment?? Am i calculating something wrong ,value of shmmax is also in form of pages???

    1. Pawan Kumar Yaddanapudi

      Can you please describe a bit on your database architecture and its OS configurations. It will help me to answer your query.

  10. Why should we configure limits.conf for Oracle database? |

  11. Md. Anisur Rahman

    Thanks for your clear explanation.
    Could you please explain little more ‘/dev/ shm’, what is used in Oracle? and have any relation with oracle database memory parameter.
    Could you please add Linux huge pages, what is used in Oracle?

    Thanks,
    Anis

  12. v v

    Hi Pawan,
    Thank you for well written document with e.g and it clears the doubts about shared memory segments.

  13. Rajesh

    Sir
    we have ORACLE 11g R2 database and previously it is running smoothly now we had a problem in taking backup .dmp file after segregate data to tow servers we take backup every day but it works for three days after three days backup doesn’t work and we restart the servers then again works for three days please tell us any solution

  14. Basha

    Hi,
    Can you please help me with solution for taking the backup of 1tb database using rman what are the things need to be consider?
    1.How many channels that i can configure?
    2.Is backup optimization should be enable?
    3.Compressed backup?
    4.Enabling the block change tracking method?
    5.where to check the logs for rman backup jobs?

Write a Reply or Comment

Your email address will not be published. Required fields are marked *

Knowledge That Can Always Keep Your Inbox Informed