Oracle initial installation memory settings reference

Prerequisites
Shared memory: shared memory segment:
A memory area can be read by different processes. Oracle uses it to form sga. Oracle uses the following three methods to create an sga zone:
1. Use a single shared memory segment.
2. Use multiple consecutive shared memory segments.
3. Use multiple discontinuous shared memory segments.
Oracle is a preferred method. If the initialization succeeds, the initialization continues. If the initialization fails, the second method is used. If the initialization fails, the third method is used. If the initialization fails, the initialization fails and the oracle Reports the error code.

Semaphore:
It can be seen as a tag. There can be on and off statuses. Oracle uses semaphores to control the access of server processes to the sga zone.
Shared memory and semaphore are important resources for oracle to run on unix. If an oracle instance cannot obtain the required resources, the instance will fail to start.

PGA + SGA = memory occupied by ORACLE.

PGA: Memory occupied by each oracle user, usually set to 10% of the memory. official recommendations:
Oracle recommends initially setting this parameter to 16% of your server's physical memory for OLTP systems and 40% of your server's physical memory for DSS systems

PGA needs to log on to the oracle client and modify it in the routine, or
For ora9i, the sga of 9i on 32-bit platforms can only be extended to 1.7 GB by default. To break this restriction, You need to link the Oracle software again. The following example shows that sga is m:
Alter system set db_cache_size = 700 m scope = spfile;
Alter system set shared_pool_size = 200 m scope = spfile;
Alter system set pga_aggregate_target = 100 m scope = spfile;

For oracle10G, sga can only be expanded to 2.5 GB by default. To break this restriction, You need to link the Oracle software again. If you have 4 GB memory, you can allocate it as follows:
You do not need to set db_cache_size and shared_pool_size for 10 Gb. It is automatically managed and you only need to give him the maximum memory.
Alter system set sga_target = 2g scope = spfile;
Alter system set pga_aggregate_target = 500 m scope = spfile;

View the number of oracle connections

Select count (*) from sys. v _ $ session;

 

View oracleMax connections

SQL> show parameter processes. Check the last line.

Modify SQL> alter system set processes = 1000 scope = spfile;

Scope = spfile is used to take effect again

 

Physical Server Memory X (0.16 to 0.40) = total memory to be allocated to all PGA
Initial SGA size:
Server physical memory × 0. 55 = total memory to be allocated to all SGA instances (TSGA)
Note: TSGA must be smaller than the size of the shared memory segment allocated to oracle. The shared memory segment echo "2147483648">/proc/sys/kernel/shmmax
TSGA/number of Oracle instances on the server = total SGA size of each instance (TSGAI)
TSGAI × 0. 4 = total memory allocated to the SHARED POOL
TSGAI × 0. 6 = total memory allocated to the DATABASE BUFFER CACHE
#### TSGAI × 0. 10 = total memory allocated to REDO LOG BUFFER
The above principles are applicable to servers with memory less than 1 GB.
On servers with memory larger than 1 GB:
Total memory to be allocated to all SGA instances (TSGA) = physical server memory X (0.60 to 0.75)

Shared memory allows processes to access common structures and data in shared memory segments. This is the fastest way of inter-process communication (IPC), mainly because data is transmitted between processes without kernel operations. Data does not need to be copied between processes.
Oracle uses the shared memory for its system global zone (SGA), which is a memory area shared by all Oracle backup processes and foreground processes. Allocating sufficient capacity to SGA is important to Oracle performance because it is responsible for saving the database buffer cache, sharing SQL, access paths, and more.

Shmmax
Maximum size of the shared memory segment (in bytes)
Meaning: This setting does not determine the actual amount of physical memory used by the oracle database or operating system. It only determines the maximum amount of memory that can be used. This setting does not affect the kernel resources of the operating system.
Setting Method: 0.5 * physical memory
By directly changing the/proc file system, you can change the SHMMAX default settings without restarting the computer. You can use the following method to dynamically set the SHMMAX value. By placing this command in the/etc/rc. local Startup File, you can make it permanently valid:
Echo "2147483648">/proc/sys/kernel/shmmax

Shmmni
This Kernel Parameter is used to set the maximum number of shared memory segments within the system range. The default value of this parameter is 4096. This value is sufficient to meet your needs, so you do not need to change it.
Run the following command to determine the value of SHMMNI:
# Cat/proc/sys/kernel/shmmni
4096

Shmall
This parameter controls the total amount of shared memory that can be used by the system at one time (in the unit of pages)
Shmall is 2097152 by default and can be queried using the following command:
# Cat/proc/sys/kernel/shmall
2097152
The default shmall settings are sufficient for Oracle RAC 10g installation.
(Note: The page size in Red Hat Linux on i386 is 4,096 bytes. However, you can use bigpages to configure a larger memory page size .)

Set Signal
The best description of the signal is that it is used to provide a synchronous counter between processes (or threads in the process) that share resources (such as shared memory. Unix System V Supports signal sets. Each of these signals is a signal count. When an application requests a signal, it uses a "set" to do this.
To determine all signal limits, run the following command:
# Ipcs-ls
------ Semaphore Limits --------
Max number of arrays = 128
Max semaphores per array = 250
Max semaphores system wide = 32000
Max ops per semop call = 32
Semaphore max value = 32767

You can also use the following command:
# Cat/proc/sys/kernel/sem
250 32000 32 128

Semmsl
Kernel Parameters are used to control the maximum number of signals for each signal set.
The semaphore occupied by the system can be identified by the following command: # ipcs-sb, where the column NSEMS shows that the system has occupied the semaphore
Oracle recommends that you set SEMMSL to the maximum PROCESS instance parameter setting in the init. ora file (applicable to all databases on Linux) and add 10. In addition, we recommend that you set SEMMSL to no less than 100.
Example: set semsys: seminfo_semmsl =-200

Semmni
Kernel Parameters are used to control the maximum number of CITIC sets in the entire Linux system.
Oracle recommends setting SEMMNI to no less than 100.

Semmns

Kernel Parameters are used to control the maximum number of signals (not signal sets) in the entire Linux system.
Each process occupies a semaphore. Oracle recommends that you set SEMMNS to the sum of the PROCESSES instance parameter settings for each database on the system, and double the maximum PROCESSES, add 10 to each Oracle database on the system.
Setting Method: This value can be calculated as follows: initsid of each oracle instance. the sum of the values of the processes in ora (excluding the maximum processes parameter) + the maximum processes × 2 + 10 × Number of oracle instances.

Shmseg
Meaning: the maximum number of shared memory segments that each user process can use.
Example: set shmsys: shminfo_shmseg = 20:
　

How to increase the number of ORACLE connections
The number of ORACLE connections (sessions) is related to the number of processes in the parameter file. Their relationships are as follows:
Sessions = (1.1 * process + 5)
However, when we increase the number of processes, the database often cannot be started. This is because we also missed a unix system parameter: It is semmns in/etc/system/, which is the semaphore parameter of the unix system. Each process occupies a semaphore. After the semmns is adjusted, You need to restart the unix operating system to make the parameter take effect. However, its size is subject to hardware memory or oracle sga. Range: 200--2000.
Semmns calculation formula: SEMMNS> processes + instance_processes + system
Processes = database parameter processes value instance_processes = 5 (smon, pmon, dbwr, lgwr, arch)
System = the semaphore occupied by the system. The semaphore occupied by the system can be identified by the following command: # ipcs-sb
The column NSEMS shows that the system has occupied the semaphore.

Adjustments to basic oracle parameters:
/Oracle/admin/ora9i/pfile/initora9i. ora.10202007144030 is created during installation. It is used for restoration. What is used during normal oracle startup?
Products/9.2/dbs/spfileora9i. ora, a binary file, cannot be modified. When oracle is adjusted, the modification is recorded in this file. When the modification causes oracle to fail to start normally, you can use the pfile file to resume startup.
Startup pfile = '/oracle/admin/ora9i/pfile/initora9i. ora.10202007144030' (startup is enabled normally)
However, oracle parameters will change to the initial installation status.
You can use the following command to generate a pfile:
Create spfile from pfile = '/oracle/admin/ora9i/pfile/initora9i. ora.10202007144030'
Adjusted parameters