Linux Kernel parameters and Oracle-related parameter tuning

Linux kernel parameters and Oracle related parameter adjustment categories: Oracle Basic knowledge2009-10-14 12:23 9648 people Read reviews (0) Favorites Report Oraclelinuxsemaphorearraysdatabase oracle10g

The size of the Linux kernel parameters and Oracle has a large shutdown, such as the ORA-27102 error, because the size of the kernel parameters is improper. Specific Reference blog:

Upon startup of Linux database get ora-27102:out of memory linux-x86_64 Error:28:no space left on device

Http://blog.csdn.net/tianlesoftware/archive/2011/05/16/6424945.aspx

There is a similar problem in Oracle 11gR2:

Oracle 11gR2 RAC ORA-00845 Memory_target not supported on the This system workaround

Http://blog.csdn.net/tianlesoftware/archive/2010/11/17/6013777.aspx

When you install Oracle, you can reference the Oracle installation documentation to set the relevant values, but the values of some parameters need to be adjusted according to our own circumstances.

1. Core parameters under Linux system

# 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 65000

Net.core.rmem_default = 4194304

Net.core.rmem_max = 4194304

Net.core.wmem_default = 262144

Net.core.wmem_max = 262144

This parameter is saved under/etc/sysctl.conf, and you do not need to restart the OS to modify the file, just use the following command:

#/sbin/sysctl-p

You can let the modified parameters take effect.

2. Kernel.shmmax parameters

2.1 Description

SHMMAX Available Physical memory defines the maximum allowable size of one shared memory segment. The SHMMAX setting should is large enough to hold the entire SGA in one shared memory segment. A low setting can cause creation of multiple GKFX memory segments which may leads to performance degradation.

Shmmax is one of the most important parameters in the core parameter, used to define the maximum value of a single shared memory segment, the Shmmax setting should be large enough to accommodate the entire SGA under a shared memory segment, setting too low may result in the need to create multiple shared memory segments, which can result in degraded system performance.

SHMMAX is simply a number used to compare when a shared memory segment is created, when a shared memory segment is created by a process, the operating system checks whether the value of the shared memory segment required is greater than the value of SHMMAX, and if so, an error is thrown. At this point, the system creates additional one or more shared memory segments to meet the needs of the process. In general, the number of shared memory segments is not directly related to the system performance, nor does it have a significant impact.

When the instance is started and the server Process is created, multiple small shared memory segments can cause slight system performance degradation at that time (you need to create multiple virtual address segments at boot time, and when the process is created to "recognize" multiple segments, there is some impact), But no other time has any effect. This means that if your program is not often create Processes (as well as destroy them), performance is not an issue to consider.

Oralce recommends SHMMAX > SGA (sga_max_size) so that at any time there will be no hidden danger of even slight performance degradation.

2.2 Example

In the previous section, if Shmmax and sga,oracle create multiple shared memory segments, we can use IPCS-SA to see the number of shared memory segments.

(1) View

[Email protected] ~]# cat/etc/sysctl.conf | grep Kernel.shmmax
Kernel.shmmax = 20971520
[Email protected] ~]# Ipcs-sa

------Shared Memory Segments--------
Key shmid owner perms bytes nattch Status
0x00000000 65537 Oracle 640 4194304 27
0x00000000 98306 Oracle 640 20971520 27
0x00000000 131075 Oracle 640 20971520 27
0x00000000 163844 Oracle 640 20971520 27
0x00000000 196613 Oracle 640 20971520 27
0x00000000 229382 Oracle 640 20971520 27
0x00000000 262151 Oracle 640 20971520 27
0x00000000 294920 Oracle 640 20971520 27
0XD2776B04 327689 Oracle 640 20971520 27

------Semaphore Arrays--------
Key Semid owner Perms Nsems
0xfafd7074 360449 Oracle 640 104

------Message Queues--------
Key Msqid owner perms used-bytes messages

Because Kernel.shmmax is set too small, multiple shared memory segments are allocated.

Change the bottom:
[Email protected] ~]# cat/etc/sysctl.conf | grep Kernel.shmmax
Kernel.shmmax = 2147483648
[Email protected] ~]# sysctl-p
[Email protected] ~]# su-oracle
[Email protected] ~]$ sqlplus '/as sysdba '

Sql*plus:release 10.2.0.1.0-production on Mon Nov 16 05:50:00 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production
With the partitioning, Real application Clusters, Oracle Label Security, OLAP
and Data Mining scoring Engine options

sql> Startup force
ORACLE instance started.

Total System Global area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 104859892 bytes
Database buffers 58720256 bytes
Redo buffers 2973696 bytes
Database mounted.
Database opened.
Sql> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production
With the partitioning, Real application Clusters, Oracle Label Security, OLAP
and Data Mining scoring Engine options

Look again:
[Email protected] ~]# Ipcs-sa

------Shared Memory Segments--------
Key shmid owner perms bytes nattch Status
0XD2776B04 360449 Oracle 640 171966464 27

------Semaphore Arrays--------
Key Semid owner Perms Nsems
0xfafd7074 491521 Oracle 640 104

------Message Queues--------
Key Msqid owner perms used-bytes messages
Only one memory segment has been allocated

3. Kernel.shmall

The Kernel.shmall parameter is to control the number of shared memory pages. The parameter size is the physical memory divided by pagesize;

To view the size of the OS system page

#getconf PAGESIZE

4096

The pagesize shown here is 4k, assuming that the maximum size of a shared memory segment is 16G, then the required number of shared memory pages is 16gb/4kb=16777216kb/4kb=4194304 (page), which is the 64Bit system 16GB physical memory, set Kernel.shmall = 4194304 meets the requirements and is almost twice times the original setting of 2097152.

4. Kernel.shmmni Parameters

The Shmmni kernel parameter is the maximum number of shared memory segments (note that this parameter is not shmmin and is

Shmmni, shmmin indicates the minimum size of the memory segment). Shmmni the default value of 4096, it is generally sufficient.

5. Fs.file-max Parameters

Fs.file-max is 512 times processes.

such as 128 process, then file-max=512*128=65536.

6. Parameters that need to be adjusted under Oracle

A very important parameter was introduced in Oracle 10g: Sga_target, which is a new feature of Oracle 10g. Automatic shared memory management (Automatic shared Memories Management ASMM), which controls this feature, is just the sga_targe of this parameter. After setting this parameter, you do not need to specify the size for each memory area. SGA_TARGET Specifies the maximum amount of memory that the SGA can use, and the size of each memory in the SGA is controlled by Oracle and does not require human designation.

Oracle can adjust the size of each area at any time to achieve the most reasonable size of the system's optimal performance, and to control their sum within the value specified by the Sga_target. Once a value has been assigned to Sga_target (the default is 0, that is, ASMM is not started), the ASMM is started automatically

Characteristics.

After setting Sga_target in 10g and starting the ASSM feature, only the memory sizes of the following areas are 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 areas in the SGA is still fixed and not shared. Its meaning is the same as the Sga_max_size, also represents the largest size of the SGA, so it also has a limit, that is, its size can not be greater than the size of sga_max_size.

oracle10g, Sga_max_size still represents the upper limit of the size of the SGA, and Sga_target is the sum of the maximum size of all components of the SGA, that is, when sga_target< sga_max_size, Oracle The value of Sga_max_size is ignored, and Sga_target is the upper limit of the SGA in this instance, it can dynamically change size, but cannot be greater than the value of sga_max_size.

When the sga_target< sga_max_size, the sga_max_size becomes the sga_target size after the instance restarts.

In 11g, this sga_target can only set is equal to the size of sga_max_size, set smaller than it, Oracle will automatically help you adjust, set larger than it, it is still wrong. Now you can think about it, Oracle's handling of Sga_target is moving in the right and simple direction.

An important benefit of sga_target is that it enables the utilization of the SGA to be optimized to save memory costs. Since ASMM is started, Oracle automatically adjusts the size of each area as needed, greatly reducing memory tension in some areas, and in some areas there is a contradiction of memory idle.

Linux Kernel parameters and Oracle-related parameter tuning