The relationship between the System Global Area (SGA) in Oracle and the `SHMMAX` and `SHMALL` parameter in Unix systems

The relationship between the System Global Area (SGA) in Oracle and the SHMMAX ?and SHMALL parameter in Unix systems is an important aspect of Oracle database memory management on Unix platforms. Here's how they are related:

SGA (System Global Area): The SGA is a group of shared memory structures, such as data buffers, control structures, and SQL command areas, that contain data and control information for an Oracle Database. It is allocated when an Oracle instance starts and is shared by all server and background processes of the instance. The SGA plays a crucial role in the performance and efficiency of data processing and storage in Oracle databases.

SHMMAX: This is a parameter in Unix-based operating systems that defines the maximum size (in bytes) of a single shared memory segment. It's a kernel setting that affects how large blocks of shared memory can be allocated. This setting is important for database systems like Oracle that rely heavily on shared memory for efficient operation.

SHMALL: SHMALL is the total size of all shared memory segments that can be allocated system-wide on a Unix or Linux system, and it's set in "pages", not bytes. The page size, usually 4 KB, can vary depending on the system configuration.

Relationship: The relationship between the SGA and SHMMAX is critical for Oracle database performance and stability. When Oracle's SGA is initialized, it attempts to allocate a large block of shared memory. The size of this block is determined by various settings in Oracle, including the SGA_MAX_SIZE parameter.

?? - If the size of the SGA as determined by Oracle’s configuration exceeds the SHMMAX value, Oracle will fail to allocate the shared memory for the SGA, resulting in startup errors.

?? - On the other hand, if SHMMAX is sufficiently large, Oracle can allocate the necessary shared memory for the SGA without any issues.

?? - It’s important to configure SHMMAX to be larger or equal to the maximum size of the SGA to ensure smooth operation of the Oracle instance. If the SGA needs to be larger, the SHMMAX value needs to be increased accordingly.

If the size of SHMMAX is less than the size of the SGA (System Global Area) in Oracle, Oracle does not simply give up on setting the SGA; instead, it tries different strategies to allocate the necessary shared memory. The approach Oracle takes depends on the version and configuration, but generally, it involves breaking the SGA into multiple shared memory segments. Here's a more detailed explanation:

1. One-Segment Model: Oracle first tries to allocate the SGA in a single shared memory segment. If the SGA size is within the SHMMAX limit, this allocation succeeds. However, if SHMMAX is smaller than the required SGA size, this single-segment allocation will fail.
2. Multi-Segment Models: When the one-segment model fails due to SHMMAX limitations, Oracle will attempt to use a multi-segment model. There are two types of multi-segment models Oracle might use:

• Contiguous Multi-Segment: Oracle tries to allocate the SGA in multiple contiguous shared memory segments. Each segment is as large as possible without exceeding SHMMAX. The segments are arranged in memory to appear as one large contiguous block. This approach is tried first because it is efficient and minimizes memory fragmentation.
• Non-Contiguous Multi-Segment: If the contiguous approach fails, perhaps due to memory alignment requirements or other constraints, Oracle will attempt to allocate non-contiguous shared memory segments. This means that the segments of the SGA will be spread throughout the system's memory, not contiguous but still functioning as a whole. This approach is less efficient than the contiguous model but provides more flexibility in memory allocation.

?3. Allocation Challenges and Errors: If Oracle cannot allocate the SGA using any of these models due to SHMMAX limitations or other system constraints, it may result in startup errors. In such cases, database administrators typically need to adjust SHMMAX or other related parameters and possibly reconfigure the SGA settings.

Oracle tries to adapt to the SHMMAX constraints by breaking down the SGA into multiple shared memory segments. This allows Oracle to function even when SHMMAX is smaller than the SGA size, although it may impact the efficiency and performance of the database.

If Oracle Database cannot allocate the necessary shared memory for the SGA (System Global Area) during startup, it typically generates specific error codes. These errors are part of Oracle's ORA error code series, which provides detailed information about the nature of the problem. Common error codes related to SGA allocation failures include:

1. ORA-7306: This error indicates a failure in the shmget() system call, which is used for allocating shared memory. This error can occur if Oracle tries to allocate a larger shared memory segment than the maximum allowed by SHMMAX.

2. ORA-7329 and ORA-7334: These errors suggest that Oracle determined it needs more shared memory segments than the maximum number Oracle can handle (`SS_SEG_MAX`). This could be due to an insufficiently large SHMMAX setting, requiring Oracle to try and allocate too many smaller segments.

3. ORA-7336: This error is raised when Oracle encounters an error while allocating a shared memory segment, which can occur if the requested size exceeds SHMMAX.

4. ORA-7307 and ORA-7337: These errors occur when there's a problem with the shmat() system call, used for attaching the shared memory segment to the process's address space. An error here could be due to misalignment or other issues related to memory attachment.

5. ORA-27146: This error suggests that Oracle could not find enough contiguous memory, which can happen if the system's shared memory settings (`SHMMAX` and others) are not configured to support the size of the SGA Oracle tries to allocate.

6. ORA-27123: This error indicates a failure to attach to the shared memory segment. This can happen for various reasons, including incorrect permissions or issues with memory addresses.

When encountering such errors, a Database Administrator (DBA) would typically investigate and adjust the relevant system parameters, such as SHMMAX, SHMNI, and Oracle's SGA configuration settings, to resolve the issue. The exact error message and accompanying information can provide valuable insights into what needs to be adjusted.

Setting SHMMAX to a value equal to or greater than sga_max_size is a good practice for Oracle database servers, as it supports efficient SGA allocation and can help avoid related startup errors.

Configuring SHMALL for Oracle in Linux

Physical Memory Limitation: The value of SHMALL should be less than the total physical memory available on the system. This is to prevent overcommitment of memory resources.

Cumulative SGA Limitation: The value should also accommodate the sum of the SGAs (System Global Areas) of all Oracle databases running on the server. Exceeding this limit can result in an "out of memory" error, as the system can't allocate more shared memory for new or resized SGAs “out of memory” error (`ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device`).

Calculating Optimal SHMALL

Example Scenario: For a system with 6GB physical memory, where 1GB is reserved for the Linux Kernel and 5GB is dedicated to Oracle Databases.

Calculation Steps:

?? - Determine Page Size (usually 4096 bytes).

?? - Convert 5GB into bytes.

?? - Divide by page size to get the value in pages.

PAGE_SIZE=$(getconf PAGE_SIZE)  # or cat /proc/sys/kernel/shmmni

echo "($5GB  1024  1024 * 1024) / $PAGE_SIZE" | bc -l        

Setting SHMALL:

- Dynamically Updating SHMALL:

echo "1310720" > /proc/sys/kernel/shmall

sysctl –p        

???- Verifying the Update:

?sysctl -a | grep shmall

 # or

 ipcs -lm        

?Persistent Configuration

- Updating sysctl.conf:

echo “kernel.shmall = 1310720” >> /etc/sysctl.conf        

- Ensuring Boot-Time Configuration:

chkconfig boot.sysctl

#If it returns "off", enable it:

chkconfig boot.sysctl on        

References

1. Oracle Document ID 567506.1: "Maximum SHMMAX values for Linux x86 and x86-64"

2. Oracle Document ID 15566.1: "TECH: Unix Semaphores and Shared Memory Explained"

3. Oracle Documentation: "Configuring Linux Kernel Parameters"

https://docs.oracle.com/en/database/other-databases/timesten/22.1/scaleout/configuring-linux-kernel-parameters.html#GUID-0A4D6707-756D-4854-807B-A220C829B798

4. Oracle Documentation: "Minimum Parameter Settings for Installation"

https://docs.oracle.com/en/database/oracle/oracle-database/23/ladbi/minimum-parameter-settings-for-installation.html#GUID-CDEB89D1-4D48-41D9-9AC2-6AD9B0E944E3