Installing Oracle Database software is one of our regular activities as DBA.
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.
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.
fs – File handles: All possible limitations in handling files.
kernel – Kernel specifics: Limitations on resource usage at kernel level like Memory, CPU etc…
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.
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.
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.
fs – File handles: All possible limitations in handling files.
kernel – Kernel specifics: Limitations on resource usage at kernel level like Memory, CPU etc…
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.
