MGA Issue - and it is fixed with Oracle 19.8.0 and newer

Where do I start? One of the customer accounts I worked the longest time with recently upgraded to Oracle 19c on Exadata. They are an Exadata customer since 2009. After going live on 19c, a few days later they hit an MGA Issue – and it is fixed with Oracle 19.8.0 and newer. But question no.1 was: Why hasn’t Oracle warned us – and how could we have learned about it?

Photo by Scott Webb on Unsplash

What is the MGA?

OK, I have heard of SGA and PGA. But MGA? The first two hits when I search with “MGA oracle” in my preferred search engine give me these results:

The GI/Solaris documentation tells me this:

Starting with 18c, on Oracle Solaris systems, Oracle Database uses a new method of sharing memory among a group of processes, also known as Managed Global Areas (MGA). The operating system memory allocation mechanism for this new method is Optimized Shared Memory (OSM).

And further:

Oracle recommends that you configure the additional shared memory identifiers and increase the shared memory limits as follows:
Additional number of MGA segments (project.max-shm-ids) = MGA memory size / granule size

Where:
MGA memory size is the cumulative MGA memory requirement size for all processes in an Oracle Database instance. Typically, 2MB per Oracle process.
Minimum value of granule size is 4 MB. For large SGA, granule size may increase automatically upto 512 MB.
Refer to the following table to determine the approximate granule size.

Ok. Interestingly, MOS Note: 2638904.1 states it is there since Oracle 12.2.0.1. Which it is, by the way. And also quite interesting, that the MGA topic isn’t mentioned in any other than the GI for Solaris documentation. But it exists on other ports as well, too.

MOS Note: 2638904.1 reveals way more details – and also some known issues.

But what is the MGA now in brief? I better quote the MOS Note here:

About MGA (Managed Global Area):

The MGA (or Managed Global Area) allows a “smaller set” of processes (or even all processes) to share an address space for the duration (typically a query). The MGA is made up of namespaces which contain segments and heaps with space management either directly or through a heap manager (KGH).

Processes can attach to their namespace for the duration they require. For e.g., in a parallel query environment, PQs participating with the QC can share the namespace from QC to share the results. Once done, they will detach from the same and the namespace can be tiered down.

The MGA is allocated dynamically which gives us more flexibility to create, control and share. The sharing is dynamic in the sense that processes coordinate before sharing. So in that sense, MGA sits between the SGA and PGA. In addition, since the MGA is a shared memory area, a latch (latch: MGA) is used to control access and protect it.

Difference between MGA/PGA/SGA:

The MGA is not the SGA or PGA. The PGA is private and the SGA is completely shared by all processes and is not elastic. The MGA is elastic (processes can create and drop their MGA segments dynamically), shared between a set of processes or all processes, and counted under the PGA target/limit values (in v$PGASTAT). The SGA is typically created once while the MGA is created on demand.

There can be multiple MGAs in an instance while we have just one SGA.

The Issue after upgrade to Oracle 19.5.0/19.6.0/19.7.0

My customer now ran into an issue with unexpected hangs/crashes in their RAC environment. It took a while until the root cause got found by Oracle Support. It is caused by unpublished Bug 30851951 – OPEN FDS TO /DEV/SHM/*KSIPC_MGA*.DAT CAUSES FILE-MAX LIMIT TO EXCEED AND CRASHES INSTANCE which only happens when PGA_AGGREGATE_LIMIT is not set – then it defaults to 0. The same issue may happen when you set it explicitly to 0. See as well: MOS Note:1520324.1 – Limiting Process Size with Database Parameter PGA_AGGREGATE_LIMIT.

Confirmed versions to be affected are:

19.5.0
19.6.0
19.7.0

This is the typical pattern you may see in your environment then:

Instance hangs and /dev/shm grows having a lot of file descriptors opened to /dev/shm/*KSIPC_MGA*.dat.

The database may crash with error: FILE-MAX Limit <XXXXXXXXX> Reached

The Solution – Move to Oracle 19.8.0 or newer

The information is not tagged by an alert on MOS. Hence, unless you know that the issue exists, it is really hard to find it. This is the main reason for this blog post.