Linux Kernel Parameter and Oracle Parameter Adjustment

We usually need to configure the core parameters of the Linux system when installing and setting the Oracle database on Linux or replacing or upgrading the hardware, and then adjust the Oracle system.

We usually need to configure the core parameters of the Linux system when installing and setting the Oracle database on Linux or replacing or upgrading the hardware, and then adjust the Oracle system.

We usually need to configure the core parameters of the Linux system when installing and setting the Oracle database on Linux or replacing or upgrading the hardware, and then adjust the parameters of the Oracle system.

Kernel. shmall is the physical memory divided by pagesize;
Kernel. shmmax is half of the physical memory;
Fs. file-max is 512 multiplied by processes (for example, 128 processes );
Net. ipv4.ip _ local_port_range/net. core. rmem_default/net. core. rmem_max parameters are set differently from those in the official documentation, which was changed according to the latest requirements of metalink 343431.1;
Net. ipv4.tcp _ rmem/net. ipv4.tcp _ wmem parameters do not need to be set in general, unless they require a lot of network transmission for Dataguard/Streams;
Set other parameters as required in the official documentation.

View OS system page size
# Getconf PAGESIZE
4096

1. Core parameters in Linux:

# Vi/etc/sysctl. conf
Kernel. shmall = 2097152
Kernel. shmmax = 2147483648
Kernel. shmmni = 4096
Kernel. sem = 250 32000 100 128
Fs. file-max = 65536

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

After modification, make the parameter take effect:
#/Sbin/sysctl-p

2. Adjust core parameters in Linux

Kernel. shmmax ----

SHMMAX Available physical memory Defines the maximum allowable size of one shared memory segment. the SHMMAX setting shoshould be large enough to hold the entire SGA in one shared memory segment. A low setting can cause creation of multiple shared memory segments which may lead to performance degradation.

Shmmax is one of the most important parameters in core parameters. It is used to define the maximum value of a single shared memory segment. The shmmax setting should be large enough to accommodate the entire SGA in a shared memory segment, setting too low may lead to the creation of multiple shared memory segments, which may lead to a reduction in system performance.

Some sayings: SHMMAX is only a number used for comparison when the shared memory segment is created. When the shared memory segment is created by a Process, the operating system checks whether the required shared memory segment value is greater than the shmmax value. If yes, an error is thrown. At this time, the system will create one or more shared memory segments to meet the needs of the process. Generally, the number of shared memory segments does not have a direct relationship with system performance, nor does it have a major impact on performance.

Steve Adams said in his booklet that when the instance is started and the Server Process is created, multiple small shared memory segments may cause slight system performance degradation at that time (you need to create multiple virtual address segments at startup, when a process is created, the process must be "identified" for multiple segments, which has some impact), but it will not be affected at other times. This means that if your program does not frequently Create Processes (and Destroy Them), performance is not a concern.

Of course, Oralce's suggestion is that a large shared memory segment can accommodate the entire SGA, so that there will be no or even slight performance degradation risks at any time.

In the Oracle Installation documentation, we recommend that you set shmmax to the maximum limit value (setting shmmax to the 32-bit number limit) for the 32Bit Linux system, that is, 4 GB. Therefore, in general, you can directly set shmmax to the maximum physical memory for 1-4 GB physical memory, so the SGA must be in a shared memory segment, if the physical memory of a 32-bit Linux system is greater than 4 GB, set it to 4 GB.

In short, generally set shmmax> = SGA (Note whether the 32Bit system supports SGA up to 1.7 GB ). For 64-bit Linux operating systems, set shmmax to be greater than SGA_MAX_SIZE.
(For reference only)

Ipcs-sa can see the number of shared memory segments

Kernel. shmall ----

The kernel. shmall parameter controls the number of pages in the shared memory. The size of the Linux Shared Memory Page is 4 kb, and the size of the shared memory segment is an integer multiple of the size of the shared memory page. The maximum size of a shared memory segment is 16 GB, so the number of pages to be shared is 16 GB/4KB = 16777216KB/4KB = 4194304 (page ), that is, 16 GB physical memory in 64 bit system, set the kernel. shmall = 4194304 meets the requirements (almost twice the original 2097152 ). In this case, you can adjust the shmmax parameter to 16 GB, and modify the SGA_MAX_SIZE and SGA_TARGET to 12 GB (the maximum SGA size you want to set, of course, can also be 2 GB ~ 14G and so on. It also coordinates PGA parameters and other memory usage such as OS, and cannot be set to too full, such as 16G ).

Kernel. shmmni ----
The shmmni Kernel Parameter is the maximum number of shared memory segments (note that this parameter is not shmmin, but shmmni, and shmmin indicates the minimum size of the memory segments ). The shmmni default value is 4096, which is generally enough.

3. parameters to be adjusted in Oracle
SGA_MAX_SIZE
SGA_TARGET
DB_CACHE_SIZE

SGA_MAX_SZIE is the sga upper limit allowed by the instance. A static parameter cannot be dynamically modified.
SGA_TARGET is the automatically managed parameter of sga, which can be modified dynamically.

Relationship between sga_max_size and size of each SGA component
The size of sga_max_size is smaller than the total size of each pool in the actual SGA, so the value of sga_max_size is automatically replaced by the total size of the actual SGA by oracle. If sga_max_size is not set, oracle automatically sets the sga_max_size value based on the total size of the actual SGA.
Set sga_max_size to a value greater than the total size of each pool in SGA: However, the value of sga_max_size is a reasonable value compared to all available physical memory. The actual value of sga_max_size is the same as that specified by sga_max_size in pfile.

An important parameter is introduced in Oracle 10g: SGA_TARGET,This is also a new feature of Oracle 10 Gb. Automatic Shared Memory Management (ASMM) controls this feature only by the SGA_TARGE parameter. After this parameter is set, you do not need to specify the size for each memory zone. SGA_TARGET specifies the maximum memory size that SGA can use. The size of each memory in SGA is controlled by Oracle and does not need to be specified manually. Oracle can adjust the size of each region at any time to reach the optimum system performance, and control the sum of them within the value specified by SGA_TARGET. Once a value is specified for SGA_TARGET (the default value is 0, that is, no ASMM is started), The ASMM feature is automatically started.

After the ASSM feature is enabled after SGA_TARGET is set for 10 Gb, the memory size of the following areas is dynamically shared:
* Buffer cache (DB_CACHE_SIZE)
* Shared pool (SHARED_POOL_SIZE)
* Large pool (LARGE_POOL_SIZE)
* Java pool (JAVA_POOL_SIZE)
* Streams pool (STREAMS_POOL_SIZE)

The memory size of other regions in SGA is still fixed and not shared.
Its meaning is the same as that of SGA_MAX_SIZE, which also indicates the maximum size of SGA. Therefore, it has a limit that its size cannot be greater than that of SGA_MAX_SIZE. In Oracle10g, SGA_MAX_SIZE still indicates the upper limit of SGA size, while SGA_TARGET is the sum of the maximum values of all SGA components, that is, when SGA_TARGET <SGA_MAX_SIZE, oracle will ignore the value of SGA_MAX_SIZE, and SGA_TARGET becomes the limitation of SGA in this instance. It can dynamically change the size, but cannot exceed the value of SGA_MAX_SIZE.

In 11g, this SGA_TARGET can only be set to the size equal to SGA_MAX_SIZE., The setting is smaller than it. oracle will automatically help you adjust the setting. If the setting is bigger than it, it will still cause an error. Now let's think about it. oracle's processing of SGA_TARGET is moving in the correct and simple direction.

An important benefit of SGA_TARGET is that it can optimize SGA utilization and save memory costs. After the ASMM is started, Oracle will automatically adjust the size of each region as needed, greatly reducing memory shortage in some regions, and there is a conflict between idle memory in some regions.